Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    'am' or 'pm' time stamps (VB\VBA)

    I get a table that is sent to us each day with two text fields in it that represent times (see attached) . As you will see, the SessionStart and the Finished_Time each have values like "8:03:21" or "10:51:07" and are text fields. Neither field is of data type DateTime and neither is stampped "AM or PM"

    I need to do a calculation where the Total_Daily_Time = Max(Finished_Time) - SessionStart (SessionStart repeats for each Name). The ActivityID field puts each row in chronological order from earliest to latest. I have a query to get the Max ([ActivityID]) for each Name and therefore associate the Finished_Time to it.

    In a Recordset, I read in each row and create each field to look like a DateTime format by adding the mm/dd/yy before each of the times. I am to the point where each field looks something like this "12/05/2005 8:04:31" However, to do the calculation Total_Daily_Time = (DateDiff("s",[SessionStart],[Finished_Time])/60)/60, I need to know if these times are "AM" or "PM".

    I can make the assumption that any times that begin with 12,1,2,3,4,5 are "PM".

    Any logic help is greatly appreciated.

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: 'am' or 'pm' time stamps (VB\VBA)

    While I can't look at the details of this, you could add 12 hours to all times before some "opening time" like 8 (AM) (i.e., the hour is < 8) . Then, your sequence would be 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, with possible confusion between 8, 9, 10, and 11 AM and PM. Hopefully those evening hours, and the midnight hour, don't matter to your application. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Check out the DateAdd function for doing the "time math."

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

    Re: 'am' or 'pm' time stamps (VB\VBA)

    Does this do what you want?

    SELECT Sample_Data.Name, TimeValue([SessionStart]) AS S, TimeValue([Finished_Time]) AS F, [F]-[S]-0.5*([F]<[S]) AS T
    FROM qryMaxFinish INNER JOIN Sample_Data ON qryMaxFinish.MaxOfActivityID = Sample_Data.ActivityID;

  4. #4
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'am' or 'pm' time stamps (VB\VBA)

    Thanks Hans. The TimeValue function will help with the calculation portion but I still need a way to determine "AM" or "PM". It appears that the TimeValue function assumes "AM" if the argument is not stamped. I have decided that looping through a recordset of each persons Finished_Time and counting the changes in the Hours will help determine if the span of time crosses noon. If that is the case, I know that SessionStart is AM and Finished_Time is PM.

    I put in this block of code....
    =======================================
    Sub TestTime()
    'some other dim statements here...

    Dim intFirstFinish As Integer
    Dim intNextFinish As Integer
    Dim intLastFinish As Integer

    ' I open a recordset here called rsdata...
    ' I get the count of records in rsdata. intRecordCount

    ' val function captures the numbers in the hours portion of the string.
    intFirstFinish = Val(rsData.Fields("Finished_Time"))
    intLastFinish = Val(rsData.Fields("Finished_Time"))

    For i = 1 To intRecCount

    intNextFinish = Val(rsData.Fields("Finished_Time").Value)
    If intNextFinish <> intFirstFinish Then
    'calculation of the number of times the hour changes.

    intHrs = intHrs + 1

    intFirstFinish = intNextFinish

    End If
    rsData.MoveNext
    Next i

    Select Case intHrs
    ' I think I need some logic here that says if the number of hour changes is > some number then
    ' SessionStart is AM and Finished_Time is PM
    ' Else Both are AM or Both are PM -- I don't have this logic figured out yet.

    End Select

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

    Re: 'am' or 'pm' time stamps (VB\VBA)

    Here is another version that uses only queries. Does it calculate the times correctly, in your opinion?

Posting Permissions

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