Null as option in query (2002)

[Leesha]

Hi,

I'm trying get a query to filter for null records based on the answer that is in the control cmbReturn on frmPatientLookup. This is what I have so far:

IIf([forms]![frmPatientLookup]![cmbReturn]="Non-Returned Records",Null,"*")

My problem is that it doesn't filter for Null. I need to have only null records show if the answer chosen is "Non-returned Records".

Thanks,
Leesha

[HansV]

You'll have to set it up differently: the WHERE part of the SQL should look like

WHERE FieldName Is Null OR [Forms]![frmPatientLookup]![cmbReturn]<>"Non-Returned Records"

Also see the screenshot below. FieldName is the name of the field you want to filter on, and tblSuppliers is the name of the table (just an arbitrary name for the screenshot)

[Leesha]

Another new learning experience! This worked great. One more question. I will be setting up a similar scenerio for another query, but there will be 4 options available to the user to choose from, one of which will result in. Is this possible?

[HansV]

One of which will result in ... what?

[Leesha]

The results would be result that shows all dates. Another will be records with "NA".

[HansV]

Sorry, you've lost me (please remember that English is not my native language). Could you explain slowly and in more detail? Thanks.

[Leesha]

"English is not my native language" - my respect for you has grown even more!!

OK, Here goes. The next query I need to correct will filter records based on [cmbEmployee] on [frmPatientLookup]. If [cmbEmployee] is null than all records should be the result. If the choice is "Director" than only records that have "NA" should be filtered. If the choice is "RN" than the result should be all records with "RN", and if the choice is "Other" than the result should be null.

[HansV]

Thanks, that is clearer. Try this WHERE part:

WHERE ([Forms]![frmPatientLookup]![cmbReturn] Is Null) OR ([Forms]![frmPatientLookup]![cmbReturn] = "Director" AND FieldName = "NA") OR ([Forms]![frmPatientLookup]![cmbReturn] = "RN" AND FieldName = "RN") OR ([Forms]![frmPatientLookup]![cmbReturn] = "Other" AND FieldName Is Null)

Replace FieldName with the name of the field you want to filter on.

[Leesha]

Wow!!! I've never done a query this way before and it opens up a whole world of new opportunities!!

Thanks so much. It's perfect!!

Leesha