how to break the bounds of 59 in data/time data type

[x]

hi
i am a pilot by profession. i want to create a database of my logbook
using ms access 2002. i am facing a problem regarding the format of
time field. when i select "Data/Time" data type for my time field then
this format gives the liberty to record times uptill a figure of 59 in
different sub-formats, whereas i want the format to be able to record
the times like 80:35 or 1:10 or 1138:00. which means that i have these
many hours on a specific aircraft. i also want to carryout sum
operation on these timings. i have found out that i can use "[hh]:mm"
format in ms excel 2002 to suite my requirements but it doesn't work in
access.
i am a very basic user of access or for that matter any database.
please consider this while replying my question. i have no background
of vb code which means that i will only be able to work the things out
which are not very advanced (point and shoot mouse user).
Thanx in advance and please be eleborate in your reply.
bye and tc

 

[Graham Mandeno]

Unfortunately Access does not do time durations very well. (Nor does Excel
for that matter).

The date/time data type is really for storing absolute *points* in time,
rather than durations. That is why a duration longer than 24 hours starts
getting formatted as some date in the distant past - for example, 25h 30m
would be formatted as "31-Dec-1899 01:30".

I have come to the conclusion that the best way is to use a numeric field
and store a number of units (minutes or seconds, depending on your
granularity) and then use your own parsing and formatting code to convert to
and from text.

The formatting is easy:

Function MinsToHHMM( Mins As Variant ) as String
If IsNumeric(Mins) Then
MinsToHHMM = (Mins \ 60) & Format( Mins Mod 60, ":00" )
End If
End Function

(note that Mins is a Variant so it can handle a null value in a field)

Parsing depends on your requirements and how flexible you want to be with
data entry. For example, do you want to allow just a single number and, if
so, is it interpreted as hours or minutes; and do you want to allow decimal
points and, if so, should "2.5" be interpreted as 2:05 or 2:30.

Anyway, the technique is to have an unbound text box for data entry. In
your form's Current event, format the field value and store the result in
the textbox. In the textbox's BeforeUpdate event, parse the input and if
it's valid, store the result in the field, otherwise cancel the event.

 

[John Vinson]

hi
i am a pilot by profession. i want to create a database of my logbook
using ms access 2002. i am facing a problem regarding the format of
time field. when i select "Data/Time" data type for my time field then
this format gives the liberty to record times uptill a figure of 59 in
different sub-formats, whereas i want the format to be able to record
the times like 80:35 or 1:10 or 1138:00. which means that i have these
many hours on a specific aircraft. i also want to carryout sum
operation on these timings. i have found out that i can use "[hh]:mm"
format in ms excel 2002 to suite my requirements but it doesn't work in
access.
i am a very basic user of access or for that matter any database.
please consider this while replying my question. i have no background
of vb code which means that i will only be able to work the things out
which are not very advanced (point and shoot mouse user).
Thanx in advance and please be eleborate in your reply.
bye and tc

Unfortunately, the Date/Time field type in Access simply doesn't
support this. There is no [hh] format in Access, as there is in Excel;
Date/Time values are best suited for specific points in time, not for
durations.

I'd suggest storing the flight times in a Long Integer field,
recording minutes - that is, 2:30 would be stored as 150 minutes. You
can format it for display purposes with a calculated field in a query:

ShowTime: [FlightTime] \ 60 & Format([FlightTime] MOD 60, ":00")

If necessary, you can use two unbound textboxes on a Form to enter
hours and minutes, and transfer the result to a bound textbox to store
the minutes. You'll need a little VBA to do this but it's not too
hard. Let's say you have unbound textboxes txtHours and txtMinutes,
and a bound txtFlightTime control. In the AfterUpdate event of *both*
txtHours and txtMinutes put code like

Private Sub txtHours_AfterUpdate()
' check to see if both textboxes are filled in
If Not (IsNull(Me!txtHours) Or IsNull(Me!txtMinutes)) Then
Me!txtFlightTime = Me!txtHours * 60 + Me!txtMinutes
End If
End Sub

Then in the Form's Current event you can fill in the unbound
textboxes:

Private Sub Form_Current()
If Not IsNull(Me!txtFlightTime) Then
Me!txtHours = Me!txtFlightTime \ 60
Me!txtMinutes = Me!txtFlightTime MOD 60
End If
End Sub

John W. Vinson[MVP]

 

[x]

guys thanx for your generous support\
tc