Time reformating

[Bunky]

I have a date/time field that is driving me nuts! When the client enters a
time in short time format, the 23:45 is supposed to representing 23 minutes
and 45 secs. When I look at the table, it is stored as 23 hours, 45 minutes
and 0 secs. How can I get the field to represent 23 minutes and 45 sec.?

I initially was going to change the table to two fields defined as numbers.
However when I need to average a group of times for one person, a lot of
problems occur. If it is possible to force the table to store as I
indicated, life would be better!

Thanks,
Kent

 

[Daniel]

Wouldn't your customer have to enter 0:23:45 because date in normally
considered hh:mm:ss.

You could always use vba to check the format enetered and make ajustments.

If 00:00 then add leading 00:00:00

Just a though, maybe some others will have a definitive answer this is just
gut instincts.

Daniel

 

[Bunky]

Nice try but we have an Input Mask running
00:00;0;_

What should I be using? I thought this would work.

 

[George Nicholson]

in short time format, the 23:45 is supposed to representing 23 minutes

and 45 secs.

Huh?

Access's predefined "Short Time" format is documented as storing time in a
24 hour (military time) format.

an entry of 23:45 = 11:45 PM = 23 hours, 45 minutes from midnight.
an entry of 23:45:56 will still store the 56 seconds, but Short time format
only displays hours & minutes, so you won't see the seconds unless you
change the format, you'll just see 23:45.

Access will record seconds appropriately if the user enters 0:23:45,
however, if you want seconds to display you would need to change the field
format to hh:nn:ss (or maybe nn:ss)

Nothing can change the fact that Access stores all the information in a
DateTime field, regardless of how you display it. However, if data is being
entered via a form you could intercept it in the BeforeUpdate event of the
control & transform it to something more to your liking as long as you KNOW
that the user will *NEVER* need/want to enter hours greater than 0.:

if Hour(myTimeField) > 0 then
myTimeField = TimeSerial(0,Hour(myTimeField),Minute(myTimeField) )
end if

This will fail if any entries are less than 1 minute. (what is intended as
40 seconds will still be stored as 40 minutes unless they enter the full
0:0:40). Editing the field will likely be confusing to the user as well, so
I doubt that this is a good approach overall.

Another option: don't use a DateTime field. Use separate minutes & seconds
integer fields instead, then combine them as necessary in your queries &
what not using TimeSerial(0, myMinutes, mySeconds). When you need to
average, etc. you need to do it in 2 steps: first create a query that
combines Minutes & seconds into a single time value (for each record) using
TimeSerial, then create a 2nd query that averages that single value across
all records, record groupings, etc..


HTH,

 

[Bunky]

I understand what you are saying and thank you for the information. Based on
your information, I have been testing with defining the field as number and
have an input mask of '00:00:00'. So far this seems to be giving me the
results I need however (always a however!), I have converted the time field
into two fields (both number) for minutes and seconds. Now, I wish to put
them back together showing '00: for hours - minutes for minutes and seconds
for seconds but my query is erroring out
Avg Talk Time converted: ("00:",[Minutes],":",[Seconds])

By doing this, I should be able to have a number field with hours always = 0

I tried the time serial but it always had '12' in the hours.

Ideas?