1

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

guidout
  • 123

2 Answers2

0

wrap the field in the nz function.

NZ will assign the second argument if the first is null. 

nz("SuperUser","default") = "SuperUser"
nz(NULL,"default") = "default"

this would change the test you have to something like this (repeat for whatever tests you need to do):

Like IIf(nz([forms]![Search Fittings]![SizeB_Text],"")="0"
         ,"*"
         ,nz([forms]![Search Fittings]![SizeB_Text],"")
        )
SeanC
  • 3,804
0

You need to use a bit more complex criteria, it's not possible to do it within one iif() (you can't test your table entry for null value within a like statement).

([...sizeC_text]="0" and isnull(<your field>)) or <your field> like iif([...sizeC_text]="0","*",[...sizeC_text])

Also note that this condition is right if you use it in VBA, if you put it in an SQL expression than replace isnull() function to ... Is Null

Máté Juhász
  • 22,071