Comparison query

[fllopez65]

Hi Folks
I use access 2007 and for a data base, I have the following field names:
id number, current use, original use. I need to run a query that will only show id numbers where the original use is different from the current use. All 3 fields are in the same table. Regardless, what I try my queries have been producing all the id numbers even when the original use and the current use are the same. I just want the exceptions. I have tried using the cross tab query wizard and the find unmatched query wizard but they don't produce the results, I want. Does anyone have any suggestions?

Tks
fllopez65

 

[PNGBill]

Can you provide some sample data for the three fields.

If your field names are as typed, I advise you avoid spaces in field names.

 

[Alansidman]

In your query, create a new field. Type Agree: IIF([current use]=[original use],"Y","N")

In the criteria for this new field type: "N"

Run your query. It should give you only those records where the fields do not match.

I agree with PNGBill on your field names. Good practice to name your fields as follows CurrentUse or Current_Use. Spaces will cause you issues in the future. Be consistent in your formatting which ever convention you use.

Alan

 

[vbaInet]

For the sake of effeciency, following Alan's good advice, I would use:

Type Agree: IIF([current use]=[original use], [COLOR=Blue]0[/COLOR], [COLOR=Red]Null[/COLOR])

And in the criteria:

[COLOR=Red]Is Null[/COLOR]

 

[fllopez65]

Tks to Alansidman,vbaInet and PNGBill, I tried your suggestions and they worked.

fllopez65