Query Help (again!)

  • Thread starter Thread starter Alex
  • Start date Start date
A

Alex

I got help on a previous query where I was trying to find
records where the value was different to another field in
my table. e.g.

Contract No, Customer Name, Amount1, Amount2

in my query I entered <>Amount1 in the criteria under
Amount2. This works fine and displays only those records
where the Amount2 value is different from Amount1.

However I also need it to display those records where
Amount1 is blank aswell as the ones that are different,
this query currently does not do this. I have tried
entering another set of criteria under <>Amount1 that says
Is Null [Amount1] but that does not work.

Any suggestions?
 
Alex said:
I got help on a previous query where I was trying to find
records where the value was different to another field in
my table. e.g.

Contract No, Customer Name, Amount1, Amount2

in my query I entered <>Amount1 in the criteria under
Amount2. This works fine and displays only those records
where the Amount2 value is different from Amount1.

However I also need it to display those records where
Amount1 is blank aswell as the ones that are different,
this query currently does not do this. I have tried
entering another set of criteria under <>Amount1 that says
Is Null [Amount1] but that does not work.
Click to expand...
Hi Alex,

I would have thought, if grid columns
looked like following, it should work

Field: Amount2
Table:
Sort:
Show: <checked>
Criteria: <> [Amount1]
Or:

Field: Amount1
Table:
Sort:
Show: <checked>
Criteria:
Or: Is Null

If you go into SQL View, you could also
change your WHERE clause to:

WHERE NZ((Amount1<>Amount2),-1)

this will return a record when
Amount1<>Amount2
or
either is Null

Good luck,

Gary Walter
 
Back
Top