Sorting on text dates

[victorab]

How can I force Access to interpret the date (e.g.) 12/01/29 to mean 12/01/1929, instead of 12/01/2029? And how can I sort correctly on a text date with the two digit year format?

 

[Pat Hartman]

1. Control Panel/ Regional Settings/Customize Button/Date tab - If you lower the end date, windows automatically lowers the start date. The initial setting is to interpret any two digit date less than 30 as being in the 21st century.

Since you would have to do this for all computers that use your app, I would suggest instead, expanding the date field to show 4 digits. The user can still enter 2 digits but he'll see the century assumption. You could also put code in the AfterUpdate event that tests the year and prompts the user when a year that could be a problem was entered.

2. You can't if the dates span centuries. And when they don't span centuries you need to reorder the fields into yymmdd order to get them to sort properly. Why are you using text dates anyway? All your dates should be defined as date/time data types to avoid problems like this.

 

[victorab]

Thanks for the tips, Pat. Reason I am using a text field for the dates in my application is that the application imports records from another system which uses dates in mm/dd/yy format. The users are so used to using this format that they expect to type only yy as the year when they use the date field (birth date) to do a search. If I use a date field, Access automatically converts the date 12/01/29 to 12/29/2029, instead of 12/29/1929, when it imports the records.

 

[Pat Hartman]

You can run an update query after you import the data to add 100 years to any birth date > some reasonable date. If you're dealing with adults, you shouldn't have any trouble. Just check for dates less than 1/1/2000.

Believe me, life will be much simplier for you if you use the date/time data type for dates.