Where Condition Help (1 Viewer)

modest

Registered User.
Local time
Today, 04:30
Joined
Jan 4, 2005
Messages
1,220
Please don't criticize the naming, I've renamed to make the logic simpler to understand.

I am trying to run a query based on the value of a textbox. Right now if the text box is empty it shows all the records. This is what works:
Code:
SELECT      A.ID, B.Weight
FROM        A 
LEFT JOIN   B 
ON          A.ID = B.ID
WHERE       B.Weight Like 
              IIf([forms]![Form]![txtBox] & ""="","*",[forms]![Form]![txtBox])

The problem is when tbl B is blank (and only when its blank), I also want to see all records where weight is null.

Some examples of what I've tried (I have tried variations of):
Code:
B.Weight [B]Like [/B]IIf([forms]![Form]![txtBox] & [B]""="","* Or Is Null"[/B],[forms]![Form]![txtBox])
B.Weight IIf([forms]![Form]![txtBox] & ""="",[B]"Is Null or Like *"[/B],[forms]![Form]![txtBox])

I am not worried about the False condition, the problem is that Null is not being returned from inside an IIF().
When I set the WHERE statement to: WHERE B.Weight Is Null, it works. But if I try IIf([forms]![Form]![txtBox] & ""="",Is Null,[forms]![Form]![txtBox]). It doesnt work (Whether I put it in quotes or not).


Any help would be appreciated. Thank you.
 

modest

Registered User.
Local time
Today, 04:30
Joined
Jan 4, 2005
Messages
1,220
Nevermind, got around it using the Nz() function in the field selector.
 

Users who are viewing this thread

Top Bottom