Query with Null Values

[Rjc8513]

My query has the following on the 'Criteria' line in the design view for the field [TYPE]:

IIf(IsNull([Forms].[frm ARCHIVE].[cbxTYPE]),[TYPE],[Forms].[frm ARCHIVE].[cbxTYPE])

There are 777 records in the table, 19 of which have null values in the [TYPE] field. If I run the query with no criteria, all 777 are returned. With the criteria above entered, making no entry in the [cbxTYPE] combo box, I only get 758 records returned. The nulls are not returned.

What can I do to get the nulls to be returned when I make no entry in the combo box? Thanks.

 

[times44]

I don't know if this would be relevant to you, but I did a "google" search on a similar subject. I found an article in MSDN library titled "plan for null responses in your parameter queries." I don't have exact web site or an article number but these came up with in top ten results in google search.

Hth

x44

 

[Geekmomz]

IIf(Len([Forms].[frm ARCHIVE].[cbxTYPE])>0,[Forms].[frm ARCHIVE].[cbxTYPE],[TYPE]) might do what you're looking for.

 

[Lightenup]

The address for the "plan for Null Responses..." article is

http://msdn.microsoft.com/library/en-us/dnima00/html/ima0062.asp?frame=true

Good luck.

Lightenup