SQL Date/Time Update

[DS]

I running this SQL that is giving me a problem.
DateIn and TxtDAdjust are Date fields
TimeIn and TxtTAdjust are Time fields
I know that for date/time fields that you are suppose to use # or '
but I'm not sure where to put them. Any help appreciated.
Thanks
DS


Dim DateInSQL As String
DoCmd.SetWarnings False
DateInSQL = "UPDATE TimeLog SET TimeLog.DateIn =
Forms!AdjustDateTime!TxtDAdjust " & _
"WHERE TimeLog.TimeID=Forms!AdjustDateTime!TxtLog;"
DoCmd.RunSQL (DateInSQL)
DoCmd.SetWarnings True

Dim TimeInSQL As String
DoCmd.SetWarnings False
TimeInSQL = "UPDATE TimeLog SET TimeLog.TimeIn =
Forms!AdjustDateTime!TxtTAdjust " & _
"WHERE TimeLog.TimeID=Forms!AdjustDateTime!TxtLog;"
DoCmd.RunSQL (DateInSQL)
DoCmd.SetWarnings True

 

[Douglas J. Steele]

I'd strongly recommend having a single DateTimeIn field, rather than two
separate fields.

Dim DateInSQL As String
DoCmd.SetWarnings False
DateInSQL = "UPDATE TimeLog " & _
"SET TimeLog.DateTimeIn = " & _
Format(Forms!AdjustDateTime!TxtDAdjust, "\#mm\/dd\/yyyy hh\:nn\;ss\#") &
_
" WHERE TimeLog.TimeID=" & Forms!AdjustDateTime!TxtLog
DoCmd.RunSQL (DateInSQL)
DoCmd.SetWarnings True

If you must keep them as 2 separate fields, there's no reason to have two
separate Update statements:

Dim DateInSQL As String
DoCmd.SetWarnings False
DateInSQL = "UPDATE TimeLog " & _
"SET TimeLog.DateIn = " & _
Format(Forms!AdjustDateTime!TxtDAdjust, "\#mm\/dd\/yyyy\#") & _
", TimeLog.TimeIn = " & _
Format(Forms!AdjustDateTime!TxtTAdjust, "\#hh\:nn\:ss\#") & _
" WHERE TimeLog.TimeID=" & Forms!AdjustDateTime!TxtLog
DoCmd.RunSQL (DateInSQL)
DoCmd.SetWarnings True

Note that I'm assuming that TimeId is a numeric field. If it's text, you
need to change the WHERE clause to

" WHERE TimeLog.TimeID=" & Chr$(34) & Forms!AdjustDateTime!TxtLog &
Chr$(34)

 

[DS]

I'd strongly recommend having a single DateTimeIn field, rather than two
separate fields.

Dim DateInSQL As String
DoCmd.SetWarnings False
DateInSQL = "UPDATE TimeLog " & _
"SET TimeLog.DateTimeIn = " & _
Format(Forms!AdjustDateTime!TxtDAdjust, "\#mm\/dd\/yyyy hh\:nn\;ss\#") &
_
" WHERE TimeLog.TimeID=" & Forms!AdjustDateTime!TxtLog
DoCmd.RunSQL (DateInSQL)
DoCmd.SetWarnings True

If you must keep them as 2 separate fields, there's no reason to have two
separate Update statements:

Dim DateInSQL As String
DoCmd.SetWarnings False
DateInSQL = "UPDATE TimeLog " & _
"SET TimeLog.DateIn = " & _
Format(Forms!AdjustDateTime!TxtDAdjust, "\#mm\/dd\/yyyy\#") & _
", TimeLog.TimeIn = " & _
Format(Forms!AdjustDateTime!TxtTAdjust, "\#hh\:nn\:ss\#") & _
" WHERE TimeLog.TimeID=" & Forms!AdjustDateTime!TxtLog
DoCmd.RunSQL (DateInSQL)
DoCmd.SetWarnings True

Note that I'm assuming that TimeId is a numeric field. If it's text, you
need to change the WHERE clause to

" WHERE TimeLog.TimeID=" & Chr$(34) & Forms!AdjustDateTime!TxtLog &
Chr$(34)

Thanks Doug, it worked great!
DS