How to properly save Date into Microsoft Access?

Asked Jan 18 '21 at 13:10

Active Jan 18 '21 at 13:10

Viewed 31 times

I am trying to populate my Access Table that has a date column from my excel, so in my excel I have DateOfBirth formatted as dd/mm/yyyy. In my Visual Basic Code this line print my date properly: MsgBox "DOB: " & Format(xlWs.Cells(i, 9), "dd/mm/yyyy"). My problem now is using this code:

sql = "INSERT INTO Persons(PersonName, Dob) VALUES('" & xlWs.Cells(i, 8) & "'," & Format(xlWs.Cells(i, 9), "dd/mm/yyyy") & ");"
DoCmd.SetWarnings False
DoCmd.RunSQL (sql)
DoCmd.SetWarnings True

where xlWs.Cells(i, 9) is the date from my excel column and dob column in my table is of type Date/Time and Format as Short Date. My problem now is all my rows in dob column in Persons table becomes: 30/12/1899. How to fix this?

asked Jan 18 '21 at 13:10

Alexander Paudak

Try formatting it as `"yyyy-mm-dd"` – Super Symmetry Jan 18 '21 at 13:38
check [this](https://stackoverflow.com/a/771902/1521579) – Ricardo Diaz Jan 18 '21 at 13:43
1

Use: `VALUES('" & xlWs.Cells(i, 8) & "',#" & Format(xlWs.Cells(i, 9), "yyyy\/mm\/dd") & "#);`. – Gustav Jan 18 '21 at 14:29

How to properly save Date into Microsoft Access?

0 Answers0