Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old June 11th, 2007, 11:18 AM
Authorized User
Join Date: Jan 2007
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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.

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


Old June 11th, 2007, 01:03 PM
Friend of Wrox
Join Date: Jun 2003
Posts: 126
Thanks: 5
Thanked 0 Times in 0 Posts

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?


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!
Old June 12th, 2007, 12:11 PM
Authorized User
Join Date: Mar 2007
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts

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:-
StrDate = StrConv(txtDate.Value, vbLowerCase)
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...

Similar Threads
Thread Thread Starter Forum Replies Last Post
INSERT DATE INTO SQL SERVER prasanta2expert ASP.NET 1.0 and 1.1 Basics 1 January 4th, 2007 08:27 AM
ASP insert into sql date problem markd Classic ASP Databases 1 February 18th, 2006 10:43 AM
SQL DATE-Format Problem Fritz Oracle 1 August 26th, 2005 07:14 AM
Date format in ASP and in SQL 2000 Database phungleon Classic ASP Databases 1 December 23rd, 2004 12:57 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.