Will not restore a date

[CD Tom]

My application was originally written in Access XP, I switched to Access 2007 and now I'm having a problem. One of my users is in Italy and of course their date is formated as D/M/Y. In my application there is a restore function that restores all data back to the database. For some reason with 2007 Access the date will not restore. I've had the user send me his backup files so I could see what was happening. I switched my computer language to Italian so that the date format would be correct but when I restored the data the date field in the database is left blank. Because of this the program will not work correctly. Does anybody have any idea why this is happening and how to fix it. Thanks for any help.

[NeoPa]

Are the dates stored as DateTime fields?

PS. You should understand that DateTime fields do not store dates as formatted. Storage and format are entirely independent. The backups are another matter, specially if they are not stored there as DateTimes. See Literal DateTimes and Their Delimiters (#) for a related issue in SQL.

[nico5038]

Hmmm, I've solved these problems in the past by setting the Regional Settings (See configuration form) as needed.
Access does look up these settings for default date and number format manipulation.

Nic;o)

[CD Tom]

I use the standard

DoCmd.TransferText acExportDelim, , vtable, apppathx, True

to export the data for backup, I've attached one of the files that should restore the date, it will restore everything but the date field.
Yes the table is formated for Date/Time.

[NeoPa]

If your data is stored in the table as DateTime, but the backed up data is as you've attached, then this certainly involves taking a text string and converting it to a date. Notice, this is not a SQL literal, so the SQL standard is not applied here. The data I saw ("13/06/2010") is certainly formatted as a European style date string, so how it is interpreted (d/m/yyyy rather than m/d/yyyy) is indeed important.

I must admit though, that like Nico, I would expect this to work fine for you if you'd set the default date style properly in your PC (probably necessary to do before Access is first loaded mind you). Of course, that example date wouldn't give a problem anyway, as it would be recognised by the fact that there are fewer than 13 months, but other date data could well be misread unless properly configured before the import or restore.

[nico5038]

OK, found two problems:
1) Your time has no ":" but "." as separator and that will need to be changed. (The 0.00.00 must become 0:00:00 or removed)
2) The Date format in the Regional settings needs to be set to the European format dd mm yyyy.

Having changed that did import the date without errors.

Nic;o)