Link to home
Start Free TrialLog in
Avatar of Rob4077
Rob4077Flag for Australia

asked on

Transfer database Date format confusion

I need to transfer a number of tables from one database to another and found what I thought would do the trick by using a Docmd.transferdatabase method to create a multi tabbed spreadsheet with each sheet being one table. Then I ise the same method to import the spreadsheets into the new database. Problem is that when I run the import and it comes across a date in the system date format of dd/mm/yyyy it tries to import it as mm/dd/yyyy and so fails to import. Does anyone know a fix?
Avatar of Rob4077
Rob4077
Flag of Australia image

ASKER

Correction to the question. Some dates are coming in correctly, like 31/08/2008 but others are not, like 31/7/2008. I have tried changing the format of the spreadsheet fields to General to ensure the data is not corrupt but it isn't. Looks like I will have to write a VBS routine to do the upgrade unless someone has a suggestion that will help
Avatar of Rob4077
Rob4077
Flag of Australia image

ASKER

Actually it is weird. It looks like the first column of dates has come in correctly but every other column that has a date in it has not worked at all so it is not the date format but another problem.
ASKER CERTIFIED SOLUTION
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Natchiket
Natchiket
Flag of United Kingdom of Great Britain and Northern Ireland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Rob4077
Rob4077
Flag of Australia image

ASKER

Hi and thanks for your comments. I think I have figured out why it's not working. I now remember I had a similar problem a couple of years ago with another application. If the first value in a date field in a spreadsheet is blank it won't import properly into a date/time field in a table from what I recall. I will try your suggestions to resolve this one. Thanks.