Question Time input (masks / 'translation' (1 Viewer)

[CazB]

I'm struggling with putting together a form to allow the input of times into a database that I'm building... the purpose of the database is to log stage times for competitors on a car rally!
The users need to input a start time and end time for each car on each stage, along with any stage penalties that may be incurred.

The actual mechanics of the form are fine - if the times are entered correctly than it works everything out for us in exactly the way it should - but the problem I have is with simplifying the way that the times are entered... to make sure they are entered correctly in the first place!

Stage Start times will always be either the exact minute or half minute.
eg 09:01:00 or 10:23:30

Stage End times can be any time
eg 09:07:22 or 10:30:00

Stage Penalty times will always be whole minutes - but I can deal with these separately so they're not necessarily part of the issue here

What I want to be able to allow the users to do is just to type in

901 and it understand that as 09:01:00
102330 and it understand that as 10:23:30
1030 and it understand that as 10:30:00

I've played around a lot with input masks but I can't find one that works for all the above situations.... At the moment I'm having to force them to enter all 6 digits of the time and there's a lot of grumbling going on and mistakes being made because of it... should maybe add the input needs to be completed as quickly and accurately as possible in order to publish the stage results as soon as possible after the end of each stage.


Any ideas / suggestions gratefully received! I've pondered making them put the hours, minutes and seconds for each 'time' in 3 separate text boxes and then using a function to convert that to a proper time, but wasn't sure if that would be the best way to go forward... and how I could then validate that...

 

[Frothingslosh]

My suggestion would be this:

Have them enter the data into an unbound text box. Once the users tab out of the box, use Form_AfterUpdate to pass the value of that textbox to a custom function.

The function should do the following:

1. If the data is < 6 characters in length, pad the ending out with enough 0's to reach 6.
2. If the data is > 6 characters, kick back an error message.
3. Check to make sure the data can be turned into actual date information. That means rejecting things like 291000 and 097100.
4. Parse the new string, and copy it into a new variable. This new variable should begin and end with '#', and have the colon added in between hours, minutes, and seconds. Example: "0910" became "091000" became "#09:10:00#".
5. Use the CDate function to turn this value into a date.
6. Format the new result to Short Time, or whatever other (perhaps custom) format you wish to set up.
7. Return the correctly formatted time to the form.

Once the correctly formatted time is received back from the function, THAT gets saved to the underlying table. (If you're saving the time strings as text, then don't worry about the '#' characters or steps 5 and 6.)

You can do this with a bound control box, too, but then you want to set up a routine to make sure that the update only triggers when a user makes a change, not when the code does, and it would only work in a text field, not a date/time field.

 

[CazB]

thanks, will give it a go..

 

[Frothingslosh]

thanks, will give it a go..

If you get stuck on something, just ask.

 

[CazB]

Finally got round to sorting this over the weekend and I'm glad to say it works.... I used a separate unbound text box for each part of the time (with simple 'between' validation on each part) and then used a bit of code to sort it into the correct format and store it
The proof of the pudding will come on Sunday when it goes into proper use for the first time but I couldn't break it yesterday, so here's hoping!

 

[MS$DesignersRCretins]

You might consider the isdate function as a backstop in case you don't catch every input variation. I don't know if you use VBA or if a UDF is off the table but this would work there. More simply, you can use IsDate([MyField]) in a query or validation.

It's not a comprehensive solution, and it would accept something like 5.5 as a date (as it should) though you might wish to reject that value. I'm only suggesting it as a backstop.

http://office.microsoft.com/en-us/access-help/isdate-function-HA001228863.aspx