Ignoring Nulls in Query

[jpkent]

I have a query where my last argument uses the following criteria:

IIf(IsNull([Query Compare]![OTHER/UNLISTED]),[Patient Information]![OTHER/UNLISTED],[Query Compare]![OTHER/UNLISTED])

[Patient Information]![OTHER/UNLISTED] is null (as far as I can tell, there is nothing in it and no spaces), and [Query Compare]![OTHER/UNLISTED] is also null. What I want is that if [Query Compare]![OTHER/UNLISTED] is null, then I want the criteria for this field to basically be ignored, therefor the query returns all [Patient Information]![OTHER/UNLISTED], but if [Query Compare]![OTHER/UNLISTED] is not null, then I want [Patient Information]![OTHER/UNLISTED] to have to equal it. This is not the case though, because when [Patient Information]![OTHER/UNLISTED] is null then it is not shown.

I found other posts like this, but I'm not still not getting it right, so I was hoping maybe there's just something wrong with this. I'm pretty sure this is where the problem is, because if I change this criteria to Is Null, I get the desired result.

 

[dodge20]

In access, a field may look null, but doesn't mean it is. Check out my FAQ for more info.

One way around this is to make the field either blank or null.

update table
set field = ''
where field is null

or

update table
set field = null
where field = ''

or check out this thread for a different approach

thread701-559605

Dodge20