Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Nov 2001
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    IIF Statement (2000)

    If I have a field that has yes or null values what formula woudl I use in a query to make the value "s/c" appear for those fields that contain a "Yes" value?

  2. #2
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF Statement (2000)

    Try this in the field row of the query grid:

    Calculated FieldName: IIF([FieldName]="Yes","s/c",[FieldName])

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF Statement (2000)

    In a private message jprait asked:

    "if There are different membership types such as Regular and Family how would you write this statement? I the following:

    Calculated FieldName: IIF([FieldName]="Regular","",) or IIF([FieldName]="Family","(FM)",) "

    if you only have 2 choices (Regular or Family) you only need one IIF statement:

    Calculated FieldName: IIF([FieldName]="Regular","","(FM)")

    If you have more than one you can use nested IIF statements but that can get confusing. Look at the Switch function in the online help. It can accomplish similar things. If the choices get above say five I would create a custom function passing the value of the membership type and returning a result. Still another option is to create a lookup table and join it to the first table in the query.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: IIF Statement (2000)

    It might be simple to have a lookup table with the substitutions in it, i.e. "Regular" in one field and "" in the other. If field1 is the primary key, the table could simply be added to the query joined on the value in the membership type field in the other table and the substitution made by simply using field2 in the lookup table.
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF Statement (2000)

    I'm sorry, I thought that is what I said.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: IIF Statement (2000)

    You tucked it in at the end of your post. I was merely elaborating.
    Charlotte

Posting Permissions

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