Use of Is Null with the IIf function

[LonnieJohnson]

I am using the IIF function to determine criteria of a date field in a query. I have found, as I am finding today that the IIF function dosen't like the Is Null or Is Not Null statement in the query grid.

I typed the following:

IIf([Forms]![MyForm]![IndicatorField]=1,Is Null,Is Not Null)

Any insight on how to select records that have a Null value in a certain field based on a condition would be very helpful.

Thanks in advance... B-) ljprodev@yahoo.com
Professional Development
MS Access Applications

 

[JerryDennison]

You cannot assign Is Null or Is Not Null as values. If you want to assign Null you can but under no condition can you simply assign Not Null. These are tests not assignments.

IIf([Forms]![MyForm]![IndicatorField]=1, Null,Forms]![MyForm]![IndicatorField])

This will assign the value of the IndicatorField to the control if it's value is anything other than 1 in which case it will assign null.

 

[DRowland]

I had what I think was an identical problem with my database. Some of my files had info in the

http://www field,

while other files did not.
I found that the easiest thing to do was to make sure that I did not have any null fields that were used in searches.
I export the data from another program to the access database, and I just changed the export program to be sure that there was a single period "." in the fields. Now it works great with no trouble.

 

[LonnieJohnson]

That's a big 10-4 Drowland.

I actually did the same thing in an earlier project I did. Unfortunately, that's not an option this time around. I posted the same question in the General Discussion Forum and was pointed in a different direction. I am putting the SQL for report query in my module and testing the parameters using variables and case selects. ljprodev@yahoo.com
Professional Development
MS Access Applications

 

[JoeMiller]

Try this page out, it may help!

http://www.fabalou.com/Access/Queries/comboboxes_criteria.htm

Joe Miller
joe.miller@flotech.net

 

[LonnieJohnson]

Thanks Joe. Not quite what I was looking for, but this is a pretty cool site that definately got bookmarked. ljprodev@yahoo.com
Professional Development
MS Access Applications

 

[JerryDennison]

My booboo! I missed the fact you're trying to define search criteria. Sorry 'bout that.

 

[JerryDennison]

I may have a solution for you. Add an unbound field that gives you a value to test the indicator field against.

Expr1: IIF(IsNull([FieldName]), 1)

Then in this field's criteria property use the criteria you have set.

IIf([Forms]![MyForm]![IndicatorField]=1, Null,Forms]![MyForm]![IndicatorField])

 

[LonnieJohnson]

JoeMiller gave a good explanation as to why you cannot use the Null or Is Null keywords in the IIf funtion. He and tlbroadbent also gave good work arounds. See
thread181-85522. ljprodev@yahoo.com
Professional Development
MS Access Applications