SQL date and time statements

  • Thread starter Thread starter Panda
  • Start date Start date
P

Panda

Please help me to find a rules for right writing of next SQL statement:
"Update TableName set DateTimeStamp = #12/27/2005 18:42:36#"

The problem is in format of DateTime. How is right statement to be written?
 
Hi,


DoCmd.RunSQL "UPDATE tableName SET fieldName=
FORMS!FormName!ControlName"


is a solution, and put the data and time value in the referred control.


Another solution:

CurrentDb.Execute "UPDATE tableName SET fieldName=" &
Format( FORMS!FormName!ControlName, "\#mm-dd-yyyy
hh:nn:ss\#"), dbFailOnError


would also do, but if there is any error with the update, it will be catch
in VBA.


You should avoid a default conversion from date to string:


"... #" & DateTimeValue & "# ... "


since that would be done in the default locale format, while it is expected
to be in US format, when embedded between a pair of #.



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top