Results 1 to 8 of 8

Need a report with calculated field only within a defined date range

  1. #1
    GarrettB is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Location
    BC, Canada
    Posts
    3

    Need a report with calculated field only within a defined date range

    Hi
    This is my first post in this forum, so apologies if I have posted it to the wrong section

    I have been using Access for a few weeks, and have used SQL before, so I have been able to create basic tables, generate queries and simple reports.
    I have a particular task in mind, and have been googling for hours, but I am going around in circles because I am missing some basic knowledge
    So, if anyone could give me a few pointers on the direction I need to go to do the following, that would be really great thanks:

    Starting point
    I have a single table


    End point
    I want to display a report that outputs a number of fields
    - within a certain date range (start date /end date)
    - with a calculated field
    I want the user to select the start/end dates

    What I tried doing so far
    I created a form, with two date selectors / text boxes, and a single button
    I used the Query Wizard to generate a query which selects ALL from the single table
    I modified the SQL in this query two use two parameters as start/end dates
    I used the Report Wizard to create a report just containing the fields I want from the table
    I then added one more column in the report, initially unbound
    I then created a public function that takes a Record ID as a parameters, and performs the required calculations on the data in that record, using multiple DLookup calls, and returns the result
    I then tried to tie the new column in the Report to this function, by setting the control's Control Source to use this function (although I am not sure I am passing the Record ID parameter to the function correctly)

    At this stage I have bits of ideas, and don't know how to knit them together
    I suspect there is a better approach
    I have purposely not pasted a load of code here because it is just the high-level direction/knowledge I need for now - and maybe I will return with a few specific questions later

    Any help is appreciated

    Regards

    Garrett

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,719
    I'm not clear where you're stuck. Using a form to gather the user's input is good, as is using those controls in the query criteria. Are you wanting the function to use them too? It could refer to the same form controls the query does. You might be able to replace the DLookup's with a recordset, hard to say without seeing them. Either could use the form controls for date parameters.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,428
    I used the Report Wizard to create a report just containing the fields I want from the table
    This might be where you went off track. The report should be based on the query. The query should do the calculations and if need be, call the function, although I'm not following why you need that. Perhaps there are complex calculations or decisions that calls for a function rather than a calculated query field. Then you bind the current unbound report field to the query that does what the report function call does. The report grouping and sorting properties will override any query grouping or sorting so just worry about getting the query data correct unless you need to sort it to make validation easier.

    One thing you must remember wrt reports, datasheets and continuous forms: there is really only 1 textbox of any name - it just gets repeated. It is possible to follow such a path as you're on only to find out that being an unbound control on one of these object types leaves you with the same value in every record in that field. This would not be the case if it's a bound field, but can happen if it's not bound.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.

  4. #4
    GarrettB is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Location
    BC, Canada
    Posts
    3
    I will try and explain further.


    I have type d out some kind of an explanation below, however it seems a bit long winded, and possibly unclear.
    Perhaps it is a clue in to the confusions I am experiencing ��
    Anyway here goes…




    I have a table defined: Tbl_MyTests
    This holds information on all tests performed in a lab, with various start and end times for the equipment used during the tests.
    The user wants a report, within selected dates, on the test time used on groups of equipment according to what tests were used.


    Example:
    If Test A is performed on Tuesday, then the function will add the usage times for Equipment 1, 2 and 3
    If Test B is performed on Tuesday, then the function will add the usage times for Equipment 2, 4 and 5
    If Test CA is performed on Tuesday, then the function will add the usage times for Equipment 1 and 4



    So the form I created lets the user enter the start and end date, and click a button to generate the report.


    The query I defined is only for grabbing the data between the two dates:


    SELECT *
    FROM Tbl_MyTests
    WHERE TestDate >= Date_Start AND TestDate <= Date_End
    ORDER BY TestDate;


    So I suppose it is implied from the above, that the query will be provided with two parameters: Date_Start and Date_End


    I define a report: Rpt_TestHours
    Its Record Source is the query above.
    It also has a field which is meant to display the equipment usage total
    This field’s Control Source is linked to a function to calculate the equipment hours. It includes code that looks like this:


    ‘Find which test was run
    testName = Nz(DLookup("TestName", "Tbl_MyTests", "[ID] =" & Record_ID))
    ‘Read appropriate start/stop times
    timeStart = Nz(DLookup("Start1Time", "Tbl_MyTests", "[ID] =" & Record_ID))
    timeStop = Nz(DLookup("Stop1Time", "Tbl_MyTests", "[ID] =" & Record_ID))
    ‘Calculate total time
    timeTotal = timeStart - timeStop

    Essentially, its job is to read one field - the test name, and based on that, use other fields to perform its calculations. Which fields are used in the calculation depend on the test name field.


    A number of issues


    When the function to open the report is called, I get a Type mismatch error
    Run-time error '13';
    Type mismatch


    Also:
    If I open the report manually, I am prompted for start and end date. The subsequent report seems to populate a few lines, although really slowly, and then it throws an overflow error on one of the Nz(DLookup... lines

    So:
    I am not sure how clear I have presented this.
    And, even if I get this working this way, it seems messy, to have code in one place, a query elsewhere, and a report using both.
    Perhaps it can be done just with a report and with code….

  5. #5
    mike60smart is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    188
    Hi Garrett

    Can you upload a zipped copy of the database?

  6. #6
    GarrettB is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Location
    BC, Canada
    Posts
    3
    Quote Originally Posted by mike60smart View Post
    Hi Garrett

    Can you upload a zipped copy of the database?
    Hi Mike

    I zipped and attached a similar project
    I couldn’t send the original because it is a work project, so I created a simple example from scratch

    I tried to create everything in the same way, but I can’t be sure


    Of the two issues I was happening, this project only has the Type Mismatch issue.
    i.e. I am not getting the overflow error – maybe this is because I do not have much data, or maybe I did something ‘better’ this time. Either way I can go and investigate the differences…


    So for the Type Mismatch error, it is presumably to do with how I am defining / expecting / passing some Date values


    Any help you can provide is much appreciated

    Summary of this zipped project:

    • The Rpt_Usage report seems to work, when selected manually, and dates manually entered in format: YYY-MM-DD
    • However, if you click on the Welcome Form, and then click the Report button, and enter the dates and click Generate Report, you get Run-time error '13' for "Type mismatch" in the b_Gen_Report_Click code, where it tries to pass the two parameters to the query



    Overview of Date usage


    Table: Tbl_MyTests
    Test_Date is Date/Time


    Query: MyTests_Query
    Uses Test_Date from the table


    Form: frm_EnterData
    Txt_Test_Date is Medium Date


    Function: Calculate_Total_Hours
    Does not use/read the date
    Attached Files Attached Files

  7. #7
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,719
    You need delimiters for date fields. You also didn't concatenate properly. Try

    DoCmd.OpenReport "Rpt_Usage", acViewPreview, , "[Date_Start]=#" & DateStart & "# And [Date_End]= #" & DateEnd & "#"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,719
    Oh, and you'll want to take the criteria out of the query to use a wherecondition like you are. Another options is referring to the form for the criteria and opening the report without the wherecondition.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 10-11-2016, 06:17 AM
  2. Replies: 5
    Last Post: 04-19-2016, 04:07 PM
  3. Replies: 13
    Last Post: 12-12-2013, 07:22 PM
  4. Query Date Range Based on Calculated Fields
    By wilsoa in forum Queries
    Replies: 6
    Last Post: 03-08-2012, 02:41 PM
  5. Defined ranges in a calculated field
    By Yeti in forum Access
    Replies: 4
    Last Post: 09-16-2010, 03:38 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums