Time format in Access table

S

Shane

I am trying to set up a time format in an Access table.
In the desgin view of the table, I have set the fields format to "Short
time" and the same for the input mask.
The problem is I need to record times in excess of 24 hours i.e 35:00. I can
do this in Excel by using the format [hh]:mm, but this doesn't work in Access.

Please can anyone tell me how to do this.

Thanks
 
A

Alex White MCDBA MCSE

Hi Shane,

I would use a string, rather than a date field if your time exceeds the 24
hour clock, you then need to write functions to do the conversion to
time/date

e.g.

35:00

1 day 11 hours

in a lot of cases using a string for strange date formats can do the trick.
 
S

Shane

Thank you for your reply.

So what format should I recored the time value in the string.
And then how on earth do the convert that to a time format?

Thanks

Alex White MCDBA MCSE said:
Hi Shane,

I would use a string, rather than a date field if your time exceeds the 24
hour clock, you then need to write functions to do the conversion to
time/date

e.g.

35:00

1 day 11 hours

in a lot of cases using a string for strange date formats can do the trick.

--
Regards

Alex White MCDBA MCSE
http://www.intralan.co.uk

Shane said:
I am trying to set up a time format in an Access table.
In the desgin view of the table, I have set the fields format to "Short
time" and the same for the input mask.
The problem is I need to record times in excess of 24 hours i.e 35:00. I
can
do this in Excel by using the format [hh]:mm, but this doesn't work in
Access.

Please can anyone tell me how to do this.

Thanks
Click to expand...
Click to expand...
 
D

Douglas J Steele

Access doesn't really support the concept of time durations: there is no
Time datatype, and the Date/Time datatype is intended to be used to hold a
timestamp (date and time)

A common recommendation is to determine the smallest resolution you need
(minutes? seconds?) and store your times as Long Integers in that unit
(i.e.: store 35:03:23 as 126203 seconds). You can then do arithmetic very
easily on them, and it's fairly simple to write a function that converts
that number back to hh:mm:ss format. The following aircode is untested, but
should give you an idea:

Function FormatTime(TimeInSeconds As Long) As String
Dim lngHours As Long
Dim lngMinutes As Long
Dim lngSeconds As Long

lngHours = TimeInSeconds \ 3600
lngMinutes = (TimeInSeconds - _
(lngHours * 3600)) \ 60
lngSeconds = TimeInSeconds - _
(lngHours * 3600) - lngMinutes * 60

FormatTime = Format$(lngHours, "0") & ":" & _
Format$(lngMinutes, "00") & ":" & _
Format$(lngSeconds)

End Function

For another possibility, see my October, 2003 "Access Answers" column in
Pinnacle Publication's "Smart Access". You can download the column (and the
accompanying sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html
 
R

Rick B

Sounds like you are trying to store a DURATION not a TIME.

Durations should be stored as numbers. Depending on your application, you
may want to store hours and portions of hours (1.5 hours) or number of
minutes, or even number of seconds.
 
Top