Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Mar 2001
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    IIF function with text field (Access 2000)

    I'm trying to use the IIF function with a text field. =IIF([register]![patient_sex]="F","Female","Male") in a report using the expression builder. The field in the table is a text field with length of 1. All I get on the report is #Error. Using Win98SE with Office 2000 Pro. Any help will be appreciated.

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF function with text field (Access 2000)

    Is this formula =IIF([register]![patient_sex]="F","Female","Male") in an unbound text box?

  3. #3
    Lounger
    Join Date
    Mar 2001
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF function with text field (Access 2000)

    Yes, it is.

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF function with text field (Access 2000)

    As a test on a report I placed two Fields Gender (bound to the query field) and GenderName(unbound).
    The Control Source for GenderName is:
    =IIf([Gender]="F","Female","Male").
    The GenderName textbox correctly displays the text. You can set the visible property of the bound field to No.
    Rupert's reply is important because I think that you need both the bound control and the unbound control for this to work.

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF function with text field (Access 2000)

    Why not set the formula in the underlaying query instead of the report ?
    Francois

  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 function with text field (Access 2000)

    Take out the reference to [register]!, whether that is the report or the underlying recordset. In either case, you only reference the field or the control, not the container. Is [patient_sex] both the name of a control and the name of a field? That can cause circular references and result in an Error as well.
    Charlotte

  7. #7
    Lounger
    Join Date
    Mar 2001
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF function with text field (Access 2000)

    You are absolutely right. Carelessness on my part. I was using the same name for the control and the field. Thanks for jogging my memory.

Posting Permissions

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