Changing date format in large database.

[mbenton]

Hello, I've been ask to make all "date" fields consistent in large database (only about 3 or 4 forms with approx. 4 or 5 date fields combined). Currently some entries require "mm/dd/yyyy" which needs to be changed to "mm/dd/yy". Ordinarily I would make the change at the table level but unfortunately for me I have a few ODBC tables that have very strict guidelines set by the company and I cannot make any design changes to the tables. The ODBC tables hold all my date fields that I need to change! All dates are set to "Short Date" in the ODBC tables. My question is two-fold, first is it even possible to override the Short Date format in my ODBC tables so that "mm/yy/dd" will appear on the forms? If so, how? I've tried changing the date format through field properties but it doesn't work, perhaps due to the higher priority table level setting. Can anyone recall a similar experience, or steer me in the right direction to get around this? *It's an established database with much data and I'd rather avoid any drastic solutions if possible. Thanks.

[ranman256]

if the field is set to DATE type, then (aside from mixing month and day) it doesn't matter how the date is entered, be it 2 digit year, or 4
JANUARY or 1 or Jan
You just set the format on how you want to see it.
Granted if users enter the data wrong , some dates will get rejected , but some wont but be wrong.

but if you want: "mm/yy/dd"
then display it in the text box or query grid, using FORMAT([DATE],"mm/yy/dd")

[CJ_London]

I've tried changing the date format through field properties but it doesn't work

'doesn't work' is not very informative - what exactly did you use for the format property and what result did you get? I've never had a problem using the format property for dates.

[ranman256]

in a query, right click on the 'field' column, properties, format
or
type FORMAT([DATE],"mm/yy/dd") directly in the query field
or
set format property in the form text box.

[mbenton]

The Input Mask feature on the Data tab in Properties allow me to create new date format, problem solved...thanks for advice.