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

Welcome to UtterAccess! Please ( Login   or   Register )

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



Posts: 1
Joined: 10-June 11



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



Posts: 3,267
Joined: 11-November 10
From: SoCal, USA


try
urrentDb.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
Joined: 21-February 07



This should work and doesn't require formatting of a date expression:
urrentDb.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
 


RSSSearch   Top   Lo-Fi    2nd March 2015 - 09:54 AM