Results 1 to 5 of 5
  1. #1
    Join Date
    May 2009
    Posts
    48

    Unanswered: How do I use a form to select 2 dates and use report to sum cost between the dates

    Hello all.
    I am creating a database to track repair costs on products. I want to use a form to select between 2 dates entered in the 'DATE SERVICE CLAIM FILED' column of my 'RepairInformation' table. I then need to click a button to open my 'Project Status Report' report and sum the associated costs in the 'Cost' column of the 'RepairInformation' table.

    I have a form with combination boxes named 'BeginDate' and 'EndDate' and a button to open the report. I have the 'BeginDate' and 'EndDate' displayed on the report. Now I need to sum the costs from the 'RepairInformation' table between the selected dates.

    If anyone can help me it would be greatly appreciated since I am still learning how to build Access databases.

    Thanks,
    Andrew

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    How do you compute the cost in regard with the elapsed time, hour fee ? and where is it stored?

    For computing the elapsed time, you can build a query and base your report on it (here for computing the time in hours):
    Code:
    SELECT Table_1.*, DateDiff("h",[Time1],[Time2]) AS ElapsedTime
    FROM Table_1;
    Have a nice day!

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    a couple of steps left:
    "I am creating a database to track repair costs on products. I want to use a form to select between 2 dates entered in the 'DATE SERVICE CLAIM FILED' column of my 'RepairInformation' table. I then need to click a button to open my 'Project Status Report' report and sum the associated costs in the 'Cost' column of the 'RepairInformation' table."

    I have a form with combination boxes named 'BeginDate' and 'EndDate' and a button to open the report. I have the 'BeginDate' and 'EndDate' displayed on the report. Now I need to sum the costs from the 'RepairInformation' table between the selected dates.

    From there....

    I'd edit the recordsource for the report and make it a query (if it isn't) and put in criteria under the matching BeginDate and EndDate fields. (ie. under a field called StartDate which represents BeginDate, add in criteria: >=Forms!MyFormName!BeginDate

    The BeginDate field on the report should also probably have a reference in it's control source to the value in the BeginDate on your form (ie. =Forms!MyFormName!BeginDate) for the control source.

    Usually your "criteria" type form is a popup form and BeginDate/EndDate fields are UNBOUND fields. You don't close the form and open the report but instead, miminize the form and open the report (that way the report can sill see the values.)
    Last edited by pkstormy; 08-28-09 at 03:06.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    May 2009
    Posts
    48
    Thank you for your replies Sinndho and pkstormy.

    The cost values are stored in the table and are the result of a quantity*price calculation. So if the values in the table between the selected dates were 5, 8, 123, 55, and 42, the sum should be displayed on the report as 233.

    I will plug in your suggestions and see what I can get to work.

    Thanks,
    Andrew

  5. #5
    Join Date
    May 2009
    Posts
    48
    Hello again.

    I have tried a few variations of the suggestions above but I am not familiar enough with Access to get this operational. I have attached a copy of what I am working on and actually have the 'RepairInformationrpt' displaying the selected begin and end dates. I don't understand how to code the 'RepairInformationqry' to calculate the sum of the costs between the dates selected so it can be displayed on the report.

    Eventually I want the report to display this information for more than one selected date range but if someone can help me get the first one to work, I think I can do the others.

    Any help is much appreciated.

    Thanks,
    Andrew
    Attached Files Attached Files

Posting Permissions

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