TIME DATE Problem

[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

 

[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.

 

[DS]

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

 

[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.

 

[DS]

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

 

[Douglas J. Steele]

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.