Results 1 to 2 of 2
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Time Values (97 SR2)

    I've got a series of time values (hh:mm:ss) that i'm importing in from Excel. Access wants to read these values as real time, converting it to AM/PM.

    How can I change the format of my main table to reflect this type of data?
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time Values (97 SR2)

    When importing the Excel table, declare the field as a "double" datatype.
    This will allow you to run a time <font color=red>conversion</font color=red> function on the field.

    BTW...Here's a script I found particularly useful

    Function GetTimeTotal ()
    Dim db As Database, rs As Recordset
    Dim totalhours As Long, totalminutes As Long
    Dim days As Long, hours As Long, minutes As Long
    Dim interval As Variant, j As Integer

    Set db = dbengine.workspaces(0).databases(0)
    Set rs = db.OpenRecordset<font color=blue>("MyTable")</font color=blue>
    interval = #12:00:00 AM#
    While Not rs.EOF
    interval = interval + <font color=blue>rs![Daily hours]</font color=blue>
    rs.MoveNext
    Wend
    totalhours = Int<font color=red>(CSng(interval * 24))</font color=red>
    totalminutes = Int<font color=red>(CSng(interval * 1440))</font color=red>
    hours = totalhours Mod 24
    minutes = totalminutes Mod 60

    GetTimeTotal = totalhours &" hours and " &minutes &" minutes"

    End Function

Posting Permissions

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