Home > IT Answers > Database > Access 2007 IIF statement in query criteria
Scubacy
25 pts.
Q:
Access 2007 IIF statement in query criteria
IIF statement, Access 2007, Microsoft Access
This is a query design looking at a checkbox on a form, if its checked, I would like to return only the database fields (ConfigData) that contain text. If it isn't checked, return all the fields whether they have text or not...Pretty basic filter I thought
Seems like I'm getting close but can't seem to get it.

IIf([Forms]![Main Form]![chkConfigNote]=True,[WorkPacks].[ConfigData]>"",[WorkPacks].[ConfigData])

Thanks for any help!

Software/Hardware used:
MS Access 2007
ASKED: Nov 12 2009  5:04 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
CCrider66
15 pts.
A:
 RATE THIS ANSWER
-1
Click to Vote:
  •   0
  •  -1
  • Bookmark and Share 

 It looks like you are trying to use the IIF statement to branch query results when it is only meant to provide an either or value for a single field.
 
 Example:  If the check-box is true, fieldA is this value.  If the check-box is true, fieldA is another value.
 
 You are trying to use it to put a where statement in a query based on the value of the chkConfigNote field.  You would have to use an If-Else Statement for that.
Last Answered: Nov 13 2009  6:55 PM GMT by CCrider66   15 pts.
Latest Contributors: Carlosdl   32715 pts.
  •   
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Carlosdl   32715 pts.  |   Nov 12 2009  2:45PM GMT

Could you please post the SQL of your query, to see where you are using that code ?

I think you could use the NZ or LEN functions, but we would need to see the complete query to make a suggestion.

 

Scubacy   25 pts.  |   Nov 12 2009  3:57PM GMT

Below is the SQL that the query wizard produced.
I have 18 fields and I’m filtering most of them with a checkbox against a “yes/no” field so those are working. But one db field is a memo type that I am filtering with a form checkbox and that’s where I’m having the problem.

The >”" isn’t working in the statement, however the IIF([forms].[Main Form].[chkConfigData]=True,[WorkPacks].[ConfigData], “*”) works to filter only memo fields with text. The false portion of the statement should show all fields with or without text, but it shows no records at all..

Thanks for your help!

SELECT *
FROM WorkPacks
WHERE (((WorkPacks.Area)=[Forms].[Main Form].[cboFCOArea]) AND ((WorkPacks.WorkPackage)=IIf([Forms]![Main Form]![cboFCOWP]>0,[Forms]![Main Form]![cboFCOWP],[WorkPacks].[WorkPackage])) AND ((WorkPacks.ConfigData)=IIf([Forms]![Main Form]![chkConfigNote]=True,[ConfigData],”*”)) AND ((WorkPacks.Started)=[Forms].[Main Form].[chkStart]) AND ((WorkPacks.Configured)=[Forms].[Main Form].[chkConfig]) AND ((WorkPacks.QA)=[Forms].[Main Form].[chkQA]) AND ((WorkPacks.FCODocBuilt)=[Forms].[Main Form].[chkDocBuilt]) AND ((WorkPacks.Screenshot)=[Forms].[Main Form].[chkScreenshot]) AND ((WorkPacks.LoadedEPKS)=[Forms].[Main Form].[chkLoaded]) AND ((WorkPacks.SlopeReceipt)=[Forms].[Main Form].[chkReceipt]) AND ((WorkPacks.SlopeFCOStart)=[Forms].[Main Form].[chkFCOStart]) AND ((WorkPacks.SlopeFCOComplete)=[Forms].[Main Form].[chkSlopeFCOComplete]) AND ((WorkPacks.HMITest)=[Forms].[Main Form].[cboHMITest]))
ORDER BY WorkPacks.DisplayName;

 

Scubacy   25 pts.  |   Nov 13 2009  9:08PM GMT

I thought the second apostrophe was the “Else”. IIF(statement, do this if its true, do this if its false (Else))
Maybe I should restate the question; IIF(chkbox=true,show only memo fields with text, else show all memo fields) What I have so far will filter the memo fields with text but will not show all if unchecked
IIf([Forms]![Main Form]![chkConfigNote]=True,[WorkPacks].[ConfigData],Not sure what to put here to show all) “*” didnt work

Thanks again.

 

Carlosdl   32715 pts.  |   Nov 14 2009  12:32AM GMT

Can you modify the query’s SQL to try something like this ?

… AND (NZ(WorkPacks.ConfigData)=IIf([Forms]![Main Form]![chkConfigNote]=True,[ConfigData],NZ([ConfigData]))) AND…