Access upload date format

[aespinoza]

Hi All

The problem i have is.
i have created an excel file with a userform that is a questionnaire.
with in this questionnaire are several section where a date is required.
once the userform is submitted a worksheet has cells filled in with the userform values. no problems here
the problem arises when I try to upload this sheet to an access database. it actually all works apart form at the beginning of the month. the dates are entered as DD/MM/YYYY but this somehow defaults into the Database as MM/DD/YYYY

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim x As Long
Dim y As Long
Set cn = New ADODB.Connection


cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & "Y:\Contact Centre\03 - Team Leaders & Support\01 - Contact Centre MI\MI Suite\Do Not Use\MI Database Quality.accdb;"




'select DB table to add records to
Set rs = New ADODB.Recordset
rs.Open "Compliance", cn, adOpenKeyset, adLockOptimistic, adCmdTable


y = 2
Do Until y = 6
'add new record
rs.AddNew


rs.Fields("Survey_ID") = Cells(y, 1).Value
rs.Fields("Q1") = Cells(y, 2).Value
rs.Fields("Q2") = Cells(y, 3).Value
rs.Fields("Q3") = Cells(y, 4).Value
rs.Fields("Q4") = Cells(y, 5).Value
rs.Fields("Date_of_ Interaction") = Cells(y, 6).Value
rs.Fields("Time_of_Interaction") = Cells(y, 7).Value
rs.Fields("Department") = Cells(y, 8).Value
rs.Fields("Workstream") = Cells(y, 9).Value
rs.Fields("Call") = y - 1

the date_of_interaction field is the problem
I have tried the following

rs.Fields("Date_of_ Interaction") = Format(Cells(y, 6).Value, "dd/mm/yyyy")

any help would be greatly appreciated

Thanks

 

[ranman256]

if Access is set to US dates, this can fail with invalid dates.
convert the cell to US date or convert to long integer. (all dates are numeric)
once saved, you can always view the dates in any format
"dd/mm/yyyy"

 

[Norie]

Try using the format yyyy-mm-dd, it's kind of an international standard thing.