Re: IIF statement in query criteria to have "Like "*" or Is Null" as the result if true
- From: "Michel Walsh" <vanderghast@VirusAreFunnierThanSpam>
- Date: Mon, 17 Jul 2006 19:59:24 -0400
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
<bdt513@xxxxxxxxx> wrote in message
news:1153179362.599440.288290@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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;
.
- References:
- Prev by Date: Re: Update Query only works first time
- Next by Date: Re: Query To Retrieve Less Than or Equal To
- Previous by thread: IIF statement in query criteria to have "Like "*" or Is Null" as the result if true
- Index(es):
Relevant Pages
|
|
