Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    5

    Unhappy Unanswered: access97 date formating

    Hi i am trying to set up a class register in a access 97 database.

    i have written a query that shows the time a student signs in and out and works out the total number of hours (signout-signin)

    i want to add this total time together and display it in a form either as total hours or total days and hours

    i have created a unbound text box in the form footer with the formula "=Sum([Total])" that gives me an answer but when the student does more then 24 hours the total displayed is incorrect.

    i would be grateful for any help

    thanks

    stephen

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    Try
    mins = DateDiff("n",starttime,endtime)
    mins = 500
    hours = Int(mins/60) this will give use hole hours
    hours = 8

    tempMins = hours * 60

    tempMins = 480

    Partmin = mins - tempMins

    partmin =500 - 480

    Format(hours, "00") & ":" & Format(PartMin, "00")

    =8:20

    put it into code

    Code:
    Function INOUT(starttime As Date, Endtime As Date)
    Dim Mins As Double
    Dim TempMins As Double
    Dim PartMins As Double
    Dim hours As Double
    
    Mins = DateDiff("n", starttime, Endtime)
    
    hours = Int(Mins / 60)
    
    TempMins = hours * 60
    
    PartMin = Mins - TempMins
    
    INOUT = Format(hours, "00") & ":" & Format(PartMin, "00")
    
    End Function
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Apr 2004
    Posts
    5
    Thanks for your help.

    all i need to do is figure out what to do with the code

    cheers

    stephen

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    Copy the INOUT to a module

    then in a Query Design View

    in the Feild: Hours:INOUT([feildnamestartdate],[feildnameenddate])

    and each time the Query is run it will run the Function
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  5. #5
    Join Date
    Apr 2004
    Posts
    5
    sorry one last question...

    i'v got the total working. but i want to get a total on that. i have put a unbound text box in the form footer and bunged =sum[total] in there but i get a #error any ideas?

    ta

    stephen

  6. #6
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    Quote Originally Posted by smp_uk
    sorry one last question...

    i'v got the total working. but i want to get a total on that. i have put a unbound text box in the form footer and bunged =sum[total] in there but i get a #error any ideas?

    ta

    stephen
    sorry about the long delay

    What I would do is Store the mins or create a Query that create a feild for the Mins

    Mins : DateDiff("n", starttime, Endtime)

    for each record there is a mins you can some this given you TOTAL mins

    mmmmmmmmm

    Your Next question could be
    change the Total mis to Days:hours:min
    LOL

    This next bit is off the top of head need working on

    base on
    Function INOUT
    Function MinstoTime(Mins)

    Days = Int(totalMins/24)

    tempDays = Days * 24

    PartDays = days - tempdays

    now do the Hours bit
    the same as the
    Function INOUT

    now do the min bit
    the same as the
    Function INOUT

    then


    MinstoTime = Format(Days, "00") & ":" & Format(hours, "00") & ":" & Format(PartMin, "00")
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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