IIF condition and query

  • Thread starter Thread starter kon
  • Start date Start date
K

kon

I have a query where I want to put the criteria
iif([forms]![master]![cmbfield]=0;null;[forms]![master]![cmbfield]) and
filter the field cmbkeymaster (Numeric)
cmbfield is numeric not string. I noticed that null takes it as string I
tried is null but nothing happened. I tried to replace null with
[Forms]![master]![cmbfield] is null where works when null value is in
cmbfield and criteria is [forms]![master]![cmbfield]. help me
Thank you in advace.

Kon
 
kon said:
I have a query where I want to put the criteria
iif([forms]![master]![cmbfield]=0;null;[forms]![master]![cmbfield]) and
filter the field cmbkeymaster (Numeric)
cmbfield is numeric not string. I noticed that null takes it as string I
tried is null but nothing happened. I tried to replace null with
[Forms]![master]![cmbfield] is null where works when null value is in
cmbfield and criteria is [forms]![master]![cmbfield]. help me
Click to expand...


When you use a criteria, Access assumes you want to compare
the field = to the criteria **value**. Null never compares
in any way to anything (not even Null). Since the criteria
must be a value, you can not include a operator such as Is
Null inside the IIf.

The way to get what I think you want is to create a
calculated column in the query:

IIf(Forms!master!cmbfield=0; thefield Is Null; thefield =
Forms!master!cmbfield)

Then uncheck the Show box and use True in the criteria row
 
Back
Top