Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2009
    Posts
    2

    Unanswered: Access Query to total all numbers between two dates in a given field

    I am creating a database to track the number of man hours that are being used in our office. I am doing this in two ways, one that totals the amount of hours people are working (one person working is 8 hours; three shifts =24 hours etc.), the other is how much time is spent doing each task, i.e. 1 hour to compete a specific task). I already have the formula in the query to total the hours for each day. I have one table and one form I am using. The goal is to be able to select a start date and end date, and be able to pull up the total number of hours of working personnel, and each listed task. I currently have a query where I select the criteria to “Between [start date] And [end date]”, and I have used the total function under each field. The problem is that it brings up each day in a different line, instead of one total number for each field between the two dates.

  2. #2
    Join Date
    Oct 2009
    Posts
    204
    One way around this is to build another query off of your existing query, and omit the date field from this one. You can then put your date input criteria in the report header so the range is visible there.

    Personally, I like the way it looks when you add text boxes to a form for the date and have the users enter the dates in those fields, then click a button to view the report. In that case the expression would look like

    Between [Forms]![MyForm]![StartDate] And [Forms]![MyForm]![EndDate]

    Then putting one of those in a text box on the report would return the date.

    Hope this helps!

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    1. Create your form for entering the start date and end date. (ie. have 2 unbound fields on your form, one called: StartDate and the other called EndDate - both these fields will not have a sourceobject (ie. they are unbound).)

    2. Create your query which does the totalling. Under the date column, instead of putting: Between [Start Date] and [End Date], instead put Between Forms!MyCriteriaFormName!StartDate and Forms!MyCriteriaFormName!EndDate. Note that you may need to make this a "WHERE" versus a "GROUP BY" for the date column, otherwise it will group on each date (you'll notice when you change it to WHERE, the visible checkbox for the date column becomes unmarked. If not, it should be unchecked so it's not visible.)

    Note also: if you do need to group by a date, I will often use an expression in another column using the date field such as: GroupingDate: Format([MyDateField],"mm/yyyy") so that it shows all totals by month. Or you can use an expression to group on the first day of the week - ie: StartOfWeek: Format(([MyDateField]-Weekday([MyDateField]))+1,"mm/dd/yyyy")

    When you execute the query, you also need to make sure the form is open (either minimized or invisible).

    (basically the same as chris07tibgs said but he beat me to the post by a few seconds.)
    Last edited by pkstormy; 11-23-09 at 22:54.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Nov 2009
    Posts
    2

    Mission Complete

    Thank you for your help. I ended up creating the second query based on the first query, omiting date field and set no criteria, as it is already used in the first query. I also totaled by sum and total all data. I then created a report using the second query as the control source. I then made a header "Total all data between (in a separate text box) =[start date] and (in a separate text bos) =[end date], so it will automatically insert the between dates that I already entered in the query. Thank you again for your advice.

Posting Permissions

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