Domocha’s Weblog

Date Picker fail to update unbound custom formatted text box value

Environment: MS Access 2007 – VBA

Finally in MS Access 2007, you won’t waste your time to build your own calendar to create a date picker. It’s already there, you just need to set several properties of a text box to create a date picker text field.

But, I’ve found a problem while using a custom date format..

Case:

• A field with Date/Time data type in a table that is assigned to an unbound text box in a form.
• Set the [Show Date Picker] Property into [For dates]
• Set custom [Format] Property, e.g: “dd-mmm-yyyy hh:nn AM/PM” instead of [General Date], [Long Date],…
• You’ll find out that changing the value by selecting date picker is FAIL. The only way to change it, is by remove the existing date in that text box, it’s a weird behavior.

Solution:

• Set [On Enter] event of that text box to change the Format into Access known date format, e.g. “dd/mm/yyyy hh:nn:ss AM/PM”, “dd/mm/yyyy”, etc.
• Set [On Exit] event of that text box to change it back into your preferred Format which is e.g. “dd-mmm-yyyy hh:nn AM/PM”

This problem only happened if you set custom format for that text box. This might be because the Date Picker will only return value if the text box’s Format is an Access “known” date format..

Leave a Comment so far
Leave a comment