SQL Date/Time Update

D

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
 
D

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)
 
D

DS

Douglas said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Syntax error in Update statement 3
Select From 1
Syntax Error WHY? 16
Write to other DB 7
First Effort Using VBA Code on ComboBox 3
Multiple SQL Stuff 6
SQL UPDATE Problem 5
Function or Variable Expected 6

Top