How do I import dates from Excel to Access?

[Chris Brown]

I can't seem to get dates imported from an Excel file to store and write to a new table properly.

Here is an excerpt from my code. The date imports correctly to my tblAll but then seems to convert the values in the tblResults. Any assistance would be greatly appreciated.

Dim bTickDate As Date

Dim rs As ADOR.Recordset
Set rs = New ADOR.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.CursorType = adOpenStatic

DoCmd.RunSQL "CREATE TABLE tblResults  (ID Long,  bTickDate Date)"

rs.Open "Select * from tblAll"

‘ read a date from the table
bTickDate = rs!TickDate

   'write record to tblResults  
DoCmd.RunSQL  "INSERT INTO tblResults  (ID, bTickDate)  VALUES  (" & ID & ", " & bTickDate & ")"

tblAll

TickDate
1/2/2001
1/3/2001
1/4/2001
1/5/2001

tblResults

bTickDate
12/30/1899
12/30/1899
12/30/1899
12/30/1899

Why are they coming up 1899?

Thanks for any help.
cb

[TheSmileyCoder]

Access stores dates as a double. The integer part of the number represents the number of days passed since 30th december 1899. The decimal part represents the fraction of the day passed.

So whenever your seeing the date 30th december 1899, it is likely that you have passed a very small double.

Now a good debugging practise whenever your having trouble and that trouble involves SQL and parsed strings is to print the string to the screen.

Dim strSQL as string
strSQL="INSERT INTO tblResults  (ID, bTickDate)  VALUES  (" & ID & ", " & bTickDate & ")"
msgBox strSQL
DoCmd.RunSQL  strSQL

Now if you had done that your msgbox would have told you that your string looks like:

INSERT INTO tblResults  (ID, bTickDate)  VALUES  (,1/2/2001)

Since you have not assigned ID any value (ALWAYS USE OPTION EXPLICIT AT THE TOP OF YOUR MODULE!!! to ensure that all variables get properly declared!)

Secondly, instead of a date, your doing math, 1 divided by 2 divided by 2001. If you when to pass a string literal you must tell access so, in the same way that you use quotes around strings. The delimiter for dates is #.

[MikeTheBike]

Hi

You could try this and see what happens

strSQL="INSERT INTO tblResults (ID, bTickDate) VALUES (" & ID & ", #" & Format(bTickDat e, "mm/dd/yy") & "#)"

??

MTB

[Chris Brown]

Thanks very much. Tried all of your suggestions and they worked.

[Chris Brown]

Thanks Mike. I am starting to get the hang of escaping these strings and also of the debug tools.