UPDATE NULL RECORD

[DS]

I'm trying to Update a blank record with this statement. It's blank but
I'm not sure if it's null. It's a Time field and a Date Field. This
statement isn't Updating anything. Any help appreciated.
Thanks
DS

Dim LOGSQL As String
LOGSQL = "UPDATE tblTimeLog SET tblTimeLog.LogTimeOut =
Time(),tblTimeLog.LogDateOut = Date() " & _
"WHERE tblTimeLog.LogTimeOut = Null " & _
"AND tblTimeLog.LogDateOut = Null;"
DoCmd.RunSQL (LOGSQL)

 

[Douglas J. Steele]

You can't use equals (or not equals, for that matter) in conjunction with
Null. Instead, you must use IS NULL:

LOGSQL = "UPDATE tblTimeLog " & _
"SET tblTimeLog.LogTimeOut = " & _
Time() & ", " & tblTimeLog.LogDateOut = Date() " & _
"WHERE tblTimeLog.LogTimeOut IS Null " & _
"AND tblTimeLog.LogDateOut IS Null;"

Note, too, the other correcting I made to your string: your comma wasn't
correct (it needed to be in quotes)

 

[DS]

You can't use equals (or not equals, for that matter) in conjunction with
Null. Instead, you must use IS NULL:

LOGSQL = "UPDATE tblTimeLog " & _
"SET tblTimeLog.LogTimeOut = " & _
Time() & ", " & tblTimeLog.LogDateOut = Date() " & _
"WHERE tblTimeLog.LogTimeOut IS Null " & _
"AND tblTimeLog.LogDateOut IS Null;"

Note, too, the other correcting I made to your string: your comma wasn't
correct (it needed to be in quotes)

Worked Great! Thanks.
I was wondering would
ISNull(tblTimeLog.LogTimeOut)
Work as well?
Once again Thank You.
DS

 

[Douglas J. Steele]

Worked Great! Thanks.
I was wondering would
ISNull(tblTimeLog.LogTimeOut)
Work as well?
Once again Thank You.

I believe it would depend on whether you're running the query from within
Access, or from outside of Access (say, from Excel or through a VB program).

From within, it should work. From without, I don't think so.

Incidentally, I didn't look that closely at what you were doing before.
Having Date and Time as two separate fields is seldom a good idea. Use a
single field (say, LogOutTimestamp), and populate it with the Now()
function. If there are occasions when you only need the date, use the
DateValue function on the LogOutTimestamp. Similarly, if there are times
when you only need the time, use the TimeValue function. You'll find it's
much easier to deal with the field in SQL statements if it's a single field.

 

[DS]

I believe it would depend on whether you're running the query from within
Access, or from outside of Access (say, from Excel or through a VB program).

From within, it should work. From without, I don't think so.

Incidentally, I didn't look that closely at what you were doing before.
Having Date and Time as two separate fields is seldom a good idea. Use a
single field (say, LogOutTimestamp), and populate it with the Now()
function. If there are occasions when you only need the date, use the
DateValue function on the LogOutTimestamp. Similarly, if there are times
when you only need the time, use the TimeValue function. You'll find it's
much easier to deal with the field in SQL statements if it's a single field.

Thanks Douglas...I appreciate the advice! It's never to late to learn
something new!
DS