Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Pontiac, Michigan, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reports-CaseOf (XP)

    I have a report where I input a parameter in the query the report is based on [which line]. This works fine. But I'd like to use the parameter (which is a 2 letter abbreviation) as the header in the report, only have it lookup the full name. So if I put in MS the report header will say Microsoft. How do I do this? And where? I thought CaseOf might help out but I don't see where it's available to use in Access. (BTW I have the lookup list in a table if I could figure out how to put the two together! <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15> )


    Any thoughts from the brightest people around would be greatly appreciated.

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

    Re: Reports-CaseOf (XP)

    You could add the lookup table to the query, and add the full name field to the query design grid. That way, you can put this field on the report. Another solution is to use the DLookup function:

    =DLookup("[FullNameField]", "[LookupTable]", "[Abbreviation]=" & Chr(34) & [Which line] & Chr(34))

    Substitute the correct names.

  3. #3
    Lounger
    Join Date
    Jan 2003
    Location
    Herndon, Virginia, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports-CaseOf (XP)

    Here's a few ways to do it:

    First, you can use the Select ... Case statement (which is what I suspect you were getting at with CaseOf). Here's an example (assuming you're using a label in the report header, and the parameter field is included in the underlying query and is named ParamField):

    Select Me![ParamField]
    Case "MS"
    Me![lblReportTitle].Caption = "Microsoft"
    Case Else
    Me![lblReportTitle].Caption = "Some other company"
    End Select

    Second, you could use a DLookup in the header's text box (assuming the lookup table is named tblParameterLookup and the full name is called Full Name):

    =DLookup("[Full Name]","tblParameterLookup", "[ParamField] = '" & [ParamField] &"'")

    Third, you could include the lookup table in the underlying query and add the full name field so it shows up in the report. That's probably the fastest solution (from a report creation standpoint).

    Brent

  4. #4
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Pontiac, Michigan, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports-CaseOf (XP)

    Hans and Brent - THANK YOU! Two great ideas. I suspect that DLookup will be the best way. However, I hadn't thought of it when I wrote my question and remembered using Case statements from eons ago in Pascal and thought it might work. However, DLookup would be better since the underlying table could change as need be. I'll fiddle around.

    Thank you for the suggestions. Greatly appreciated. <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

  5. #5
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Pontiac, Michigan, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports-CaseOf (XP)

    Gentlemen, I actually got it to work using DLookup. Thanks for heading me in the right direction. Certainly made my day. The report is perfect (at least until I think of something else to play with on it!). My co-workers are envious of the speedy replies I get from fellow Loungers and I'm gushing gratitude - thanks again!

Posting Permissions

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