Null Criteria - Null not equal to Null

[mbreu996]

Greetings,

My issue is simple. I am making a query interface for my database - so end users can easily extract what they want.

I have several controls that may or may not be null when I run the query. I am using the following as my Criteria (which may be flawed):

IIf([Forms]![F_QUERY]![A_Combo_Box_Control] is null, [T_SITE]![Station_ID], [Forms]![F_QUERY]![A_Combo_Box_Control])

In essence I am saying if the control that is a criteria for this FIELD is null, use the value from the TABLE as the criteria - hence it is accepting all records. I am sure I don't have any data type mismatches either. This worked for many different FIELDS until I ran into one that had null values in the TABLE itself.

The query won't return any of the records that have null values on the TABLE in this FIELD. In essence, Access is telling me that:
Null does not equal Null ?

There must be a better way to do this - or maybe some work around.

Thanks in advance,
-Mark

 

[boblarson]

You need

IsNull(FieldToCheckHere)

Null is not EQUAL to Null since NULL is the absence of a value.

 

[mbreu996]

Thanks for the help. Using IsNull([Control]) instead of [Control] Is Null still leaves me with the issue that you point out - the absence of a value is not equal to the absence of a value (I did not know this).

How can I solve my dilemma? Is there some technique I can use to do the following:
When a control has a null value, there are no criteria for this field in the query (i.e. return all the records), but when the control has a value, use this value as the criteria for the field in the query.

I suppose I could use a query before this one to take the null values from the field in the table and change them to "-" or something. Then my previous technique would work - but this doesn't seem like the best approach.

 

[Brianwarnock]

Read this

Brian

 

[mbreu996]

Thanks a bunch Brian- I had a feeling I was missing some fundamental concept. My mistake for not locating that thread in the first place.

Thanks also to Bob for the lesson on Null not equal to null.

Another case solved by the Access World Forums - you guys are the best!

 

[Brianwarnock]

Thanks a bunch Brian- I had a feeling I was missing some fundamental concept. My mistake for not locating that thread in the first place.

Don't be hard on yourself, I've pointed so many to that thread that I now have a word doc in my samples folder with the link on.

Brian