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

cssc1
cssc1 used Ask the Experts™
on
PROBLEM
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.)

WHAT I TRIED
Using the formats in the field property.
Adjusting the query so user must enter the correct date
Start-Time-and-End-Time-Format.jpg
Silica_App---V5---Copy.accdb
Comment
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

Commented:
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.

thanks

have an awesome day,
crystal

Author

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

Commented:
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
Commented:
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.

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

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

Author

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

Commented:
cssc, here is a free book you can read to help you with Access:

Access Basics
http://www.AccessMVP.com/strive4peace
Free 100-page book that covers essentials in Access

be sure to pay close attention to the Normalization chapter.

Author

Commented:
Thanks

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