Using Is Null in an SQL IIF criteria

[Enigma007]

Heya.

Quick question...

If I am creating a query and I want to find all records where a field is null I could put:

Is Null

As my criteria. Unfortunately, if I use a IiF statement, I can't put Is Null as one of the two results of the Iif. It gives me errors. I can put Null, but Null=Null does not evaluate to true so I need to use Is Null...

ANy ideas how to do this?

Thanks,
Enigma

 

[PHV]

What do you want to do with the IIf function ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Enigma007]

Basically I have a form control that I want to use as part of the criteria. It would look like this:

Iif(Len(Forms![myForm]![myText] & "") = 0, Is Null, Forms![myForm]![myText])

So, if the control is filled in I want that to be the criteria. Otherwise, the user is looking for the null values.

But, like I said... the Is Null does not work there.

Thanks,
Enigma

 

[PHV]

I'm afraid you have to go in the SQL pane and amend the WHERE clause:
(([theField] Is Null And Len(Nz(Forms![myForm]![myText],"") & "")=0)
OR [theField]=Forms![myForm]![myText])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Enigma007]

I actually just figured it out. I was using the query builder instead of the straight SQL. It was doing an:

MyTable.myField = iff(...)

This wont work because MyTable.myField = Is Null would naturally create errors.

I encapsulated the whole thing in the IIF and did:

iif(Len(Forms![myForm]![MyText] & "") = 0, MyTable.Myfield is Null, MyTable.MyField = Forms![myForm]![MyText])

Sorry to have wasted your time

 

[cmmrfrds]

iif(Len(Forms![myForm]![MyText] & "") = 0, MyTable.Myfield is Null, MyTable.MyField = Forms![myForm]![MyText])

This should be okay.
iif(Len(Forms![myForm]![MyText] & "") = 0, Null, Forms![myForm]![MyText])