Working With Time In Access

Comments

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.

Related Posts

Access 2016 Relationships

Short blog today, this is a great read on relationships in Access 2016: Access 2016 Relationships Thanks to Microsoft for this one!

Microsoft Access Union Query from Hell. Skill Level: Demi-God

The worst query I ever had to write which powers a master report for the database user.  Here it is.  I don't think I can make it smaller, there were too many weird rules, aggregates, and translations in it.  And we needed to pull different columns based on criteria. ...

0 Comments

0 Comments