Working With Time In Access

This last week I ran into a couple of scenarios where time is being tracked...in particular it's employee shift times.  The client uses a 24 hr system, and needs to capture shift start and end times.  The problem is employees enter times without leading zeroes, or get confused as to how to properly enter a 24hr time setting in Access...you'd be surprised how many people try and enter time in the wrong format, even with Control Tips turned on.

A neat approach to this problem is to create two separate fields for time entering...you can either do it at the form level, or at the table level.  I would suggest using unbound fields at the form level that provide a drop down for hours, and a drop down for minutes.  Force in default settings, then give them an after update event on each field which flags AM or PM as a label to see, and then concatenates the values with appropriate masking and auto fills the actual time field in the table (invisible to the user).

Magic!  You now have a clever time picker to prevent errors for even the most IT challenged employee in your workforce.  Sometimes you have to do a lot of extra work to prevent GIGO - Garbage IN, Garbage OUT....this is one of those cases.