Dates getting 'inverted' when imported from Excel

[Alc]

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

Brian

Ah good, I was just starting to think I didn't have enough problems
Thanks anyway.

 

[Alc]

After months away from this one, I'm back to it.

To summarise:
1) A cell in an Excel spreadsheet says '10/06/2009'.
2) I'm importing this cell (among others) into a table in an Access db. The field it's going into is of type Date/Time, format Long Date.
3) To set the variable to be inserted I'm using
[/code]
Dim ld_Todays_Date as Date

ld_Todays_Date = WorkS.Cells(I, 2)
[/code]
followed by the insert statement, which works.

The problem is that the above date is being 'inverted' i.e. it gets stored as October 6th, 2009. It looks very much as if this happens - as suggested in an earlier post - whenever the day is less than 13.

I don't have any control over the spreadsheet being sent to me and those who do would not be willing to make any changes to it.

What can I do, within Access, to ensure that if the Excel data is in the sequence Month Day Year, it doesn't get switched to Day Month Year during import.

 

[Alc]

Okay, in case it's of any use to anyone else in the same boat, the following is working, but it ain't neat. It's based on a few of the earlier posters' ideas, combined with trail and error.

I replaced

ld_Todays_Date = WorkS.Cells(I, 2)

with

str_Day = Left(Trim(WorkS.Cells(I, 2)), 2)
li_Day = Val(str_Day)
str_Month = Right(Left(Trim(WorkS.Cells(I, 2)), 5), 2)
str_Year = Right(Trim(WorkS.Cells(I, 2)), 4)
If li_Day < 13 Then
      ld_Todays_Date = CDate(str_Month & "/" & str_Day & "/" & str_Year)
Else
      ld_Todays_Date = CDate(str_Day & "/" & str_Month & "/" & str_Year)
End If

 

[PeterF]

I agree with Brian, dateserial will prevent these kind of errors.
In VBA all dates are in US format and you need to format it that way to prevent these kind of results.

 

[Alc]

I agree with Brian, dateserial will prevent these kind of errors.
In VBA all dates are in US format and you need to format it that way to prevent these kind of results.

I'll have another look at DateSerial, but I've found that a good workaround is to change the PC's settings to be US as opposed to Canadian for dates. This application will only be running on one PC, so it won't affact anything else. That should so the trick, until I can get DateSerial working.

 

[Alc]

Okay, stupid question of the day:

If DateSerial requires that I pass in the Year, Month and Day values, but I don't know what these are, how do I use it here?

I tried using Year(), Month() and Day() to find the values, but that still results in the Day and Month being inverted, unless the PC settings are US.

I can't Use Left() or Right() to isolate the year, etc. as I don't know where each part falls in the cell.

 

[gemma-the-husky]

i think you ought to check your regional settings.

i just prepared a spreadsheet with two dates

13/7/09
12/7/09

now 13/7/09 will always import as 13th July 2009

12/7/09 should import as 12th July 2009 in UK, but may import as 7th December 2009 (US)


both dates imported as UK dates with no problem.

-------------
I would check the spreadsheet to make sure that every value in the suspect column IS a valid UK date. (if there are blanks, put some value in there!) - if this fails this may account for your problem.

there are two workrounds I can see

a) in row 2 of the spreadsheet, in the data column put any "text word" - this will force the column to come in as text - then you can use the date conversion routines advised above

b) save the spreadsheet as a csv - then import the csv - you actually get more control over a csv import, and you MAY be able to fix the date error that way.

 

[Alc]

i think you ought to check your regional settings.

i just prepared a spreadsheet with two dates

13/7/09
12/7/09

now 13/7/09 will always import as 13th July 2009

12/7/09 should import as 12th July 2009 in UK, but may import as 7th December 2009 (US)


both dates imported as UK dates with no problem.

Thanks for the reply.

That's exactly what I'm finding. If a date can be switched, it will be.
If I change my PC settings to be US, the import runs as expected

The problem with changing the region setting - as I've since discovered - is that the PC on which this will be running is 'locked' at Canadian, while Access uses US.

I would check the spreadsheet to make sure that every value in the suspect column IS a valid UK date. (if there are blanks, put some value in there!) - if this fails this may account for your problem.

It's only a test dataset of ten rows. All dates are definitely valid.

there are two workrounds I can see

a) in row 2 of the spreadsheet, in the data column put any "text word" - this will force the column to come in as text - then you can use the date conversion routines advised above

b) save the spreadsheet as a csv - then import the csv - you actually get more control over a csv import, and you MAY be able to fix the date error that way.

Thanks for the suggestions, i'll have a play with it and see what happens.

EDIT:
No idea why, but after following the suggestion of saving the file as a CSV, I then changed the code from the original

            ld_Todays_Date = Trim(WorkS.Cells(I, 2))

to

            str_Day = Day(Trim(WorkS.Cells(I, 2)))
            str_Month = Month(Trim(WorkS.Cells(I, 2)))
            str_Year = Year(Trim(WorkS.Cells(I, 2)))
            ld_Todays_Date = str_Month & "/" & str_Day & "/" & str_Year

and the import worked.

Whatever the reason, thanks very much for the help.

 

[gemma-the-husky]

were you importing the data by reading the spreadsheet cells directly?

that is different to importing the spreadsheet, i would guess.

 

[Alc]

I am, yes.

While the date field just gets brought in, there are a few other fields on which checks need to be run as the data comes in. Importing the sheet en masse isn't an option, unfortunately.