Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Mar 2001
    Location
    Texas
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Use Query Prompt Value in Report (Access 2002, SP2)

    I have a query that requests a begin and end date as criteria for extracting the data. I need to print these dates in the title of the report that uses this query. I have no idea how to do this.

    Any help would be appreciated.

    Thanks,

    Mary

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

    Re: Use Query Prompt Value in Report (Access 2002, SP2)

    If your query has criteria in the form Between [Enter Start Date] And [Enter End Date], you can use these parameters on a report too.

    For example, create a text box with label "Start Date" and control source =[Enter Start Date]. You can set the Format property of the text box to the desired date format.

    If you want to include both parameters in one text box, you must do the formatting in the control source, for example

    ="Sales for the period from " & Format([Enter Start Date], "mmmm d, yyyy") & " through " & Format([Enter End Date], "mmmm d, yyyy")

    In all cases, take care to use the parameters in exactly the same way as in the query.

  3. #3
    Lounger
    Join Date
    Mar 2001
    Location
    Texas
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use Query Prompt Value in Report (Access 2002, SP2)

    Thanks! I knew there was a way to do it but wasn't seeing it. Works like a charm.

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

    Re: Use Query Prompt Value in Report (Access 2002,

    Hans, this certainly does work.

    However, when I do it (using both dates in the control source of the text box), I have to re-enter the start date (and sometimes the end date). What am I including that shouldn't be there so I only enter the dates once?

    TIA

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

    Re: Use Query Prompt Value in Report (Access 2002,

    You must make sure that the spelling of the parameters is exactly the same in the query and in the expression on the report. The slightest difference will make Access think that you have two different parameters.

    If that still doesn't help, declare the parameters in the query:
    - Open the query in design view.
    - Select Query | Parameters...
    - Enter the first parameter, e.g. [Enter Start Date], in the Parameter column.
    - Select the correct data type (Date/Time in this example.)
    - Repeat for the other parameter.
    - Click OK.
    Here too, you must be very careful to enter the names of the parameters exactly the way they are used in the query.

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

    Re: Use Query Prompt Value in Report (Access 2002,

    You were absolutely right, Hans. I had a colon after one of the date field parameters. <img src=/S/groan.gif border=0 alt=groan width=16 height=15> <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

    Thanks!

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

    Re: Use Query Prompt Value in Report (Access 2002,

    Computers are finicky...

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

    Re: Use Query Prompt Value in Report (Access 2002,

    Absolutely. However, my eyes just didn't "see" the colon at all (which is why I try not to proof my own writing) <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Posting Permissions

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