Working with queries and null values

[guidout]

this might seem an easy question but I can't figure it out. I have a table with a number of fields. 3 of them (SizeA, SizeB, SizeC) are numbers and I have a form which uses a query to query the table. Now, Size A is always not null, but SizeB and C can be null. This is the criteria I use for sizeA, B and C:
Like IIf([forms]![Search Fittings]![SizeA_Text]="0","*",[forms]![Search Fittings]![SizeA_Text])
Like IIf([forms]![Search Fittings]![SizeB_Text]="0","*",[forms]![Search Fittings]![SizeB_Text])
Like IIf([forms]![Search Fittings]![SizeC_Text]="0","*",[forms]![Search Fittings]![SizeC_Text])
As you can see, the if checks whether the form field Size*_Text is equal to 0 or not. The problem is that the table entries with SizeB or SizeC not set (so they are null) do NOT show up in the search. The solution might look like the following:
Like IIf([forms]![Search Fittings]![SizeC_Text]="0",Like "*" Or Is Null,[forms]![Search Fittings]![SizeC_Text])
but it doesn't work. do you have any suggestion on how I can realize this logic?



Thank you, Guido

Ps: I found this:
http://www.dbforums.com/showthread.p...uot-left-blank
I tried:
WHERE (MyField = Forms!MyForm!MyCombo or Forms!MyForm!MyCombo IS NULL)
but I get an error about WHERE does not exists, I guess it is for older Access versions.

[pbaldy]

I would try that last method again. Did you change to your form/combo names? It should work for any version. The form has to be open.

[guidout]

I get "Undefined function 'WHERE' in expression'...and yes I put the right name of my form...

I would try that last method again. Did you change to your form/combo names? It should work for any version. The form has to be open.

[NTC]

you have the 0 wrapped in quotes which is ok if it is text....but if it is a number type then one does not use quotation symbols around it

[guidout]

[forms]![Search Fittings]![SizeA_Text] is a text type field. The corresponding table field is actually a number. Anyways it works, the problem is that it does not show the table entry when the table field is null.
The Null value is not included in "*"...that's my problem...

you have the 0 wrapped in quotes which is ok if it is text....but if it is a number type then one does not use quotation symbols around it

[pbaldy]

I get "Undefined function 'WHERE' in expression'...and yes I put the right name of my form...

What's the full SQL of the query with that attempt? Sounds like something is out of place. In design view, you would not include the word WHERE.

[NTC]

oh you are trying to pass a wild card to a query object; yeah that's messy.... my approach would be to use SQL language itself and then toggle which SQL statement to use based on the field value. If you are not familiar with the SQL language itself just build a separate query that works for each of those conditions; then view them in SQL View and that gives you the language itself - then you can copy paste that into an event...a little elbow grease research on RunSQL but is do-able.