"Does Not Equal" in expression builder

[caringsharingbristolbilly]

Hi to all you kind kind people in MrExcel.comland.

This is a really simple question, but one my brain (which has been replaced by Silly Putty ) is unable to extract from the help files.

If I want to do an expression which compares two text columns, and reports the following conditions:

(a) Column 1 does not equal column 2
AND
(b) Either of the two columns is blank, while the other isn't

...I can't use the <> function, apparently, as that only uses condition (a).

I'm sure it's dead easy, so any non-VB related help would be much appreciated (my VB knowledge doesn't extend past the Australian imported lager of the same name).

Much obliged,
CSBBB

 

[Philem]

Try this as a criteria:

Iif(And(Not(IsNull(ColA)),Not(IsNull(ColB))),Iif(ColA<>ColB,"Columns Not Equal",Iif(IsNull(ColA),"Column A empty","Column B empty")))

Basically, what you're trying to do is test for Null, then test for non-equallity.

HTH

P

 

[Joe4]

If I understand you correctly, it sounds like condition B overrides condition A, because if you want both conditions together (AND instead of OR), then you only really care about conditions where one field is blank and the other isn't (you don't seem to care if the values are different if neither is blank).

If that is the case, in the Query builder, on the first line of criteria, under your first field enter "Is Null" and under your second field enter "Not Is Null".

Then create a second line of criteria where you do the reverse, enter "Not Is Null" under the first field and "Is Null" under the second field.

 

[caringsharingbristolbilly]

Hi Jmiskey.

Actually, I want to know all cases where (a) one of them is blank as well as (b) where neither is blank, but they're different. Will your suggestion report both? Or do I add in another field?

Philem - Do I just type that in to the expression box on the "design" view of the query builder?

Thanks both...

 

[Joe4]

Actually, I want to know all cases where (a) one of them is blank as well as (b) where neither is blank, but they're different.

Then what you really meant by your original question is (a) OR (b), not (a) AND (b). The semantics make a big difference.

Simply add a third line of criteria and under the field 1 criteria, enter:
<>

.[Field2]

where Table is the name of the Table the fields are found in
and Field2 is the name of the second field you are comparing the first field to.

 

[caringsharingbristolbilly]

Hi. Sorry about that. Having a semantically challenging day at the moment. I've used the <> function, but it's not working how I'd expect. I'll try and clarify further.

The two tables I have are lists of customer records extracted from our systems at different points in time. I want to compare the Name fields for the two sets of data to see if any changes have been made between the dates the extracts were done.

Hence, I want to know a total of the following: (a) any CHANGES to the names (i.e. clients getting married), (b) any ADDITIONS of names (i.e. middle names) and (c) and DELETIONS of name fields (i.e. if we put them on by mistake).

When I do my comparison query using the <> function, it says there are 395 records where the MIDDLE NAME field is not the same across them both. It also says that there are 7 records where the old data is blank, and the new data has a name (i.e. an ADDITION of a middle name).

However, when I go into the data sheets and do filters for BLANK on the old data and NOT BLANK on the new, I get 1255 records.

This is why I don't think the <> function is doing the job.

Any ideas?

I literally want to do what in excel would be IF(A1<>A2,"Changed","Not Changed"), but can't export it, as it's far too large.

 

[Joe4]

The two tables I have are lists of customer records extracted from our systems at different points in time. I want to compare the Name fields for the two sets of data to see if any changes have been made between the dates the extracts were done.

Are you comparing two columns within the SAME table, or comparing columns across two tables?

These details are very important to how you would approach the problem, so please be as thorough and detailed as possible.

 

[Norie]

Why not create a Find Unmatched query using the Wizard?

 

[caringsharingbristolbilly]

The comparison is across TWO tables. In other words, I want to compare the "Name1" column on table 1 (the old data) to the "Name1_SRC" column on table 2 (the new data).

I've set up a relationship between the two tables around customer number, which is a unique reference for each customer, and exists on both tables.

The two tables contain the same customers, but with the data taken from the systems at different points in time.

 

[Joe4]

If that is the case, they you do what Norie suggested, create an Unmatched Query using the Query Wizard.