By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,566 Members | 1,840 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,566 IT Pros & Developers. It's quick & easy.

Will not restore a date

100+
P: 462
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.
Jul 12 '10 #1
Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 31,769
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.
Jul 12 '10 #2

nico5038
Expert 2.5K+
P: 3,072
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)
Jul 12 '10 #3

100+
P: 462
I use the standard
Expand|Select|Wrap|Line Numbers
  1. 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.
Attached Files
File Type: txt params.txt (1.7 KB, 199 views)
Jul 12 '10 #4

NeoPa
Expert Mod 15k+
P: 31,769
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.
Jul 12 '10 #5

nico5038
Expert 2.5K+
P: 3,072
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)
Jul 12 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.