decraneit
asked on
How does Access know when to put AM vs. PM?
We have an Access 2003 database that was created around 1998 in Office 97. We later added a table and form in which people put the start and end times (Medium Time format) for jobs they perform. Because some people had 20 or more entries we did not request that they add AM/PM. We were most concerned about the length of time and could calculate that even if the computer assigned AM when it should be PM. We did not even display that part of the time in the form.
Now we have need for the AM/PM stamp to be correct and that raises the question of what happens when we type 2:35 in the control? How does Access or Windows decide which to put behind the entry? It seems that it guesses well most times but not all times. It is important to understand these entries can be made hours or days later than the times they represent. Any ideas?
Now we have need for the AM/PM stamp to be correct and that raises the question of what happens when we type 2:35 in the control? How does Access or Windows decide which to put behind the entry? It seems that it guesses well most times but not all times. It is important to understand these entries can be made hours or days later than the times they represent. Any ideas?
I would check the Regional settings for Windows.
ASKER
I realize I failed to make one thing clear. We have formatted the field as Medium Time format so it will have to have either AM or PM behind the number. If all we type is 2:35 and hit enter, how is it decided which to put, AM or PM, behind the 2:35.
0 to 11:59:59 is AM.
12:00 to 11:59:59 is PM
Data entry It works like this:
2:35 is always AM
If you want PM you must enter:
2:35 PM or 14:35
If the time span can cross midnight ( 0 ) or the length on a span can be longer than 24 houor you will need to store the date with the time for both stating and ending times..
12:00 to 11:59:59 is PM
Data entry It works like this:
2:35 is always AM
If you want PM you must enter:
2:35 PM or 14:35
If the time span can cross midnight ( 0 ) or the length on a span can be longer than 24 houor you will need to store the date with the time for both stating and ending times..
Opps ..
Should have said:
0 to 11:59:59 is AM.
12:00 to 23:59:59 is PM
This is for data entry.
If you are entering tim in 12hour not 24hour notation you must include PM to gt noon or later.
Should have said:
0 to 11:59:59 is AM.
12:00 to 23:59:59 is PM
This is for data entry.
If you are entering tim in 12hour not 24hour notation you must include PM to gt noon or later.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you run this in the Immediate Pane (Alt+F11):
? format(#2:35#,"hh:mm am/pm")
02:35 am
? format(#14:35#,"hh:mm am/pm")
02:35 pm
? format(#2:35#,"hh:mm am/pm")
02:35 am
? format(#14:35#,"hh:mm am/pm")
02:35 pm
ASKER
OK, here is the form with some entries I have made. Things were going swimmingly as I entered the first 6 entries, but on the 7th entry I got 4:30 AM even though I had been gatting PM right before that. I confess I have been fooling around with some code to try to logically determine the most likely choice but I was having similar experiences before I started checking the entries. 
I agree with mx. Access does not decide AM/PM. It takes whatever you enter.
I only use 24 hour time for data entry. Eliminates the confusing.
I only use 24 hour time for data entry. Eliminates the confusing.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks, glad to help.
I just wanted to note that .. the actual answer to the question "How does Access know ..." is @ http:#a35218963 :-)
IE ... Access does not.
mx
IE ... Access does not.
mx
I agree with mx.
ASKER
Sorry, but I got busy and missed out on some of this last discussion. In retrospect, your decision is the correct one - I apologize for not seeing that the first time. Thanks.
Keep in mind that ... Access stores dates/times (Date/Time data type) as double precision numbers internally, left of dec pt is the date, right of dec pt is the time. So ... not sure you need to be concerned.
mx