IIF Statement - Dealing with Like * and Nulls

[Maturi]

I’m trying to build a query (using query builder) that will change the criteria on a field (Num) depending on the value of text box (TB1) on form (frm1)

So if TB1 is left blank (Null) then I want the criteria field to match everything in Num. If the value is non-Null (ie an actual value entered) then I want to match against just that value.

Simplified Example – I have a Table

ID Num
1 44
2 55
3 66
4 empty - Null
5 empty - Null
6 77

If I create a simple query in query builder to list all the ID’s with the following criteria on Num - ‘Like * ‘

I get back records 1,2,3 and 6 (ie the non Null’s in Num)

If I use the Criteria - ‘ Like * or is Null’

then I get back all the records – which is what I want.


Now if I put this criteria in an IIF statement like this

IIF(TB1 is null, (Like * or is Null), TB1)

Then I get back no records at all - which I can’t explain.

Can you explain – or better still – think of a way to make this work. Even in SQL would be useful

Thanks

 

[Remou]

As far as I recall, Nulls are a problem in this knid of set up. Here are two possiblities.
Build an SQL statement:

Dim qdf As DAO.QueryDef
If IsNull(Me.TB1) Then
  strSQL="Select * From Table Where Field Is Null"
Else
  strSQL="Select * From Table Where Field ='" & Me.TB1 & "'"
End If

Set qdf = CurrentDb.QueryDefs("[i]ExistingQueryName[/i]")
qdf.SQL = strSQL

DoCmd.OpenQuery "[i]ExistingQueryName[/i]"

Or, if both Blanks and Nulls are acceptable, you could alter your query like so (from Query Design, SQL view):
[tt]SELECT Trim([Field1]) & "" AS Expr1
FROM tblTable
WHERE (((Trim([Field1]) & "")=IIf(Trim([Forms]![frmForm]![TB1] & "")="","",[Forms]![frmForm]![TB1])));[/tt]

 

[Maturi]

Remou

Fantastic - I've been trying to resolve this problem for ages. I'm using your second soution (but it returns only the Null values when I need all values - but that's OK)

It was the line 'Trim([Field1]) & "" AS Expr1' that really helped. Basically it was an easy way to covert all Null values to non-Null without changes the other values - Yes I know you knew that. But it's new to me!!

Thanks for your help

M