IIF IsNull in a SQL not working.

[gtslabs]

I have a Query in my Record Source of a Form.
It references 2 combo boxes. I want to display records based on both
boxes or if Box1 is Null then only Box2

I used the following as substitutions as I am getting confused with
the many ().

A = ((Log.Project)=[Forms]![frmMainForm]![cbo1])
B = ((Log.Pour_Date)=[Forms]![frmMainForm]![cbo2])
C = ((([Forms]![frmMainForm]![cbo1]) Is Null));

SELECT *
FROM Log
WHERE iif(IsNull(A,B,(A AND B))) OR C

I have the following SQL statement:

SELECT *
FROM Log
WHERE iif(IsNull(((Log.Project)=[Forms]![frmMainForm]![cbo1]),
((Log.Pour_Date)=[Forms]![frmMainForm]![cbo2]),
(((Log.Project)=[Forms]![frmMainForm]![cbo1])
AND ((Log.Pour_Date)=[Forms]![frmMainForm]![cbo2])))) OR ((([Forms]!
[frmMainForm]![cbo1]) Is Null));


I beleive I have my () in order but I still can not make it work.
Can I used the IIF in a SQL? What is wrong?

 

[Lord Kelvan]

because isnull dosnt work with strings you have to use iif string = ""

Regards
Kelvan

 

[John Spencer]

Try the following.
It will return ALL records if both comboboxes are left blank.

If one combobox is blank (null), it will return records based on the other
combobox.

If both comboboxes have a value it will return records based on both.

SELECT *
FROM Log
WHERE (Log.Project=[Forms]![frmMainForm]![cbo1]
OR [Forms]![frmMainForm]![cbo1] is null)

AND (Log.Pour_Date=[Forms]![frmMainForm]![cbo2] OR
[Forms]![frmMainForm]![cbo2] is Null)


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Dale Fye]

I think the other problem is that you appear to be evaluating whether a field
is equal to a value in a form (which will evalute to True or False), and then
testing it for ISNULL( ), which will evalute to true if either the field is
null, or the control on the form is null.

Based on your description, I think you want:

WHERE (Len(Forms!frmMainForm.cbo1 & "") = 0 OR
[Project] = Forms!frmMainForm.cbo1)
AND [Pour_Date] = Forms!frmMainForm.cbo2

Note that I didn't use ISNULL( ) on cbo1, since it could be null or empty.
Both of these look the same, but are definately are not the same. By
concatenating an empty string to the end of the value of the cbo1, you can
test to see whether the length is equal to zero (cannot use LEN( ) to test
for Null because it will return an error).

So, what this says is give me all the records where (cbo1 is null, blank, or
where cbo1 = [Project]) and where cbo2 = [Pour_Date]

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.