Results 1 to 12 of 12
  1. #1
    millby2000
    Guest

    Report using parameter info in heading of report

    This is probably an easy one - but it is beyond my capabilities.

    I am using Access 97

    The report that I have developed works fine. It asks for a date range before the report is run. I set up in the query criteria the parameters [Beginning Date] and [Ending Date]. The report produces the expected data just fine.

    What I am trying to figure out how to do is to have my Beginning Date and Ending Date, that I establish in my parameters before the report is run, to appear in the heading of the report? I think I have to get the parameter data to appear as a field in the query, right?

    Any suggestions would be helpful. Thanks in advance.

    Steve Metcalf

  2. #2
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report using parameter info in heading of report

    Steve:
    If you have something along the lines of Between [Beginning Date] And [End Date] in the Criteria Row of the query, you can then just insert a text box in the Report Header with a control source along the lines of ="For the period "&[Beginning Date]&" through "&[End Date]

  3. #3
    millby2000
    Guest

    Re: Report using parameter info in heading of report

    Brian,

    Works like a charm! Thanks for your help.

    Now do you know anyway to use the Start Date parameter (from the query criteria) in the above example and subtract it from a date field [DCDATE] in the table that the report is drawing its information from? And secondly, show the difference of that calculation on the report itself?

    What I am trying to do is to run a report based on the parameters (usually the 1st day of the month and the last day of the month). Then have the query/report calculate the number of days from the first parameter entered (1st day of the month) to the [DCDATE] (Day of discharge). The result of this calculation needs to show up on the report.

    Any thoughts are appreciated.

    Thanks,
    Steve Metcalf

  4. #4
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report using parameter info in heading of report

    Yep: =DateDiff("d",[DCDate],[Start Date])

  5. #5
    millby2000
    Guest

    Re: Report using parameter info in heading of report

    Brian,

    You are da man!

    Thanks, it works perfectly!

    Steve

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

    Re: Report using parameter info in heading of report

    Actually, you don't need DateDiff. You can simply subtract one date from the other and use the difference, which will be days.
    Charlotte

  7. #7
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report using parameter info in heading of report

    Charlotte's right of course. I'm just so used to calculating DateDiff in months in my application, it becomes second nature <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  8. #8
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report using parameter info in heading of report

    I have this exact same setup and whenever I run a report based on a parameter query WITH that same textbox criteria in the report header-I am forced to enter the dates multiple times to get it to process. Sometimes I have to repeat the process to get it to spool to the network printer.
    I checked the syntax and it is identical to the parameter criteria. This happens on nearly every report I run.
    What have I been missing all this time?

  9. #9
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report using parameter info in heading of report

    You know, seems to me I have had reports where this problem pops up. But for the life of me, I can't reproduce it. Thought it may have something to do with linked queries but nope <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>. Access help and the KB both say this procedure works, and it does for me. Now you do need to be absolutely certain the criteria and the reference are identical (obviously). I'm going to search further and see if I can locate one of my old reports where the problem exists. In the mean time, if anyone knows of a quirk we're overlooking I'd be interested and I'm sure you would too. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  10. #10
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report using parameter info in heading of report

    Perhaps it is because this report is based on a parameter query that is not passing it's criteria through a form.
    I've thought alot about this in the past and it wouldn't seem too far-fetched that the hierarchy of form to query to table would make it alot easier to pass the value, and that value could be passed when creating a new report.

    Where as a report with a parameter, based on a parameter query could cause some confusion.
    Just a hunch...I'm sure the experts have some thoughts on this.
    Thanks for the help B <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

  11. #11
    Lounger
    Join Date
    Apr 2001
    Location
    Lakeland, Florida 33805-9595
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report using parameter info in heading of report

    If you put the textbox in the header only with code like:
    ="For the period "&[Beginning Date]&" through "&[End Date]
    Access should ask you for the Beginning Date and Ending Date as it is a missing field. I works on my report. I also put in the footer and just use:
    ="For the period "&[Beginning Date]&" through "&[End Date]
    access doesn't ask it for me again.
    I think I also use + istead of & in other MDB.

  12. #12
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Report using parameter info in heading of report

    Having just been through this process in great agony I can say that the problem is almost sure to be one in which the parameters are not being passed from the query to the report. I found that the simplest way to get the correct parameter format was to open the query in SQL form, copy the parameter as it exists in the SELECT portion of the query, then paste this into your text box on a report. Let's face it, if the query can get the paremeter right, then it must work in the report. I did discover that SQL had added .[Value] to the end of the parameter string, but it wasn't necessary in the report. Here is the parameter definition that I inserted into the Start Date text box on my reports: [Forms]![frmStartup]![Starttxt] In this case the Start Date was entered into a text box on the form frmStartup and this was passed to the query from the form.

    I hope all of this is not too confusing. <img src=/S/dizzy.gif border=0 alt=dizzy 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
  •