Link to home
Start Free TrialLog in
Avatar of cssc1
cssc1Flag for United States of America

asked on

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

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
Avatar of crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access

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
Avatar of cssc1

ASKER

Yes, I am new to vba, an example would help
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
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hate to pile on, but I could not agree more with Gustav. Your database is badly in need of normalization.
Avatar of cssc1

ASKER

Thanks everyone. I have started to normalize the data and have already decreased the table field size form 255 to 190, thank you all
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.
Avatar of cssc1

ASKER

Thanks