UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Vba Insert Into & Date Field Problem    
 
   
Edward_R223
post Jun 10 2011, 11:45 PM
Post #1

New Member
Posts: 1



Need to insert the current date into a field named MyDate within the Shipping table which is a short date type, can't seem to get the quotes and # signs in the right places. I've also tried specifying MyDate As String and tried using Format(MyDate, "#mm/dd/yyyy#")...

Here is the existing working code without the new field.

CurrentDb.Execute "INSERT INTO [Shipping] (ClientID, TrackingNum, FullName, UserID, Address, City, State, Zip, ServiceType, RefField, RetTrackNum, RetServiceType) VALUES ('" & rs![ClientID] & "', '" & TrackingNum & "', '" & rs![CallerLastName] & "', '" & Me.CurrentUser & "', '" & rs![CallerAddress] & "', '" & rs![CallerCity] & "', '" & rs![CallerStateOrProvince] & "', '" & rs![CallerPostalCode] & "', '" & rs![ServiceType] & "', '" & "Alloderm-" & rs![ClientID] & "', '" & RetTrackNum & "', '" & rs![ServiceType] & "')"
Go to the top of the page
 
+
tina t
post Jun 11 2011, 01:54 AM
Post #2

UtterAccess Ruler
Posts: 2,928
From: SoCal, USA



try

CurrentDb.Execute "INSERT INTO [Shipping] (ClientID, TrackingNum, FullName, UserID, Address, City, State, Zip, ServiceType, RefField, RetTrackNum, RetServiceType, MyDate) VALUES ('" & rs![ClientID] & "', '" & TrackingNum & "', '" & rs![CallerLastName] & "', '" & Me.CurrentUser & "', '" & rs![CallerAddress] & "', '" & rs![CallerCity] & "', '" & rs![CallerStateOrProvince] & "', '" & rs![CallerPostalCode] & "', '" & rs![ServiceType] & "', '" & "Alloderm-" & rs![ClientID] & "', '" & RetTrackNum & "', '" & rs![ServiceType] & "', #" & Date & "#)"

hth
tina
Go to the top of the page
 
+
Gustav
post Jun 11 2011, 02:46 AM
Post #3

UtterAccess VIP
Posts: 2,163



This should work and doesn't require formatting of a date expression:

CurrentDb.Execute "INSERT INTO [Shipping] (ClientID, TrackingNum, FullName, UserID, Address, City, State, Zip, ServiceType, RefField, RetTrackNum, RetServiceType, MyDate) VALUES ('" & rs![ClientID] & "', '" & TrackingNum & "', '" & rs![CallerLastName] & "', '" & Me.CurrentUser & "', '" & rs![CallerAddress] & "', '" & rs![CallerCity] & "', '" & rs![CallerStateOrProvince] & "', '" & rs![CallerPostalCode] & "', '" & rs![ServiceType] & "', '" & "Alloderm-" & rs![ClientID] & "', '" & RetTrackNum & "', '" & rs![ServiceType] & "', Date())"

You should use a better field name than "MyDate".

/gustav
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 29th July 2014 - 11:46 AM