Form Check Date/Time Format

[JayRab]

I am looking to find a way to check if a Date/Time text box on a form is in the correct format.... IE General Date.



Has Date and Time mm/dd/yyyy hh:mm am/pm.

I am using this for calculations currently they use a date picker or double click to set now(). But sometimes they will delete time and input something wrong or delete the time and just leave a date. Therefore I get a Time Field with Just a date.

I do have another text box that selects the DATE they use with a date picker(they dont ever edit this just select date then move onto TIME textbox), so I have been combining them when they delete everything or just type in a time.

Anyway I would like to put something in the BeforeUpdate event, that checks to make sure the Time textbox is in this format mm/dd/yyyy hh:mm am/pm.?


Already have something in validation rule.

thanks

[June7]

If the date/time entered should always be >= Now(), can check for that. There is also the IsDate() function.

Would midnight ever be a valid input?

[JayRab]

sometimes...but the likely hood of it being exactly midnight is low. I can see from the data i have a few people just selecting a date in the time entered field and that is giving me an error in my calculations showing only a date in the time field of the table

below is a screen shoot. the boxed section is throwing the error, time down is before time delivered in this instance. Give me a negative time down. and >= now() will not work because if they arent at computer when equipment is delivered they come in later and update so it could be hours earlier when it came up before they update the system.
I only require an update before 12:00 am/ pm and 6:00 am/6:00 pm when reports are sent out.

[June7]

Would have to analyze those calcs to determine if issue can be handled there.

You understand date/time is stored in a DateTime type field as a Double number? Decimal part is the time. Midnight is 0 decimal. If midnight is a valid input, can't base validation on lack of time part.

Could try input mask and make sure the calendar picker is not available.

[JayRab]

I have them double click and input current time then just edit the time...but they dont always do that. I have added a check to make sure timedelivered is greater then timedown. just added it but i would like to make sure the time was entered correctly.

good idea about removing date picker... that should force them to use manual entry and double click

[June7]

If they are entering to textbox bound to DateTime field, Access will complain if the entry is not a valid date/time. Error handler code could probably trap that error if you want to do something other than the intrinsic error popup.

[losstww]

You can set the textbox Format to General Date.i.e. 6/19/2014 5:34:23 PM and you can add an Input mask 99/99/0000\ 00:00:00\>LL;0;_ The user has to add the correct information or they will receive an error.

You could create a textbox On Exit event

If Me.YourDateTextBox <> Format(Me.YourDateTextBox, "General Date") Then

Me.YourDateTextBox = Format(now(), "General Date")

End If

The date in the date Textbox will always default to the current system time in the General Date format.

[JayRab]

That is a good idea too. I will look into it tuesday thanks for help.