Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jul 2004
    Location
    UK
    Posts
    43

    Question Unanswered: easy way to display date range on report header

    Hi, I have been working on a MS Access database on behalf of a collegue and I am not very experienced in using it.

    I have managed to create her a report which displays all the data she needs arranged by date. What I would like to do is have the report header say the date range - e.g. report of data from xx/xx/xx to yy/yy/yy. Is there any way of doing this?

    Cheers

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    How are you obtaining your "xx/xx/xx" and "yy/yy/yy" values?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jul 2004
    Location
    UK
    Posts
    43
    Hi,
    sorry bout that: I'm using Access 2000 and the report uses a table as its data source. The column of the table with dates in (DATE) is the significant one and that's how I've set up the report to sort/group the report by using the sorting and grouping tool. By putting a text box in the report header and writing DATE the report shows the start date so I'm nearly there.

  4. #4
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Hi jwab,

    What we're trying to get at is, HOW are you pulling up the report? Then, HOW are you giving it the specific RANGE of DATES that you are wanting to report on? Are you perhaps using a FORM with a QUERY to get your desired dates? If you are only working with (1) a Table and (2) a Report ONLY, then you need to get back to basics and develop your database a bit more.

    have a nice one,
    BUD

  5. #5
    Join Date
    Jul 2004
    Location
    UK
    Posts
    43
    Hi - I should have been more specific I guess.

    The database is in MS Access 2000.

    The report needs to show COMMISSIONS that our DEALERS have earned between a range of dates. To make it quick and easy to run off reports each week I have made a form which allows a user to enter: Date From, Date To and Dealer.

    The user clicks a button and this runs some sql which joins the right tables and queries for the right things. It dumps the results in a table and it is this table that the report gets its data from.

    I have set up the report to get data from the table and it works fine each time. I have set it up to display the results according to date but what I want is for the report to say: report of commissions from x to y.

    Merely putting a text box with DATE in just brings up the first date from the results - I'm wondering if there is an easy way of displaying the last date?

    As you can tell I'm not a very experienced Access user as I'm coming from a background of MySQL.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    lets assume
    your form which you specify the parameters is called frmParameters
    the date from contorl is called tbdatefrom
    the date to control is called tbdateto
    the combobox or control containing the dealer is tbdealer

    place a text box control in either the reports page or report header and call the control something meaningful (eg tbReportHeader)
    in the forms on load event place some vba

    with forms!frmparameters
    tbreportheader="Money extorted by dealer:" & .tbdealer & " between " & tbdatefrom & " and " & tbdateto & " for doing absolutely nothingt as usual"

    you may elect to change the wording slightly.

  7. #7
    Join Date
    Jul 2004
    Location
    UK
    Posts
    43
    Thank you soooo much.

    I'm not quite there yet - it's complaining that I cant assign a value to this object (i.e. the textbox on the report)

    But I think you've set me on the right track.

    Cheers!

  8. #8
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Hello again jwab,

    Simply put, place an Unbound TextBox on your report, then Copy and Paste the below phrase inside it. There ya go.



    ="Report of commissions from " & [EnterBeginningDate] & " Through " & [EnterEndingDate]


    Inside the brackets [] place whatever your Parameter is set for. Mine prompts for [EnterBeginningDate] so that is what I placed there. Whatever yours is just fill that in.

    have a nice one,
    BUD

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Fair point Bud, but if the parameters are being captured in an other form then why re-enter them. You could end up with the possibility that soemone could run the report with say May 2004 figures but the report title claims its January 2005 figures.

  10. #10
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by healdem
    Fair point Bud, but if the parameters are being captured in an other form then why re-enter them. You could end up with the possibility that soemone could run the report with say May 2004 figures but the report title claims its January 2005 figures.
    Hi healdem,

    Though I never ran into that problem, I thought being as it was based on the Query at the time of execution that it displayed only what it was reporting on at that particular time. Once done the values were cleared. At least in the environment we were in, where we all ran many reports simultaneously on varying data, nobody ever got what they didn't ask for. However, do explain to me further of any hiccups that could occur.

    have a nice one,
    BUD

  11. #11
    Join Date
    Jul 2004
    Location
    UK
    Posts
    43
    Hey guys, thanks for all your help. I got it working using the text box method but I couldn't get it working with my own code so I used:

    control source -> and expression builder.

    I then chose forms-> loaded forms -> my form -> [appropriate date field]

    Thanks again for all your help.

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Bud
    I am happy that if the parameter is based on the query then it works fine
    By placing the call for the parameters that populates the report header aswell as for a query then you run the risk that the second set of figures may not be the same as the first set. Perhaps you don't have the same devious people using databases as I seem to......

    Put simply the date parameters in this thread are being captured in the in a parent form then these can populate both the query and the report title from the same time. Should go without saying that you lock down the parameters immedaitely before calling the report just in case a (mis/ab)user chages the parameters once the query has accepted them....

  13. #13
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Date fields

    Why not just:

    1. Have a form called: ReportForm (PopUp form).
    2. Have 2 text box fields on this form (one called: StartDate and one called: EndDate.) The user will enter the StartDate and EndDate (these are unbound with no Control Source.)
    3. Base the parameters for your query on the report (i.e. for the date field criteria: Between Forms!ReportForm!StartDate and Forms!ReportForm!EndDate).
    4. Have a button on the ReportForm which when clicked:
    a. Minimizes or makes invisible the ReportForm
    b. Opens and maximizes the Report.
    5. Have 2 unbound text fields in the Report. Set the Control Source for 1 of the text boxes to: =Forms!ReportForm!StartDate and the other text box Control Source: =Forms!ReportForm!EndDate.
    6. After the Report is closed (i.e. On_Close):
    Forms!ReportForm.setfocus
    Docmd.restore

  14. #14
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Sample Reporting System

    jwab

    Attached is simple little reporting Access db which utilizes a Start Date and End Date on a form to create a report based on the date range entered and show them in the Header. I saved it in an XP, 2000 and 97 version. Let me know if this helps.
    Attached Files Attached Files
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  15. #15
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by pkstormy
    jwab

    Attached is simple little reporting Access db which utilizes a Start Date and End Date on a form to create a report based on the date range entered and show them in the Header. I saved it in an XP, 2000 and 97 version. Let me know if this helps.

    Hey there pkstormy,

    I have pretty much the same thing. From the Form you click to run the Report, up pops a dialog box to let you input Begin and End date in separate boxes then Click to open the Report. The Report shows the dates you entered. Also a button to clear out the entries in case of incorrect entry, that to avoid user having to select and delete.

    BUD

Posting Permissions

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