MSAccess Change Date Value Before Validation

Question

In a grid I have a date/time field where I would like the user to be able to type in short-hand formats for dates and times. For example, if they type in "435p" and then focus off of the cell they get the message "The value you entered isn't valid for this field.". Instead, I want to trap a pre-validationevent and change it to "4:35pm" for them.

What event can I use?

I've tried:

LostFocus & BeforeUpdate: too late (validation fires before event)
Dirty & OnChange: too early (they haven't left the cell yet)

Or is there a way to turn off the native validation rule that is checking for date formats?

I'm thinking you're going to have to get fancy and have an unbound text box that you can validate, do the conversion and then update the actual date/time field. — Jeremy Cook
@Horaciux supposing I didn't do anything wrong... I tried before update and the field or record level validation fires and prevents before update from firing when the input is completely invalid, as in this case. — Jeremy Cook
@JeremyCook - the problem there is that I need to see that field in the grid. If it's unbound do you know how'd I'd wire it up for viewing & sorting? — sisdog
I think it's going to be a bit of a mess to do what you want. You might be better off providing a friendly validation message with some examples of valid times...Access is quite flexible with what it will accept. If you don't want to do that you can setup a continuous form and display the bound date/time field most of the time, except for when a user clicks into it, in which case the unbound text field will appear and get focus. The user types into that and your code behind handles validating and updating the actual date/time field. — Jeremy Cook

Johanness Johanness · Accepted Answer · 2014-06-13T09:20:57

You could use an additional text field without formatting (or with your very own format). Then show this instead of the datetime-field and update the date-time field with your code. Not very pretty, but if you always format the input to a proper time string on before-update and never access this field (but rather the real date-time field) you should be ok. You could even name the field Helper_DateTime or somesuch, so you are never tempted to access the field from anywhere else ;)

MSAccess Change Date Value Before Validation

1 Answers