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:
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):
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.
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.