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

Forgive me for sounding so basic, but I have done this myself.
Did you review the format for your date field on both tables (local and network) to make sure you have them formatted identical?

Regards,
Laura

FYI...My user id has changed. My old id was lbreitenbach

The only thing standing between you and your goal is doubt.  Quit doubting yourself and you'll be able to accomplish anything!

I think your problem may be that the date is not storing correctly - it needs to be formatted as mm/dd/yyyy to store in a Jet (Access) database table. If you supply it the other way round, then some dates will be rejected (31/05/07 - not 31st month), while others will be stored but as the reverse date from intended (e.g. 04/05/07 - becomes 5th April).

Just substitute this line:-
Replace
StrDate = StrConv(txtDate.Value, vbLowerCase)
with
StrDate = VBA.Format(StrConv(txtDate.Value, vbLowerCase), "\#mm\/dd\/yyyy\#")

and remove the "#" bits from the strSql statements.

By formatting the date correctly for insertion into the Jet database table, it will be stored properly, and will then retrieve correctly. You just need to format the text fields that display the date so that it appears as you want it, i.e. "dd/mm/yyyy".

Let the forum know how it goes...