Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Mar 2006
    Posts
    160
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access query IIF error

    Hello,
    I am trying to make a truepart false part field. When the monthly fee status field in my table says active,
    I want the query to say active, and when the table says on hold, I want the query to say on hold.
    I believe this is the right code but i need some help.
    When i try to run this query,
    Expr1: IIf([Monthly Fee Status],"Active","On Hold")


    I get a syntax error "comma"



    Does anyone know how i can make this code run?

    Thanks

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,111
    Thanks
    75
    Thanked 4,336 Times in 4,302 Posts
    What is the DATA TYPE of the field???

    As you have coded it, it would need to be a BOOLEAN field (also known as a YES/NO field if you look at it with the Access table designer).

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,111
    Thanks
    75
    Thanked 4,336 Times in 4,302 Posts
    Note that normally you would use apostrophes in the query, not quotes.

    Code:
    IIf([Monthly Fee Status],'Active','On Hold')
    but that sill assumes the field is a boolean field.

  • #4
    Regular Coder
    Join Date
    Mar 2006
    Posts
    160
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I still get a syntax error. The instructions I have say:

    Add a calculated field named Monthly Fee Status as the last column that equals active if the membership field is equal to Active and equals Not Active otherwise.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,111
    Thanks
    75
    Thanked 4,336 Times in 4,302 Posts
    So what is the data type of the membership field?

    And why would you expect to be able to do an IIF( ) on a field that doesn't exist?

    *READ* the instructions:[quote]
    Add a calculated field named Monthly Fee Status as the last column
    that equals active if the membership field is equal to Active and equals Not Active otherwise.
    [quote]
    membership is the *EXISTING* field, according to that.

    Code:
    SELECT ..., IIF( membership = 'Active', 'Active', 'Not Active' ) AS [Monthly Fee Status] ...
    would be my *guess*. But that's only if membership is indeed some kind of text field.

    If you don't know what data types are and mean, time to find out.
    Last edited by Old Pedant; 08-08-2011 at 04:52 AM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •