Sql Insert -- date format issue
Below is the second part of a command I am trying to run from an access form. The first part runs a stored procedure which insert the record into the sql backend. However I also want to run an insert command which inserts the record onto a local table held locally on the user's pc. Making it available to query whilst offline.
My problem is that it inserts the record successfully however it changes the date field's format in the process. dd-mm-yyyy becomes mm-dd-yyyy on the local mdb version.
To query the visits from the local mdb table I have created a form with two activex calender controls representing date from and date to. They display correctly in their control, however because the dateOfvisit field is in the wrong format no records are displayed.
' Create a local connection to insert a new record into Local Mdb
CnnStr = "Provider=Microsoft.jet.oledb.4.0;"
CnnStr = CnnStr + "Data Source= " + strMdbPath
strOfficer = cboOfficer.Column(1)
strSchoolName = cboSchool.Column(1)
strActivity = cboActivity.Column(1)
strReason = cboReason.Column(1)
strFunding = cboFunding.Column(1)
StrMethod = cboMethod.Column(1)
StrTime = cboHours.Column(1) & " Hours " & cboMinutes.Column(1) & " Minutes"
' Convert Date to a string datatype, to run SQL script, "#" converts back to SmallDateTime
' Format when insert into SQL DataBase
StrDate = StrConv(txtDate.Value, vbLowerCase)
strSql = "Insert Into tblOfficerVisits(OfficerName,SchoolName,Activity,R eason,Funding,Method,DateOfVisit,Outcomes,TimeSpen t) Values"
strSql = strSql + "("
strSql = strSql + Chr(34) + strOfficer + Chr(34) + "," + Chr(34) + strSchoolName + Chr(34) + ","
strSql = strSql + Chr(34) + strActivity + Chr(34) + "," + Chr(34) + strReason + Chr(34) + ","
strSql = strSql + Chr(34) + strFunding + Chr(34) + "," + Chr(34) + StrMethod + Chr(34) + ","
strSql = strSql + "#" + StrDate + "#," + Chr(34) + strOutcomes + Chr(34) + "," + Chr(34) + StrTime + Chr(34) + ")"
Set cmd = New ADODB.Command
cmd.ActiveConnection = CnnStr
cmd.CommandType = adCmdText
cmd.CommandText = strSql