Dates getting 'inverted' when imported from Excel

[Alc]

I'm importing an Excel spreadsheet into an Access db.
For most records, the import works fine, but in a few cases dates are being 'inverted'. For example, if I import 01/02/07 (February 7th, 2007) it becomes January 2nd, 2007 in my Access table. This doesn't hapen to the majority of records, but even one is too many.

I've confirmed that the Excel date genuinely is Feb 7th on the spreadsheet.
I've used a message box to check that the value being inserted is #01/02/07#.
Somehow, the day and month combination still gets interpreted differently by Access.

Is there some property I can set within Access to ensure that all dates are stored in the same format they are in Excel?

Any thoughts gratefully received.

 

[JohnLee]

Hi,

Looking at your information, 01/02/07 would translate as the January, 2nd, 2007 and not the February, 7th, 2007, assuming your using the American date format, otherwise it would translate as the 1st of February 2007 [01/02/07] using the UK date format.

Your characters read as zero [0], one [1] slash [/] zero [0], two [2] slash [/]zero [0], seven [7]

There may something in that particular cell of the spreadsheet that is causing the data to be read incorrectly, assuming that the data is all coming from the same spreadsheet.

John

 

[Alc]

Thanks for the reply. I think you may well be onto something, there.

The data is stored in the spreadsheet cell as 01/02/2007. The display format says 1-Feb-07.

When the data is imported, it gets stored in the Access table as 02/01/2007.

Do you know if i can specifiy which country's date format is to be used, using VBA?

 

[Dennisk]

Dates are not stored as '02/01/2007. they are stored as floating point numbers. Is the firld receiving the data a date field or a text field.

Try exporting the excel data to a CSV then you can examine it using notepad.

 

[Alc]

Dates are not stored as '02/01/2007. they are stored as floating point numbers.

Sorry, I just meant that when you click on the spreadsheet cell, the formula bar displays 02/01/2007.

Is the firld receiving the data a date field or a text field.

A date field.

Try exporting the excel data to a CSV then you can examine it using notepad.

What exactly would I be looking for?

 

[Brianwarnock]

Are all dates with a day less than 13 being "inverted"?

Brian

 

[KeithG]

If you cant find a solution you could always run an update query with the below expression replacing date with your field name.

Day([date]) & "/" & Month([date]) & "/" & Year([date])

 

[Alc]

Are all dates with a day less than 13 being "inverted"?

Brian

Yep, which makes me
a) Think that JohnLee definitely had a point
and
b) feel extremely dull for having wasted hours looking for a problem with my code

If you cant find a solution you could always run an update query with the below expression replacing date with your field name.

Day([date]) & "/" & Month([date]) & "/" & Year([date])

I think this may be my best bet.

Thanks to all for the help and suggestions.

 

[KeithG]

I would only use this a temp solution until you can find out the real problem. Are you sure your access column and excel column are both date data types?

 

[Alc]

I would only use this a temp solution until you can find out the real problem. Are you sure your access column and excel column are both date data types?

Yep.
The Excel column is in the format DD-MMM-YY.
The Access field is of type Date/Time, and in the format Short Date.

I just tried your formula but it didn't help. A value which is set to October 5th in the spreadsheet becomes May 10th after import.

Is there something glaringly wrong with the following:

Opened = IIf(IsEmpty(WorkS.Cells(I, 17)), #1/1/1900#, WorkS.Cells(I, 17))
Opened = Day([Opened]) & "/" & Month([Opened]) & "/" & Year([Opened])

 

[KeithG]

Did you put that in the spreadsheet or update query?

 

[Alc]

Did you put that in the spreadsheet or update query?

I included it in the import code.

Originally, the code just contained

Opened = IIf(IsEmpty(WorkS.Cells(I, 17)), #1/1/1900#, WorkS.Cells(I, 17))

I then added the second line.

 

[Brianwarnock]

Won't this
Opened = Day([Opened]) & "/" & Month([Opened]) & "/" & Year([Opened])
produce a TEXT date, wouldn't it be better to use Dateserial?

Brian

 

[Alc]

Won't this
Opened = Day([Opened]) & "/" & Month([Opened]) & "/" & Year([Opened])
produce a TEXT date, wouldn't it be better to use Dateserial?

Brian

Wasn't familiar with Dateserial.
Having just looked it up, how would I use it to get the date into the format I need?

 

[Brianwarnock]

Dateserial provides a date in your default format.
I'm in the uk and have no trouble with microsofts standard import of Excel to Access, nor with using DateSerial in Access, but I have never used my own code. I just felt that having a "text" date was perhaps giving you problems.

Brian

 

[JohnLee]

Hi,

Could possible post a sample of your spreadsheet that includes the offending data. I would then have a look to see if I can discover what is causing the problem.

John

 

[Alc]

Dateserial provides a date in your default format.
I'm in the uk and have no trouble with microsofts standard import of Excel to Access, nor with using DateSerial in Access, but I have never used my own code. I just felt that having a "text" date was perhaps giving you problems.

Brian

Thanks, but the problems were there before DateSerial got mentioned.

 

[Brianwarnock]

Thanks, but the problems were there before DateSerial got mentioned.

I know, Dateserial was suggested as a possible solution if the Text Date format was causing the problem.

Brian

 

[Alc]

Hi,

Could possible post a sample of your spreadsheet that includes the offending data. I would then have a look to see if I can discover what is causing the problem.

John

Thanks. I've attached the file with all 'confidential' stuff removed. the problematic column is now G ('Date').

 

[Brianwarnock]

I'll have to sign off as the Zip cannot be handled by my system, I guess you have a later version.

Brian