 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / July 2006IIF statement in query criteria to have "Like "*" or Is Null" as the result if true
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; 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 [quoted text clipped - 54 lines] > tblTickets.tktSection, tblTickets.tktBox, tblTickets.tktRow, > tblTickets.tktSeat; 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 > Hi, > [quoted text clipped - 36 lines] > > > > -BDT >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]) 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] 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!! > >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. [quoted text clipped - 10 lines] > > John W. Vinson[MVP] >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. 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] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.