Solved

How does Access know when to put AM vs. PM?

Posted on 2011-03-25
17
474 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:decraneit
  • 4
  • 4
  • 3
  • +2
17 Comments
 
LVL 75
ID: 35218839
Unless you have used a specific Format, it's determined by the Regional settings in Control Panel.

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
0
 
LVL 5

Expert Comment

by:KPAYNE123
ID: 35218857
I would check the Regional settings for Windows.
0
 
LVL 2

Author Comment

by:decraneit
ID: 35218890
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
 
LVL 75
ID: 35218916
0
 
LVL 21
ID: 35218922
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..
0
 
LVL 21
ID: 35218952
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.
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 250 total points
ID: 35218963
"how is it decided which to put, AM or PM, behind the 2:35."
It's not. You have to decide.

That's why ... I personally only use 24 hr time.

mx
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 44

Expert Comment

by:GRayL
ID: 35218985
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
0
 
LVL 2

Author Comment

by:decraneit
ID: 35219056
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. Notice the two highlighted, in green, entries
0
 
LVL 21
ID: 35219096
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.
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 250 total points
ID: 35219351
Another thing, you've had the guy on the job since 4:00 am and it looks like you haven't allowed him/her to take breakfast or lunch;-)  It looks like once you enter the Start Time for the first Job of the day for that person, when you go to a new record, the only thing you should have to fill in is the End Time as the Start Time is always the End Time of the previous record.  Is that automated?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35233467
Thanks, glad to help.  
0
 
LVL 75
ID: 35233500
I just wanted to note that .. the actual answer to the question "How does Access know ..."  is @ http:#a35218963 :-)

IE ... Access does not.

mx
0
 
LVL 21
ID: 35233795
I agree with mx.
0
 
LVL 2

Author Comment

by:decraneit
ID: 36209889
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.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now