Wrox Programmer Forums
| 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 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
  #1 (permalink)  
Old May 11th, 2006, 04:10 AM
Registered User
 
Join Date: May 2006
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default DoCmd.RunSQL dates and time

ive been working on an database and i want to add a record so i am using

DoCmd.RunSQL (INSERT INTO Booking " & "(MemberID, CourtID) " & " VALUES (11, 1);)

which works

but when i add the next fields the date and time and i get a 3134 error

DoCmd.RunSQL ("INSERT INTO Booking " & "(MemberID, CourtID, Date, Time) " & " VALUES (11, 1, #25/06/06#, 10:00);")

ive changing the # and ' doing various combinations of the two.

ive searched the internet and this forum for about 2 hours but i cant get it to work.

any help

thanks

  #2 (permalink)  
Old May 11th, 2006, 06:20 AM
Registered User
 
Join Date: Apr 2006
Location: Scarborough, Ontario, Canada.
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

If I understand your post, you have named two fields, one "Date" and one "Time". These are 'keywords'. Try changing the field names in the table "Booking" first; example: "courtDATE" and "courtTIME". Define "courtTIME" data type TEXT. Then modify your insert like this:

dim sSQL as string
sSQL="INSERT INTO Booking (MemberID,CourtID,CourtDATE,CourtTIME) "
sSQL=sSQL & "VALUES (11,1,#25/06/06#,'10:00');"
DoCmd.RunSQL sSQL

If you want courtTIME as data type Date/Time, then wrap time in '#':

dim sSQL as string
sSQL="INSERT INTO Booking (MemberID,CourtID,CourtDATE,CourtTIME) "
sSQL=sSQL & "VALUES (11,1,#25/06/06#,#10:00#);"
DoCmd.RunSQL sSQL

This will work fine.
:)


  #3 (permalink)  
Old May 11th, 2006, 06:46 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

That would be the problem. I would further add, try combining the date and time fields into CourtDateTime and save a column.



mmcdonal
  #4 (permalink)  
Old May 11th, 2006, 11:19 AM
Registered User
 
Join Date: May 2006
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

wow that worked thank you very much

you guys are good.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Using DoCmd.RunSQL to return values jscully Access VBA 10 August 21st, 2017 12:58 PM
Docmd.runsql anne.burrows VB How-To 2 October 25th, 2006 08:21 AM
Question about DoCmd.RUNSQL using an insert statem Nasem VB Databases Basics 1 May 5th, 2006 10:15 AM
docmd.runsql "select RodMead Access VBA 2 July 31st, 2004 02:55 PM
Help with DoCmd.RunSQL command ricmar Access VBA 3 July 21st, 2004 03:32 PM





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