Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Iff in a query? (2000)

    I have the follow Iff statement in a query, but need to add another criteria

    Beds: IIf([bedrooms]="1",[bedrooms] & " bedroom",[bedrooms] & " bedrooms" & iff([bedrooms]="0",""))

    I want to say that iff bedrooms = "0" then the output would be ""
    Best Regards,

    Luke

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Iff in a query? (2000)

    Try this:
    <code>
    Beds: IIf([bedrooms]=0,"",[bedrooms] & " bedroom" & IIf([bedrooms]>1,"s",""))
    </code>
    BTW, why do you have quotes around the numbers? I assume that bedrooms is a number field.

  3. #3
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Iff in a query? (2000)

    If I use that I get #error in the field
    Best Regards,

    Luke

  4. #4
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Iff in a query? (2000)

    If might have to do with that the 0 does not exist, if thereis nothing, it is empty
    Best Regards,

    Luke

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Iff in a query? (2000)

    0 is not the same as null (blank, empty). Does this work?
    <code>
    Beds: IIf(IsNull([bedrooms]),"",[bedrooms] & " bedroom" & IIf([bedrooms]=1,"","s"))
    </code>
    If you still get #Error, there must be another problem.

  6. #6
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Iff in a query? (2000)

    still get an error message, having a look at the bedrooms field, it is a text field, so do i need to change this to number?
    Best Regards,

    Luke

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Iff in a query? (2000)

    A number field seems more logical than a text field - or should the user be able to enter something like

    1 (can be split into 2)

    or

    4 (if you're optimistic)

    Then, of course, it would need to be a text field.

Posting Permissions

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