TIME DATE Problem

D

DS

I have this SQL statement that seems to not reconize that I am trying to
INSERT the current Time and Date, I tried the #
but that doesnt seem to work. Any help appreciated.
Thanks
DS

DoCmd.SetWarnings False
TimeOutSQL = "UPDATE TimeLog
Set TimeLog.TimeOut =#TIME#,TimeLog.DateOut=#DATE# " & _
"WHERE TimeLog.EmployeeID = Forms!INOUT!TxtID " & _
"AND TimeLog.TimeOut=Null " & _
"AND TimeLog.DateOut=Null;"
DoCmd.RunSQL (TimeOutSQL)
DoCmd.SetWarnings True
 
J

John Spencer

Assuming that EmployeeID is a string value

TimeOutSQL = "UPDATE TimeLog " & _
" Set TimeLog.TimeOut =Time(), " & _
" TimeLog.DateOut=DATE() " & _
" WHERE TimeLog.EmployeeID =" & Chr(34) & Forms!INOUT!TxtID & Chr(34) & _
" AND TimeLog.TimeOut Is Null" & _
" AND TimeLog.DateOut Is Null;"

If EmployeeID is a number value then remove the Chr(34) & in the above.

TimeOut = Null will never be true, since nothing is ever equal to null
(including null)
DateOut = Null (see above)
Forms!INOUT!TxtID - I think you need the value of this control, instead of
referencing the control itself. I could be wrong here, since I vaguely
recall that DoCmd.RunSQL will use Access to interpret the SQL statement
prior to Jet processing the SQL request.
 
D

DS

John said:
Assuming that EmployeeID is a string value

TimeOutSQL = "UPDATE TimeLog " & _
" Set TimeLog.TimeOut =Time(), " & _
" TimeLog.DateOut=DATE() " & _
" WHERE TimeLog.EmployeeID =" & Chr(34) & Forms!INOUT!TxtID & Chr(34) & _
" AND TimeLog.TimeOut Is Null" & _
" AND TimeLog.DateOut Is Null;"

If EmployeeID is a number value then remove the Chr(34) & in the above.

TimeOut = Null will never be true, since nothing is ever equal to null
(including null)
DateOut = Null (see above)
Forms!INOUT!TxtID - I think you need the value of this control, instead of
referencing the control itself. I could be wrong here, since I vaguely
recall that DoCmd.RunSQL will use Access to interpret the SQL statement
prior to Jet processing the SQL request.
Thanks, Your right about the null thing, I switched it to
"AND IsNull(TimeLog.TimeOut)

also I made the TIME field as such
#" & TIME & "#

ditto for the Date

This all seems to work now. Thank you for your input.
DS
 
D

Douglas J. Steele

FWIW, I'd recommend very strongly storing the date and time in the same
field, and using the Now() function to give the values. Should you require
date only or time only in certain cases, you can use the DateValue or
TimeValue function to return only what's required. In fact, you can use
those functions in a query and use the query wherever you would otherwise
have used the table.
 
D

DS

Douglas said:
FWIW, I'd recommend very strongly storing the date and time in the same
field, and using the Now() function to give the values. Should you require
date only or time only in certain cases, you can use the DateValue or
TimeValue function to return only what's required. In fact, you can use
those functions in a query and use the query wherever you would otherwise
have used the table.
I need the values seperated, so upon your recomendation how would I use
this DateValue thing?
Thnaks
DS
 
D

Douglas J. Steele

DS said:
I need the values seperated, so upon your recomendation how would I use
this DateValue thing?

As I said, you'd store the entire timestamp in one field

TimeOutSQL = "UPDATE TimeLog " & _
" Set TimeLog.TimeStampOut =Now(), " & _
" WHERE TimeLog.EmployeeID =" & _
Chr(34) & Forms!INOUT!TxtID & Chr(34) & _
" AND TimeLog.TimeStampOut Is Null" & _


You could then have a query that uses DateValue and TimeValue to extract
only the appropriate parts:

SELECT EmployeeID, Field1, Field2, TimeStampOut,
DateValue(TimeStampOut) AS DateOut,
TimeValue(TimeStampOut) AS TimeOut
FROM TimeLog

In general, I think you'll find that using TimeStampOut will make queries
easier, and make it easier to use DateDiff and other Date/Time functions.
 

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

SQL Date/Time Update 2
ALTER COLUMN syntax 2
SQL Syntax Error 6
Select From 1
Syntax error in Update statement 3
First Effort Using VBA Code on ComboBox 3
SQL Percent Problem 2
Insert into loop not working 3

Top