Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2003
    Posts
    22

    Angry Unanswered: Date and Time calculating

    For those of you who use MS access, I have a time calculation question. In a report several records meet criteria from a query. One of the fields that I'd like to calculate in the Report is the amount of time that has transpired over a group of Records. (In my query the individual Date and Time Fields were 1st combined into a single Date/Time Field MM-DD-YY HH:MM). I tried to Sum the combined Date & Time Fields of the grouped records in the Report but had no luck with elapsed time.

    Thanks,

    Bill

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you have a query yourQuery containing a field yourDate which returns a number of rows.

    try this:

    firstDate = Dmin("[yourDate]", "yourQuery")
    lastDate = Dmax("[yourDate]", "yourQuery")
    deltaDays = DateDiff("d", lastDate, firstDate)

    or

    Control Source = DateDiff("d", Dmax("[yourDate]", "yourQuery"), Dmin("[yourDate]", "yourQuery"))

    the interval "d" can take other values: check help for DateDiff.


    izy

  3. #3
    Join Date
    Jan 2003
    Posts
    22

    Talking

    Many thanks for your help; this looks to be helping with my problem.

    Bill

  4. #4
    Join Date
    Jan 2003
    Posts
    22
    On 2nd look, I got the following (which does not format well in this form but let me explain).

    My actual date and time is not reported in the FirstDate or LastDate Fields using the suggested DMin, DMax, DaysDelta idea. Odd, but 7/30/2002 is derived from the DMin/max expression.

    Any ideas would be appreciated,

    Thanks,

    Bill

    DateTime FirstDate LastDate DeltaDays
    12/5/2002 8:39:23 PM 7/30/2002 10:26:46 AM 1/2/2003 10:44:06 PM 156
    12/5/2002 8:56:24 PM 7/30/2002 10:26:46 AM 1/2/2003 10:44:06 PM 156
    12/5/2002 10:27:03 PM 7/30/2002 10:26:46 AM 1/2/2003 10:44:06 PM 156
    12/6/2002 12:46:51 AM 7/30/2002 10:26:46 AM 1/2/2003 10:44:06 PM 156
    12/6/2002 2:10:20 AM 7/30/2002 10:26:46 AM 1/2/2003 10:44:06 PM 156
    12/6/2002 4:15:04 AM 7/30/2002 10:26:46 AM 1/2/2003 10:44:06 PM 156

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ouch!!!!

    this was one of the rare ones i actually tried before replying, so i'm pretty happy that it works.

    are you putting DMax/DMin in the query itself????
    you REALLY _really_ really don't want to do that!



    let's try again:

    --you have a query "yourQuery" that returns the records that you want and the records returned each include a field "DateTime".

    --your query DOES NOT include the words "DMin" or "DMax" _ANYWHERE_

    --your query DOES NOT return anything that pretends to be FirstDate LastDate or DeltaDays

    --you promise not to touch your query again until this experiment is over.

    --now make a new unbound form and add to it a new unbound text box and set the text box control source to:
    =DateDiff("d", Dmax("[DateTime]", "yourQuery"), Dmin("[DateTime]", "yourQuery"))

    --save the new form, and open it:

    ??? did it work.
    --if YES: happy new year!!
    --if NO: please post the SQL from your query AND the rows returned from your query AND whatever showed up in your text box during the experiment.


    izy

  6. #6
    Join Date
    Jan 2003
    Posts
    22

    Talking Date/Time calculating w/Access

    Many thanks, I'll give it a determined effort tonight.

    Bill

  7. #7
    Join Date
    Jan 2003
    Posts
    22

    Re: Date/Time calculating w/Access

    Good morning,

    Progress has been made but there is still a problem sorting out the answer that I am looking for. You have correctly identified the problem in your last post.

    I made a Form with an unbounded Text box with the following Control Source:

    =DateDiff("d",DMax("[DateTime]","HME Query"),DMin("[DateTime]","HME Query"))

    First though, let me give an example of the records that this applies to in my HME Query. I have several records with dates and times that begin on 7/30/2002 at 10:26:46 AM and ends on 7/30/2002 5:59:00 PM. The elapsed time should be 7 hours and 32 minutes. Instead, a Standard Number of 153 is reported. Also, all groups report elapsed time at 153. I have changed the Format of the reported number to Short Time but get 0:00 for a result. If the Format is changed to a Short Date, the result is 1/5/1900.

    At this point, it appears that the Records are not being looked at as individuals within each group, (because the same number is reported regardless of differences in Dmin and Dmax times in separate Groups). This is a mind teaser in Access which is the preferred software. Excel has no problem reporting correctly but I’d rather not Export to it every time I run a Query in Access.

    Thanks for helping me with this problem.

    Bill

    N. Easton, MA

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    a couple of things to think about --

    -----first

    from my first attempt at reply:
    "the interval "d" can take other values: check help for DateDiff".

    the "d" in the expression calculates days. it can be replaced with
    "q":quarters
    "m":months
    "h":hours
    "n":minutes
    and so on (please check DateDiff help for other possible intervals).

    -----second

    one of your posts reads:
    "DateTime FirstDate LastDate DeltaDays
    12/5/2002 8:39:23 PM 7/30/2002 10:26:46 AM 1/2/2003 10:44:06 PM 156"

    and the mysterious 156 is the DateDiff in "d" (days) between 7/30/2002 and 1/2/2003 ...where on earth do these dates come from!



    let's start all over again.

    these instructions Access-101 and pedantic - sorry ;-)
    please do each step exactly or we will never get to the bottom of this.

    you have a table somewhere that has a field DateTime plus other fields.

    make a new query in design view,
    add your table containing the DateTime field,
    close the add table dialog,
    double-click the DateTime field, (plus any others of interest) into the query.
    add some dummy test criteria for DateTime - use:
    between #7/30/2002 10:26:46# and #7/30/2002 17:59:00#

    KISS: do not add anything else to this query - no formulas, no DMax, no DateDiff, no totals - NOTHING other than simple fields from a single table.

    save the query as qryTest.

    run qryTest
    does it return the records you mention in your last post?
    (if not, don't continue until you find a way to force this saved query to return the records).

    ---

    OK, now we have a saved query which returns a set of records according to some test date criteria.

    close the query and leave it alone.

    ---

    make a new unbound form in design view,
    add a text box called Delta,
    set the control source for Delta to:
    DateDiff("n", Dmax("[DateTime]", "qryTest"), Dmin("[DateTime]", "qryTest"))

    set the box Delta to enabled:false, locked:true

    save the form as frmTest and close it.

    open the form...
    you should now see the difference in minutes between 7/30/2002 at 10:26:46 and 7/30/2002 at 17:59:00

    (if not, open the query in SQL view, copy/paste the query into this site)

    ---

    assuming everything is working as advertised, now replace the dummy test criteria with real criteria:

    open frmTest in design view

    add a couple of more boxes (startDateTime and endDateTime) to frmTest.
    put some default dates for each box (e.g. #7/29/2002# and #7/31/2002#)
    open the code window, and in the after update event of each box, add:
    Delta.requery

    close the code window

    save & close frmTest

    ---

    open qryTest in design view

    click in the DateTime criteria box
    and then click on the wizard button in the menu bar
    the formula editor dialog should now open with your existing criteria displayed

    highlight #7/30/2002 10:26:46# with the mouse in the top pane
    in the lower right pane click forms -> all forms -> frmTest
    in the lower middle pane, double click startDateTime

    highlight #7/30/2002 17:59:00# with the mouse in the top pane
    in the lower right pane click forms -> all forms -> frmTest
    in the lower middle pane, double click endDateTime

    {if you are in an older version of Access you might have to tidy up the bracketing - old versions of the wizard sometimes set [form.box] which you must manually tidy to [form].[box]

    save your new criteria,

    save & close qryTest.

    now open frmTest: the delta box should now display the difference in minutes between the first and last record in your database that fits between the startDateTime and endDateTime that you enter on the form.

    still working????

    your real app will not look anything like this of course, but you have now a mechanism for using two values somewhere in your app to get the difference in minutes between selected records.

    ...if you want to play further: DMax (and other domain aggregate functions) also have alternate forms including:

    DMax("[nameOfField]", "nameOfTable", "WHERE ...")
    which you could construct dynamically using an appropriate WHERE clause (see the WHERE in sql-view of qryTest)


    phew!


    izy
    Last edited by izyrider; 01-05-03 at 06:45.

  9. #9
    Join Date
    Jan 2003
    Posts
    22

    Talking Date Time Calc

    Good morning Izy and many thanks for your endurance,

    I have made significant progress thanks to you of course. A couple of feedback comments.

    To the formula: DateDiff("n", Dmax("[DateTime]", "qryTest"), Dmin("[DateTime]", "qryTest")) I added *(-1)/60. (Otherwise, the value reports as a negative number)

    Time reports out incorrectly, (e.g not in Time format 00:00). So, I changed the reported value to a General Number which now reports time as 7.55 hours. This works fine for my purposes.

    I attempted to use this information in a report. Having grouping issues but there is much promise thanks to you. I am not sure if I can use these concepts in Reports that you identify in the Forms instructions but will try it out.

    Many thanks,

    Bill

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    GREAT that something works!

    swap DMax and DMin --- fixes the -1

    change "n" to "h" --- might fix the /60 depending on the result you want.

    as you noticed, DateDiff returns a NUMBER in the ("d", "n" or "h" etc...) interval specified, and not a date/time.

    Dxxx and DateDiff will work fine in a report: your only decision is how to pass the start/end dates to the query.


    izy

  11. #11
    Join Date
    Jan 2003
    Posts
    22

    MS Access Time Calculation

    Izy,

    Thanks for leading me to a successful solution. There are many reports in a variety of DBs that this will be applied to.

    Bill

    Massachusetts, US

  12. #12
    Join Date
    Jan 2003
    Posts
    22

    Date Time Calculation

    Hi Izy,

    I wanted to update you. After many hours of frustration I tried a very simple formula:

    =(Max([DT]))-(Min([DT]))

    This formula gave me the hours and minutes in the Report that I was looking for. In Design View, I applied this formula to the combined Field of DT or Date and Time. Fortunately, each group of records has an elapsed time reporting and a Drand Total is also available for the Overall Group.

    Thank you very much for helping me with this. I realize that Dmax and DMin should have worked for this project but I was not able to figure it out with multiple records that were seperated in groupings.

    Thanks again,

    Bill

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    it's great you got it fixed!

    i'm still an addict of domaine aggregates but i've copy/filed your solution for future use: next step is some timing tests.

    ...funny how simple things can be when the brain is working!


    izy

  14. #14
    Join Date
    Jan 2003
    Posts
    22

    Date Time Calculation

    Izy,

    Further problems but finally a workable solution. I satyed with the Min Max idea but the original method was not reporting correctly when several days elapsed. So, I have placed in the header of the report, (where this works well), the following 3 formulas:


    =Max([DT])
    =Min[DT])
    =(Max([dt])-Min([dt]))*24

    Direct calculation from the records without seperate min or max boxes did not work. What reports now is time for each group of records. In my case, I wanted the elapsed time that patients were receiving a specific mode of treatment for example. In any case, thanks for hanging in with me on this. As I mentioned before, it sure is a lot easier with excel to have done this but manipulation of all the records and inferior reports when compared to Access made me stay with Access.

    Thanks,

    Bill

Posting Permissions

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