Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Determing hours between (access 2k)

    I need to determine the amount of hours and minutes between 2 dates and times. but the date and times are in separate fields.

    Is there an easy way to determine the hours and minutes. ?

    Field1 - short date 01/01/2007
    time1 - 24 hours 17:51

    Field2 - short date 05/01/2007
    time2 - 24hr clock 06:03

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Determing hours between (access 2k)

    Try this in a query:
    <code>
    Duration: [Field2]+[Time2]-[Field1]-[Time1]
    </code>
    Alternatively, in the control source of a text box on a form/report:
    <code>
    =[Field2]+[Time2]-[Field1]-[Time1]
    </code>
    In both cases, set the Format property to a date/time format.

    If you need cumulative hours (over 24), it's easier to get decimal hours than hours and minutes, although the latter is possible with some extra work. To get decimal hours (e.g. 4.5 for 4 hours and 30 minutes), use
    <code>
    Duration: 24*([Field2]+[Time2]-[Field1]-[Time1])
    </code>
    and format as a number, not as date/time.

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determing hours between (access 2k)

    I did as suggest Hans, and i get this:-

    Date1 08/05/2007
    Time1 07:12:00

    Date2 15/05/2007
    Time2 11:43:00

    I have a text box on the form, and the format is set to Short Time.

    The text box is currently showing 04:31


    I need to show the number of hours and minutes that have elapsed between the two, in code or whatever.

    I have done searched on the forum Hans, and the datediff functions seems to fall short of whats required. MY current thinking.. is
    would it be possible to concentate the two fields into one, and use that, and get the number of minutes between the two date dates and times, then.. do simple division to get the number of hours and minutes?

  4. #4
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determing hours between (access 2k)

    Edited by HansV to make 2nd date field visible again - the Lounge software has problems with text between brackets [ ] starting with "tr".

    =DateDiff("n",([Platedon]+[PlatedTime]),(<!t>[TreadDate]+[ReadingTime]))/60

    Seems to be giving me the number of hours. or can get the exact minutes

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Determing hours between (access 2k)

    See <post:=633,928>post 633,928</post:>.

    You could use

    =(DateDiff("n",([Platedon]+[PlatedTime]),<!t>[TreadDate]+[ReadingTime])60) & ":" & (DateDiff("n",([Platedon]+[PlatedTime]),<!t>[TreadDate]+[ReadingTime]) Mod 60)

  6. #6
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determing hours between (access 2k)

    Further on from this Hans.. although i can make this work, and give the right amount of hours, by this in a text box. i need to do this via code. (basically because if the last date isn't filled in, i want to use the current date and time)

    Attepting to do this, via this code. i have this running on the 'on current', event of this sub form.
    If i Dim the variables as date, when it runs, i get an error message of 'invalid use of Null'.. if i use type Variant, it will recognise the items in the box on the subform, but then i can't use date manipulation.

    this is the code

    Dim Datemydiff As Integer

    Dim Date1 As Date
    Dim Time1 As Date
    Dim Date2 As Variant

    Dim Date3 As Variant
    Dim Time3 As Variant
    Dim Date4 As Variant


    Date1 = Forms!tblsamples2!tbltestr2.Form.Platedon
    Time1 = Forms!tblsamples2!tbltestr2.Form.PlatedTime
    Date2 = Date1 + Time1

    Date3 = Forms!tblsamples2!tbltestr2.Form.treaddate
    Time3 = Forms!tblsamples2!tbltestr2.Form.ReadingTime
    Date4 = Date3 + Time3

    'Datemydiff = DateDiff("n", Date4, Date2)
    Forms!tblsamples2!tbltestr2.Form.Datex1 = Date2
    Forms!tblsamples2!tbltestr2.Form.Datex2 = Date4

    Forms!tblsamples2!tbltestr2.Form.TotalTime = Datemydiff

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Determing hours between (access 2k)

    You can use the Nz function to avoid the problems with Null values, either in an expression or in code.

    Nz(Something, Date) will return Something if it is filled in, and Date (the current date) if Something is null.

  8. #8
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determing hours between (access 2k)

    I guess the bit im confused at Hans.. is why its returning null... To my thinking i should be declaring the variables in the code as Date.

    When i open the form, the date fields have dates in them etc...

    If i dim them as a Date, i get a null returned, but if i dim them as variant, i get the date returned. I have checked, the table the fields are based on, are correctly set as Date/Time fields for each of the boxes.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Determing hours between (access 2k)

    I think we'd have to see the database. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  10. #10
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determing hours between (access 2k)

    Unfortunately hans, the db is huge, it would take me hours to strip these bits down.

    What i have realised is that the code im running is in the wrong place, its in the 'on current' part of the form.. its needs to be somewhere
    else.. because when i put the code on a button, it works correctly.

    I need to run the code, when the fields have been populated with data.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Determing hours between (access 2k)

    In the Before Update event of the form perhaps?

Posting Permissions

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