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

cssc1 used Ask the Experts™
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
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

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,


Yes, I am new to vba, an example would help
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

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
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2015
Distinguished Expert 2018
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.

Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Hate to pile on, but I could not agree more with Gustav. Your database is badly in need of normalization.


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 Programming
Top Expert 2015

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.



Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial