Microsoft Office 2007 Access database query

Up Donegal

I recently got a new computer and I transferred a Microsoft Office Access 2007 database to it from my old computer. The database contains tables and each table has a date field with dates either from 1800 to 1899, from 1900 to 1999 or from 2000 to the present.
When I designed the database on my old computer, I formatted each date field as dd mmm 18yy for dates between 1800 and 1899, dd mmm 19yy for dates between 1900 and 1999 and dd mmm 20yy for dates from 2000 to the present, which meant that I only had to enter the day of the month, the number of the month and the last two digits of the year, e.g. by entering 10 03 17, this would appear on the database table as 10 Mar 2017.
However, when I went to consult the database on my new computer, the years in date fields are shown in the table as, 181800 - 181899 for 1800 to 1899; 191900 - 191999 for 1900 to 1999 and 201900 - 201918 for 2000 to 2018. But when I enter new record, the date shows up in the date field as I want it to on the table i.e. 10 03 17 shows up as 10 Mar 2017.
I have noticed that in design view, the original formatting, which I set up has changed. Originally I had formatted it as dd mmm 18yy; dd mmm 19yy and dd mm 20yy. It now shows up as dd mmm” 18”yy; dd mmm” 19”yy and dd mmm” 20”yy. I have tried changing the formatting of the database in design view but to no avail.
Any ideas as to what caused this and how I can rectify it?
Thanks.

KAGY

Access stores dates as number of days since 1/1/1900. Your formatting wasn't storing the actual date, just showingg it differently. Your new PC is adding the 20 to the 4 digit year.

If you don't mind that the date is actually wrong (days of weeks won't match up, calculations may be wrong etc) I thick that you can get your computer to show 2 figure years in the language setting in the control panel

KAGY

Opps my mistake, I was thinking of excel, access should handle dates from 100ad, but you'll need to enter 4 digit years. You may need an update query to subtract approx 36525 days (1 century) or double that from affected dates

Display tip should still work

You could also split the date into three fields

beauf

I think I would go back to the original database correct the date format then import it properly as a datetime.

None standard date formats are just wrong. If you want to display it differently or search for it differently you do that on the front end. The data itself should be stored in the correct format though