Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 11th, 2007, 11:18 AM
Authorized User
 
Join Date: Jan 2007
Location: , , United Kingdom.
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

cmd.Execute




Reply With Quote
  #2 (permalink)  
Old June 11th, 2007, 01:03 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Connersville, Indiana, USA.
Posts: 126
Thanks: 5
Thanked 0 Times in 0 Posts
Default

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!
Reply With Quote
  #3 (permalink)  
Old June 12th, 2007, 12:11 PM
Authorized User
 
Join Date: Mar 2007
Location: , , United Kingdom.
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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...

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 09:24 AM.


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