By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,705 Members | 1,845 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,705 IT Pros & Developers. It's quick & easy.

Format a specific time in Access

P: n/a
I am very new to access and I am trying to get a database set up to
gather time data. Most, if not all of my data is in the nn:ss format
or minutes and seconds. I am trying to set up a form to enter the
data into a table and then reports to consolidate and display the
data. The first major problem I have run into is I can not enter any
time greater than, I think, 23:00 Access keeps telling me I have
entered data that is not valid. I have all my time fields set as
short time with an input mask of 00:00;0;_

I have been trying to find help on this for a few days now with no
luck. Is there anyone that can help with my format issues?

Thanks,
Craig
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Firstly I was a bit consufed as you mentioned times in the format nn:ss,
and showed the example 23:00 (which is hh:nn).

Anyway this is one of those things that Excel does very well and Access
not so (entering data as date/time that is).
The solution I choose (and tend to stick to) is to store times as long
integers. This would mean a time like 12:34:56 would be stored as the
number 123456. This does make manupilating these "time-numbers" for
arithmetic purposes a bit more complicated, but some of the advantages I
found are are:-

1) Absolute accuracy for all times
2) The ability to store times greater than 235959
3) The ability to store negative times

For the example you suggested (on a form) I would have a text box with
the following properties

Name : TimeBox
Input Mask : 90\:00\:00;0;_
Vaildation Rule: ([TimeBox] Mod 100 Between 0 And 59)
AND (Int([TimeBox]/100) Mod 100 Between 0 And 59)

The functions for adding, subtracting, multiplying these "time-numbers"
are simply a case of converting them to seconds first, doing the
calculations then converting back.

It may seem a bit complicated, but it works for me

Sean
"Just press the off switch, and go to sleep!"

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #2

P: n/a
Sean,

Thanks for the input. I know that 23:00 was not a good expample of
what I was trying to do. In look at it further I can get to 23:59 and
then I get an error if I try to go to 24:00, so now I understand my
format problem. Access is seeig these numbers as MILITARY time hh:nn
when I need it to see them as nn:ss. I need the ability to enter a
number like 45:34 (45 min, 34 seconds).

I will try the properties that you suggested and see if that helps.

Thanks,
Craig
Sean <se*********@TimeaIntLtd.hu> wrote in message news:<41**********************@news.newsgroups.ws> ...
Firstly I was a bit consufed as you mentioned times in the format nn:ss,
and showed the example 23:00 (which is hh:nn).

Anyway this is one of those things that Excel does very well and Access
not so (entering data as date/time that is).
The solution I choose (and tend to stick to) is to store times as long
integers. This would mean a time like 12:34:56 would be stored as the
number 123456. This does make manupilating these "time-numbers" for
arithmetic purposes a bit more complicated, but some of the advantages I
found are are:-

1) Absolute accuracy for all times
2) The ability to store times greater than 235959
3) The ability to store negative times

For the example you suggested (on a form) I would have a text box with
the following properties

Name : TimeBox
Input Mask : 90\:00\:00;0;_
Vaildation Rule: ([TimeBox] Mod 100 Between 0 And 59)
AND (Int([TimeBox]/100) Mod 100 Between 0 And 59)

The functions for adding, subtracting, multiplying these "time-numbers"
are simply a case of converting them to seconds first, doing the
calculations then converting back.

It may seem a bit complicated, but it works for me

Sean
"Just press the off switch, and go to sleep!"

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.