Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    11

    Unanswered: Option group displays numeric values in report

    I have an option group on my form and the default numeric values are being stored in the table. My report is displaying the numeric values. How do I get it to display the text values associated with each number?

  2. #2
    Join Date
    Feb 2004
    Posts
    142
    Do you have a table that holds the description of the option values, if so then join to the table in the query for the report.

    If not then you could use an immediate if statement in the report query

    immediate if structure

    iif(trueorfalse test = true,value/text if true,value/text if false)

    if you use text then remember to enclose it in quotes

    you can nest iifs inside another iif

    =iif([optionValue]=1,"Option One Text",iif([optionValue]=2,"Option Two Text",[more iifs]))
    KC

  3. #3
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    11
    Thanks for replying!

    Problem is , I have a table where the option values are being stored as numbers. Then I made a lookup table where the option values are associated with text, then joined them in a query expecting to use the query in the report. Then when I tried to use both the table and the query as source for the report, I got an error saying I couldn't use data from both the table and the query because the table was being used *in* the query. ??!!! That's the whole point. I need the table values translated by the query to retrieve the text values.

    As for the second suggestion, I can't use a true/false immediate iif statement because there are too many options (six), so I tried the nested iif statement. My expression was the following ("status" is the field on my table, and using dummy text to save time):

    =IIf([Status]=1,"One",IIf([Status]=2,"Two",IIf([Status]=3,"Three",IIf([Status]=4,"Four",IIf([Status]=6,"Six",IIf([Status]=5,"Five","not"))))))

    I think the syntax is correct, but I get "#error" showing up in the report. I'm placing the formula in the "control source" of the properties box for the field on the report. Is that where I'm supposed to put it?

Posting Permissions

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