Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    report needs to show 3 options in a report (2000)

    I have a table with three fields--call them [choice1], [choice2] and [choice3]. I want a report that will show the data from whichever of those three fields is filled (just one text box showing the data from whichever of the three choices was selected). I don't know if I need an "If, then, else, else" or how to do it!

  2. #2
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: report needs to show 3 options in a report (20

    Is there any advantage to setting that up in the query somehow, or just as good to do it in the report?

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

    Re: report needs to show 3 options in a report (2000)

    You can use the Nz function in the Control Source property of the text box:
    <code>
    =Nz([Choice1],Nz([Choice2],[Choice3]))
    </code>
    The Nz function returns its first argument, unless that is null (blank), then it returns its second argument. Here, two Nz functions are nested. It is also possible to use the IIf function, but that's messier:
    <code>
    =IIf(IsNull([Choice1]),IIf(IsNull([Choice2]),[Choice3],[Choice2]),[Choice1])
    </code>
    If Choice1 etc. are all text fields, and if you are sure that just one of them is populated for any given record, you can simply concatenate them:
    <code>
    =[Choice1] & [Choice2] & [Choice3]</code>

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

    Re: report needs to show 3 options in a report (20

    A query is more efficient, in theory at least, but the difference is probably only measurable if you are dealing with hundreds of thousands of records.

    If you need the result (choice 1 or 2 or 3 in one field) in other places in the database, a query is definitely better.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: report needs to show 3 options in a report (20

    OOPS! The "Nz" worked, except I realized some may not choose any one of the three choices--how can I specify to leave blank if none of the three is chosen?

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

    Re: report needs to show 3 options in a report (20

    If all of the choices are left blank, the expressions I suggested will all return a blank too, so that should be OK.

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: report needs to show 3 options in a report (2000)

    Hans
    You suggested:
    =[Choice1] & [Choice2] & [Choice3]
    Don't you mean:
    =[Choice1] + [Choice2] + [Choice3]

    I am assuming that they are text fields of course.

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

    Re: report needs to show 3 options in a report (2000)

    According to wsryan, only one of the fields will be populated, the others will be null. Using + will result in null if at least of the arguments is null, so in this situation, =[Choice1]+[Choice2]+[Choice3] will ALWAYS be null. Using & will ignore nulls, and concatenate the non-null arguments, in this situation the one field that is populated.

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: report needs to show 3 options in a report (2000)

    Hans, you are quite right.

    I understood it to be the other way around. I always use & for concatenation.
    When would you use + for concatenation? Ignore this question

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

    Re: report needs to show 3 options in a report (2000)

    Actually, you use + for concatenation when you WANT to return a null if a value is null. For example:

    [LastName] & ( ", " + [FirstName] )

    This will return <code><lastname>, <firstname></code> if both fields are populated. If only the last name is populated, it will return <code><lastname></code> rather than <code><lastname>, </code>
    Charlotte

  11. #11
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: report needs to show 3 options in a report (20

    That expression (now in the query) works perfectly--now I'm stumped by a similar problem: I want to be able to return a line of text if a field is True: Like,

    If [Field1]=True, then "Text1", if not true, look at [Field2]
    If [Field2]=True, then "Text2", if not true, look to [Field3]
    If [Field3]=True, then "Text3", if not true, then leave blank

    How do I do that? Thanks in advance for your help...

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

    Re: report needs to show 3 options in a report (20

    You can use nested IIf functions:
    <code>
    =IIf([Field1], "Text1", IIf([Field2], "Text2", IIf([Field3], "Text3", "")))
    </code>
    Note: Access will remove the spaces between the parts of the expression, I put them in for clarity.

  13. #13
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: report needs to show 3 options in a report (20

    Can I say:
    =IIf(IsNotNull([Field1]), "Text1", IIf(IsNotNull([Field2]), "Text2", IIf(IsNotNull([Field3]), "Text3", "")))

    Or what is the right syntax? Thanks for your patience with me! How's the wather in the Netherlands today?

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

    Re: report needs to show 3 options in a report (20

    IsNotNull doesn't exist, instead use Not IsNull(...), or IsNull(...) = False:

    <code>=IIf(Not IsNull([Field1]), "Text1", IIf(Not IsNull([Field2]), "Text2", IIf(INot sNull([Field3]), "Text3", "")))</code>

    <img src=/w3timages/blueline.gif width=33% height=2>

    We've been having very unusual weather here. Usually, winter is more or less over by the second half of February, but it's been quite cold for almost two weeks now, with the heaviest snowfall in 50 years on the 2nd of March, and record low temperatures for March last night (-20.7 C = -5 F). More snow to come this afternoon. I know it's nothing special for many regions in the US, but it is for us. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  15. #15
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: report needs to show 3 options in a report (20

    My next problem is on a form for people to order a quantity of DVDs for $10.00 each, I have a field [DVDOrd] which I have set as a number for field type. Another field called [DVDFee]. How do I get the right fee to show up in that field (how do I calculate the number in [DVDOrd] X $10.00)?

    Thanks for your help!
    Warren

Page 1 of 2 12 LastLast

Posting Permissions

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