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.

Any ideas???

' 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

cmd.Execute