Using IIF statement in query to return answer

150 pts.
Tags:
Microsoft Access 2010
Microsoft Access fields
How do I write a IFF statement to return data? I am trying to write a IIF statement to let a student know if they are approved or not approved to play a sport. Here's the statement: Status: IIF([Waiver]="Yes"]) and IIF([Academic]="Yes"), "Approved", "Not Approved" When I do this and hit run, it pops up with a parameter and isn't working correctly. Status is a new column not associated with any other field names but I need it to show Approved and Not Approved. It only comes up with parameter question and isn't displaying what I need to do. Can someone please help me with this?

Software/Hardware used:
Access 2010

Answer Wiki

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

The code that you have below is what I need but I don’t need a parameter to pop up. When I put in that code or my code, and hit run I’m prompted to enter a parameter. I need it to pull info from the academic column and waiver column if both are yes then have approved appear and if not yes then not approved. I have a new column Status that I’m trying to zoom on the title and enter that information in. Now I’m getting invalid syntax.

 

This is the code that I have created: Now I’m getting invalid syntax

Status: IIF([Waiver]=”Yes”, and IIF([Academic]=”Yes”, “Approved”, “Not Approved”)

Discuss This Question: 18  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
    What exactly says that 'parameter question' you get ? Your first IIF function has an extra square bracket at the end, and your second IIF call has two of its parameters out of the function call.  I don't think that is the exact code you are trying.  Could you please post your code? You might need something like this:
    IIF([Waiver]="Yes",IIF([Academic]="Yes","Approved","Not Approved"),"Not Waiver")
    70,220 pointsBadges:
    report
  • loggerhead79
    The code that you have below is what I need but I don’t need a parameter to pop up. When I put in that code or my code, and hit run I’m prompted to enter a parameter. I need it to pull info from the academic column and waiver column if both are yes then have approved appear and if not yes then not approved. I have a new column Status that I’m trying to zoom on the title and enter that information in. Now I’m getting invalid syntax. This is the code that I have created: Now I’m getting invalid syntaxStatus: IIF([Waiver]=”Yes”, and IIF([Academic]=”Yes”, “Approved”, “Not Approved”)
    150 pointsBadges:
    report
  • philpl1jb
    Perhaps like this?  IIF(([Waiver]=”Yes”) and ([Academic]=”Yes”), “Approved”, “Not Approved”)
    51,355 pointsBadges:
    report
  • philpl1jb
    But that assumes that the columns Waiver and Academic contain "Yes" or "No" not a true/false button or the value "YES" or the value "yes".
    51,355 pointsBadges:
    report
  • loggerhead79
    I tried that but I'm getting the expression you entered has a function containing the wrong number of arguments.
    150 pointsBadges:
    report
  • loggerhead79
    The Waiver has Yes and No. The Academic has Yes and No for the answers and not
    150 pointsBadges:
    report
  • philpl1jb
    51,355 pointsBadges:
    report
  • loggerhead79
    I tried that link but it didn't help. I got an email back from my professor that has this is in and I tried what he said but all I get for a response is Approved for all answers but some should be not approved. He gave me this to try: Column Name: IIF(Logical Test, IIF( second test, value if true,False),False) IIF([Waiver] = Yes and [Academic] = Yes, "value if True", "value if False") I've tried the 1st suggestion, the 2nd and put the 2 together and all I get is Approved. It doesn't come up with Not Approved. I don't get what I'm missing on this. I can't skip the question since the next 7 feed off of it.
    150 pointsBadges:
    report
  • carlosdl
    The first suggestion you got from your professor is what I suggested from the beginning.    If you are being asked for a parameter it means that either [Waiver] or [Academic] are not valid field names.
    70,220 pointsBadges:
    report
  • loggerhead79
    It's not asking for a parameter any longer after I tried the professor's suggestion but the query is only returning approved for everything.  It's not recognizing the not approved if it's not true and false and only shows approved even when it shouldnt be. I sent him another email but I'm beyond confused at this point. I'm not getting any errors when I run the query but it's not returning accurate data.
    150 pointsBadges:
    report
  • carlosdl
    Why don't you post the code you are actually trying?
    70,220 pointsBadges:
    report
  • loggerhead79
    Status: IIF ([Waiver] = Yes, IIF [Academic] = Yes, "Approved", "Not Approved") The query only returns the word approved. It's ignoring Not approved. Only the word Approved shows but it shouldnt.
    150 pointsBadges:
    report
  • carlosdl
    Are you copy-pasting your code?  I don't see how that code could even be accepted by Access, since it is incomplete.
    70,220 pointsBadges:
    report
  • loggerhead79
    No. I'm not copy and pasting the code. It's accepting the code and not giving me an error message that it's missing information. What is incomplete with the code?
    150 pointsBadges:
    report
  • carlosdl
    Look at each of your IIF function calls separately.  Each one should be used this way: IIF(<condition>,<value if true>,<value if false) Your inner IIF call doesn't even include paranthesis, and it is not clear whether the "Approved" and "Not Approved" values are part of the inner or the outer function call. I seriously recommend trying to understand how the IIF function works  before trying other options in your query.  Once you understand that, you will write your query easily.  The link Phil suggested could help you on that.  Good luck.
    70,220 pointsBadges:
    report
  • philpl1jb
    Perhaps like this ...      IIF([Waiver] = "Yes", IIF([Academic] = "Yes", “Approved”, “Not Approved”),"Not Approved")     You need quotes around the "Yes" and () around the functions 
    51,355 pointsBadges:
    report
  • loggerhead79
    Thanks. My professor advised me that with the IIF that we only need " " around text statements and not around values and other things. I'll try out the rest and see what happens. Thanks for your help.
    150 pointsBadges:
    report
  • philpl1jb
    Perhaps some miscommunication. If a test is a numeric field and value no quotes If a value is a literal constant like true or false (do these exist in access?) no quotes. If a test is of character type then the value must be in quotes. The problem you were having where it asked for parameter values .. it meant what is the value for the variable Yes that I see in these equations.  If you had used "Yes" it would use this value instead of assuming that it is the name of a variable. Hope this helps Professor Phil   The
    51,355 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