How to set the format for a field so user must enter time is a certain format?

How to set the format for the fields “Sampling Start Time” and “End Start Time” so the user must enter the time in this format “3:30 PM” or “3:30 AM”. (See attached image.)

Using the formats in the field property.
Adjusting the query so user must enter the correct date
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
format ...

Format is the way data looks ... not what it IS ...  Format can give clues, and trigger other behaviors. It may be good to set the format property to one of the named time formats, or enter a custom format when a Time is expected.  However, this will not stop the user from entering something that is not a valid time.

to answer your question:

To ensure data can be understood as a Time before allowing the user to leave the control, here are a couple ways:

1. InputMask might seem to be the easy choice ... however, there are reasons I would not set it for date/time ... for instance, to use shortcuts like 8a (8:00 am), and 2p (2:00 pm).

2. validate data using form BeforeUpdate event, and/or control BeforeUpdate event

check the value that the user inputs before it is saved.  If you are new to VBA and need an example, please let us know.


have an awesome day,
cssc1Author Commented:
Yes, I am new to vba, an example would help
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
actually, you don't need VBA in this case, since the controls are bound to fields.  In the design view of the tblSilica1 table, the data type of [Sampling Start Time] and [Sampling End Time] should be Date/Time, not Short Text.

Internally, Date/Time stores values as numbers; the whole part of the number represents days passed since 12/30/1899; the fractional part of the number represents time.
0.5 is noon -- halfway through the day
0.75 is 6:00 pm
Get Blueprints for Increased Customer Retention

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Gustav BrockCIOCommented:
You are still storing the times as text, so there is no way to enforce this except by using an inputmask, and users hate these.

To repeat: Always store date and time as data type Date, not text, not numbers, no exceptions.

Also, as I've noted before, and I mean it, you really should read up on how to normalize a database. As it stands your table is a monster with its 190 fields, and it will sooner or later bite you badly.
It will take some time now, but it will only take increasingly longer time as long as you postpone it, and, when done, you won't regret it for a minute. There are lots of guides out there for the browsing.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Hate to pile on, but I could not agree more with Gustav. Your database is badly in need of normalization.
cssc1Author Commented:
Thanks everyone. I have started to normalize the data and have already decreased the table field size form 255 to 190, thank you all
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
cssc, here is a free book you can read to help you with Access:

Access Basics
Free 100-page book that covers essentials in Access

be sure to pay close attention to the Normalization chapter.
cssc1Author Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.