IIF statement in query criteria to have "Like "*" or Is Null" as the result if true
- From: bdt513@xxxxxxxxx
- Date: 17 Jul 2006 16:36:02 -0700
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;
.
- Follow-Ups:
- Prev by Date: Re: What's max limit for MAXLOCKSPERFILE in registry?
- Next by Date: Re: Update Query only works first time
- Previous by thread: Re: SQL Syntax Bug
- Next by thread: Re: IIF statement in query criteria to have "Like "*" or Is Null" as the result if true
- Index(es):
Relevant Pages
|
