Access 2007 IIF statement in query criteria

25 pts.
Tags:
Access 2007
IIF statement
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. It 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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    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.
    69,045 pointsBadges:
    report
  • Scubacy
    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;
    25 pointsBadges:
    report
  • Scubacy
    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.
    25 pointsBadges:
    report
  • carlosdl
    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...
    69,045 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following