There didn't seem to be any posts on the internet for a solution to this problem.
Background
I've written a vba macro that takes data from Excel, processes it into an XML file & then imports into Access.
All fields were fine apart from the date field, which appeared in the Access table as "00:00:00"
The date in the XML was appearing as 01/01/2014.
Solution
The solution is to reformat the date as follows:
This produces 2014-01-01T00:00:00
Note, putting the date into reverse order without the formatting also causes the import to result in 00:00:00
Anyway, hope this saves someone the 30+ mins it took me to play about with it
Best wishes
Ediun
Background
I've written a vba macro that takes data from Excel, processes it into an XML file & then imports into Access.
All fields were fine apart from the date field, which appeared in the Access table as "00:00:00"
The date in the XML was appearing as 01/01/2014.
Solution
The solution is to reformat the date as follows:
Code:
dateTemp = [B][date here][/B]
xmlFieldElement.Text = Year(dateTemp) & "-" & Format(Month(dateTemp), "00") & "-" & Format(Day(dateTemp), "00") & "T00:00:00"This produces 2014-01-01T00:00:00
Note, putting the date into reverse order without the formatting also causes the import to result in 00:00:00
Anyway, hope this saves someone the 30+ mins it took me to play about with it
Best wishes
Ediun
