Military Time - No Colon Separator for Key Entry

[Rocco57]

Hello, Help, and Thank You in advance. First Post Here!



This is probably a very simple problem to solve for someone with more experience than myself.

We have a new contractual requirement to track all time spent on Military contracts and report it to our Prime Contractor annually.

I am trying to put into Access 2010, basically the same functionality that I have built into Excel time sheets for years; that is simple entry of time in military time using 3 or 4 keystrokes depending on the time of day.

I have a table which records an employee's employee number and job that he or she is working on. I have formatted fields to short for Start Time and Stop Time, and I have a Calculated field also formatted short for elapsed time, however I need to have it simplified to where there is no colon separator on key entry in the form to 700 for 0700 hrs military and 1317 for 1:17 PM.

I will be using this database on several computers, but only a couple that will be used for time entry.

I have a rather limited knowledge of Visual Basic and would really like to stay away from that if at all possible. I would need EXPRESSED HANDS ON HELP IN LAY LANGUAGE, Prozac, and 3 or 4 pots of coffee if I had to venture there, but I have a pretty good understanding of databases of several years and database genders, i.e. SB4, Access97, Access03, Access07, OpenOffice.

The Excel spreadsheets work well, but I have found them cumbersome trying to dump all the information into a Master spreadsheet from several different individual spreadsheets and I am using Access as our ERP so it will save time if I just add a simple form to track time from there putting it in the hands of the Repairmen and save someone from data entry later.

Again, Thank You!

[June7]

Are you saving date in one field and time in another? Or do you want user to input those values into separate textboxes and then use VBA to concatenate and save to a single field?

I don't think you can avoid the colon if textbox is bound to date/time field and/or has time formatting.

[Rocco57]

Currently, they are separate fields and I'm using a date picker. I have no plans to do anything other than send it to a report at this time.

[June7]

A date picker will work for the date part but there is no picker for the time part. Regardless of how the time part is entered by user, saving into a date/time field must be as a date/time value and that requires colon separators.

Can try setting the textbox to Short Time and use an input mask of 00:00. User will have to type all 4 digits to avoid typing the colon.

Or

Input can be to a textbox that is not bound to date/time field and not formatted as time then code can convert to the time structure required for date/time field.

[Rocco57]

Thanks June7 for the reply!

I think I'll try the input mask approach to the form/table, I mean, all that I need is a simple math to total hours at the bottom of the input form per employee and a company total in a report.

I had seen on another forum where there was a big discussion evolve from a similar question on how to enter mil time, i.e. 725, or 0725, or 07:25, or 07.25. It was a couple years old and they never really answered the question posed by the OP. Another forum had commented that, that was how they had coded in the military and since they had left they never used it again.

The spreadsheet worked extremely well with the format and the math, but employs quite involved formulas just to convert the 3 or 4 digits into hour based logic to roll over to the next hour for any minute field over 60 and a bunch of hidden fields to insure the math is carried out.

Access was just where everything ended up anyway.

[June7]

Still need to manipulate the time value to convert hours/minutes to decimal hours in order to add.

Data entry to a date/time field won't allow minutes over 60.

Might find this interesting https://www.fmsinc.com/MicrosoftAcce...ker/index.html

The alternative is not to use date/time type, just number, and require user to enter the elapsed time as decimal hours. 7:15 would be 7.25.

[Rocco57]

Thanks June.

I went and looked at the link. Not sure where you were going with that, but I tend to be dense from time to time.

What I had in mind was time start and time stop to be the only time values entered by the employee and a calculated field for the elapsed time. This may be on one job or many and each job may have several tasks.

I'm only interested in one shift, from 700hrs to 1100hrs and from 1130hrs to 1530hrs. The input mask works but is a pain on values from 700hrs to 959hrs due to the need for the leading zero, i.e. 0700.

How do you suggest the summing of all hours spent on a particular job or task, or hours spent per employee, or just total hours for a certain period for my query and eventual report?

I am familiar with decimal hours from other applications, but not sure how to use it in my application. Do I simply add a calculated field to my table and sum off of that field? I can see even hours reported as not a problem, but I see any fraction as being converted back to minutes in an additional field and then concatenate those into a readable value for the report.

Am I all wet and completely confused or what?

I am strongly considering leaving my spreadsheets in place, but I know I will be grilled at some point in the not so distant future as to why this isn't a working function automated in our SQL.

[June7]

The link seems to offer a time picker control.

As I said, have to type all 4 digits to avoid typing the colon.

I think I have outlined the options available for data entry of time. Up to you to pick one.

If you save start and end times then will have to do calc for the elapsed time and then sum that calculated field.

If you want the summed elapsed time presented as hours/minutes, will need calc to convert from decimal hours.