IIF statement in query criteria to have Like * or Is Null as the result if true

B

bdt513

I have a Microsoft Access 2003 query that checks a value in a form's
combo box [cboFilterDepartments] (the combobox returns a integer ID
value) and uses that value as criteria for an integer field [alcDepID].

If the field is populated, I want the query to return the records based
on the value in the field. This would read --> Like
[Forms]![frmAllocations]![cboFilterDepartments]

If the field is null, I want the query to return all records. Not
every record has data in the alcDepID field, and thus would need a like
as follows --> Like "*" or Is Null

Here is what I had as a criteria item, and this (as one would expect)
only returned those fields for which there was a value in the record.
Instead of 5506 records, I got only 429 --> Like
IIf(IsNull([Forms]![frmAllocations]![cboFilterDepartments]),"*",[Forms]![frmAllocations]![cboFilterDepartments])

There is a lot of other stuff going on in the query, but everything is
working just fine. It is only here where I'm having issue. I included
the SQL below, but it can probably be ignored.

Thank you very much for any tips that you can give.

-BDT


SELECT tblProperties.prpYear, tblProperties.prpName,
tblSessions.sesDate, tblSessions.sesTime, tblSessions.sesNumber,
tblTickets.tktAccess, tblItems.itmNumber, tblTickets.tktSuite,
tblTickets.tktSection, tblTickets.tktBox, tblTickets.tktRow,
tblTickets.tktSeat, tblItems.itmAllocateItem, tblArea.araNameCommon,
tblDepartments.depName, [empNameLast] & ", " & [empNameFirst] AS
empNameFull, [usrNameLast] & ", " & [usrNameFirst] AS usrNameFull,
tblItems.itmID, tblItems.itmSesID, tblItems.itmTktID,
tblProperties.prpID, tblAllocations.alcAraID, tblAllocations.alcDepID,
tblAllocations.alcEmpID, tblAllocations.alcUsrID
FROM tblUsers RIGHT JOIN (tblDepartments RIGHT JOIN (tblEmployees RIGHT
JOIN ((tblArea RIGHT JOIN tblAllocations ON tblArea.araID =
tblAllocations.alcAraID) INNER JOIN ((tblTickets INNER JOIN
(tblProperties INNER JOIN (tblSessions INNER JOIN tblItems ON
tblSessions.sesID = tblItems.itmSesID) ON tblProperties.prpID =
tblSessions.sesPrpID) ON tblTickets.tktID = tblItems.itmTktID) INNER
JOIN tblAlcIDs ON tblItems.itmID = tblAlcIDs.itmID) ON
tblAllocations.alcID = tblAlcIDs.alcID) ON tblEmployees.empID =
tblAllocations.alcEmpID) ON tblDepartments.depID =
tblAllocations.alcDepID) ON tblUsers.usrID = tblAllocations.alcUsrID
WHERE (((tblItems.itmSesID) Like
IIf(IsNull([Forms]![frmAllocations]![cboFilterSessions]),"*",[Forms]![frmAllocations]![cboFilterSessions]))
AND ((tblProperties.prpID) Like
IIf(IsNull([Forms]![frmAllocations]![cboFilterProperties]),"*",[Forms]![frmAllocations]![cboFilterProperties]))
AND ((tblAllocations.alcAraID) Like
IIf(IsNull([Forms]![frmAllocations]![cboFilterAreas]),"*",[Forms]![frmAllocations]![cboFilterAreas]))
AND ((tblAllocations.alcDepID) Like
IIf(IsNull([Forms]![frmAllocations]![cboFilterDepartments]),"*",[Forms]![frmAllocations]![cboFilterDepartments])))
ORDER BY tblProperties.prpYear, tblProperties.prpName,
tblSessions.sesDate, tblSessions.sesTime, tblSessions.sesNumber,
tblTickets.tktAccess, tblItems.itmNumber, tblTickets.tktSuite,
tblTickets.tktSection, tblTickets.tktBox, tblTickets.tktRow,
tblTickets.tktSeat;
 
M

Michel Walsh

Hi,

Is it possible that cboFilterDepartments is not null, but blank (one or more
spaces).
in SQL view, try the syntax:


.... WHERE IIF( 0=len(Forms!frmAllocations]!cboFilterDepartments & "" ),
TRUE, FieldName LIKE "*"& Forms!frmAllocations]!cboFilterDepartments &
"*")


It should also be faster since it does not force the evaluation of LIKE at
all if the parameter is not specified.




Hoping it may help,
Vanderghast, Access MVP


I have a Microsoft Access 2003 query that checks a value in a form's
combo box [cboFilterDepartments] (the combobox returns a integer ID
value) and uses that value as criteria for an integer field [alcDepID].

If the field is populated, I want the query to return the records based
on the value in the field. This would read --> Like
[Forms]![frmAllocations]![cboFilterDepartments]

If the field is null, I want the query to return all records. Not
every record has data in the alcDepID field, and thus would need a like
as follows --> Like "*" or Is Null

Here is what I had as a criteria item, and this (as one would expect)
only returned those fields for which there was a value in the record.
Instead of 5506 records, I got only 429 --> Like
IIf(IsNull([Forms]![frmAllocations]![cboFilterDepartments]),"*",[Forms]![frmAllocations]![cboFilterDepartments])

There is a lot of other stuff going on in the query, but everything is
working just fine. It is only here where I'm having issue. I included
the SQL below, but it can probably be ignored.

Thank you very much for any tips that you can give.

-BDT


SELECT tblProperties.prpYear, tblProperties.prpName,
tblSessions.sesDate, tblSessions.sesTime, tblSessions.sesNumber,
tblTickets.tktAccess, tblItems.itmNumber, tblTickets.tktSuite,
tblTickets.tktSection, tblTickets.tktBox, tblTickets.tktRow,
tblTickets.tktSeat, tblItems.itmAllocateItem, tblArea.araNameCommon,
tblDepartments.depName, [empNameLast] & ", " & [empNameFirst] AS
empNameFull, [usrNameLast] & ", " & [usrNameFirst] AS usrNameFull,
tblItems.itmID, tblItems.itmSesID, tblItems.itmTktID,
tblProperties.prpID, tblAllocations.alcAraID, tblAllocations.alcDepID,
tblAllocations.alcEmpID, tblAllocations.alcUsrID
FROM tblUsers RIGHT JOIN (tblDepartments RIGHT JOIN (tblEmployees RIGHT
JOIN ((tblArea RIGHT JOIN tblAllocations ON tblArea.araID =
tblAllocations.alcAraID) INNER JOIN ((tblTickets INNER JOIN
(tblProperties INNER JOIN (tblSessions INNER JOIN tblItems ON
tblSessions.sesID = tblItems.itmSesID) ON tblProperties.prpID =
tblSessions.sesPrpID) ON tblTickets.tktID = tblItems.itmTktID) INNER
JOIN tblAlcIDs ON tblItems.itmID = tblAlcIDs.itmID) ON
tblAllocations.alcID = tblAlcIDs.alcID) ON tblEmployees.empID =
tblAllocations.alcEmpID) ON tblDepartments.depID =
tblAllocations.alcDepID) ON tblUsers.usrID = tblAllocations.alcUsrID
WHERE (((tblItems.itmSesID) Like
IIf(IsNull([Forms]![frmAllocations]![cboFilterSessions]),"*",[Forms]![frmAllocations]![cboFilterSessions]))
AND ((tblProperties.prpID) Like
IIf(IsNull([Forms]![frmAllocations]![cboFilterProperties]),"*",[Forms]![frmAllocations]![cboFilterProperties]))
AND ((tblAllocations.alcAraID) Like
IIf(IsNull([Forms]![frmAllocations]![cboFilterAreas]),"*",[Forms]![frmAllocations]![cboFilterAreas]))
AND ((tblAllocations.alcDepID) Like
IIf(IsNull([Forms]![frmAllocations]![cboFilterDepartments]),"*",[Forms]![frmAllocations]![cboFilterDepartments])))
ORDER BY tblProperties.prpYear, tblProperties.prpName,
tblSessions.sesDate, tblSessions.sesTime, tblSessions.sesNumber,
tblTickets.tktAccess, tblItems.itmNumber, tblTickets.tktSuite,
tblTickets.tktSection, tblTickets.tktBox, tblTickets.tktRow,
tblTickets.tktSeat;
Click to expand...
 
B

bdt513

Michel,

Thanks for the idea, but that won't work in this case. As I said, I
need ALL records to return, not just those with a value (which is what
would return if I had TRUE in my IIF statement). Also, the issue is
not with the combo box - that is definitely null (I even Set
cboFilterDepartments = Nothing just to make sure while doing my
testing); this issue is with trying to get the query to include a >>
Like "*" Or Is Null << as part of an IIF statment.

Thanks,
BDT

Michel said:
Hi,

Is it possible that cboFilterDepartments is not null, but blank (one or more
spaces).
in SQL view, try the syntax:


... WHERE IIF( 0=len(Forms!frmAllocations]!cboFilterDepartments & "" ),
TRUE, FieldName LIKE "*"& Forms!frmAllocations]!cboFilterDepartments &
"*")


It should also be faster since it does not force the evaluation of LIKE at
all if the parameter is not specified.




Hoping it may help,
Vanderghast, Access MVP


I have a Microsoft Access 2003 query that checks a value in a form's
combo box [cboFilterDepartments] (the combobox returns a integer ID
value) and uses that value as criteria for an integer field [alcDepID].

If the field is populated, I want the query to return the records based
on the value in the field. This would read --> Like
[Forms]![frmAllocations]![cboFilterDepartments]

If the field is null, I want the query to return all records. Not
every record has data in the alcDepID field, and thus would need a like
as follows --> Like "*" or Is Null

Here is what I had as a criteria item, and this (as one would expect)
only returned those fields for which there was a value in the record.
Instead of 5506 records, I got only 429 --> Like
IIf(IsNull([Forms]![frmAllocations]![cboFilterDepartments]),"*",[Forms]![frmAllocations]![cboFilterDepartments])

There is a lot of other stuff going on in the query, but everything is
working just fine. It is only here where I'm having issue. I included
the SQL below, but it can probably be ignored.

Thank you very much for any tips that you can give.

-BDT
Click to expand...
Click to expand...
 
J

John Vinson

Here is what I had as a criteria item, and this (as one would expect)
only returned those fields for which there was a value in the record.
Instead of 5506 records, I got only 429 --> Like
IIf(IsNull([Forms]![frmAllocations]![cboFilterDepartments]),"*",[Forms]![frmAllocations]![cboFilterDepartments])
Click to expand...

Try this instead:

= Forms!frmAllocations!cboFilterDepartments OR
Forms!frmAllocations!cboFilterDepartments IS NULL

This will match the combo box if the combo box contains data, and
return all records if it's null.

John W. Vinson[MVP]
 
B

bdt513

John,

That's perfect! Maybe I'll use that for my other variables as well. I
don't fully understand what's going on behind the scenes, but I'll play
around and see where else that might be applicable.

Thanks a lot for your help!!

John said:
Here is what I had as a criteria item, and this (as one would expect)
only returned those fields for which there was a value in the record.
Instead of 5506 records, I got only 429 --> Like
IIf(IsNull([Forms]![frmAllocations]![cboFilterDepartments]),"*",[Forms]![frmAllocations]![cboFilterDepartments])
Click to expand...

Try this instead:

= Forms!frmAllocations!cboFilterDepartments OR
Forms!frmAllocations!cboFilterDepartments IS NULL

This will match the combo box if the combo box contains data, and
return all records if it's null.

John W. Vinson[MVP]
Click to expand...
 
J

John Vinson

That's perfect! Maybe I'll use that for my other variables as well. I
don't fully understand what's going on behind the scenes, but I'll play
around and see where else that might be applicable.
Click to expand...

There's nothing all that mysterious about it.

A query has a WHERE clause for its criteria. The WHERE clause is a
logical expression which evaluates to either TRUE or FALSE, using the
syntax of Boolean logic (e.g. X AND Y is TRUE if both X and Y are
TRUE, it's false otherwise; X OR Y is TRUE if either X, or Y, or both
are TRUE).

The WHERE clause I suggested has two subclauses separated by the OR
operator: if either the table field is equal to the form reference, or
the form reference is NULL, the entire expression becomes TRUE and the
record is retrieved.

John W. Vinson[MVP]
 
Top