SQL Syntax Error

[DS]

I'm getting a Syntax Error on this.
Any Suggestions?
Thanks
DS

Dim OUTSQL As String
DoCmd.SetWarnings False
OUTSQL = "UPDATE tblTimeLog SET tblTimeLog.LogTimeOut = #" & TIME & "#, " &
_
"tblTimeLog.LogDateOut = #" & Date & "# " & _
"WHERE tblTimeLog.LogTimeOut = " & Null & " " & _
"AND tblTimeLog.LogDateOut = " & Null & " " & _
"AND tblTimeLog.LogHasChecks = 0;"
DoCmd.RunSQL (OUTSQL)
DoCmd.SetWarnings True

 

[Michel Walsh]

Change the:

...Out = " & Null & " AND ...

to

...Out IS NULL AND ...



When you compare a value with a null,

? 4 = null

the result is not true, not false, but null. See it as if asking: Are my
eye color are blue? since you don't know my eye color, that is the same as:

blue = null


(null = you don't know).

And the result of that test is, well, you don't know, which is null.



Furthermore,


? " abc= " & null & " AND def "


returns

"abc= AND def"


which is an invalid syntax. So, you were having two problems and both are
solved with a test with: IS NULL




Hoping it may help,
Vanderghast, Access MVP

 

[Bob Barrows [MVP]]

I'm getting a Syntax Error on this.
Any Suggestions?
Thanks
DS

Dim OUTSQL As String
DoCmd.SetWarnings False
OUTSQL = "UPDATE tblTimeLog SET tblTimeLog.LogTimeOut = #" & TIME &
"#, " & _
"tblTimeLog.LogDateOut = #" & Date & "# " & _
"WHERE tblTimeLog.LogTimeOut = " & Null & " " & _
"AND tblTimeLog.LogDateOut = " & Null & " " & _
"AND tblTimeLog.LogHasChecks = 0;"
DoCmd.RunSQL (OUTSQL)
DoCmd.SetWarnings True

One cannot troubleshoot a sql statement without seeing what it is. So
your first step needs to be to write it to the debug window and look at
it:

debug.write OUTSQL

Usually, the mistake will stick out like a sore thumb at this point. if
not:
Second step: use the Access Query Builder to create a query that does
the same thing your code is doing. Switch to SQL View. Compare the
generated sql to the sql you created in your code. now the error should
be really obvious. If not, show us the sql generated by your code.

 

[Allen Browne]

The most serious issue here is that nothing equals Null.
Use:
"WHERE tblTimeLog.LogTimeOut Is Null " & _
"AND tblTimeLog.LogDateOut Is Null " & ...
More information in:
Common errors with Null
at:
http://allenbrowne.com/casu-12.html

The next problem is that you turned SetWarnings off. Consequently, you have
no idea of whether the query worked, and (if it did), of how many records
were affected. It would be better to Execute the query. More info in:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html


Finally, if this code ever runs outside the US, the way the Date and Time
are concatenated into the string could give wrong results. Explicity format
them, as explained here:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

That should get you up and running.

 

[DS]

IS Null worked
Thanks
DS

 

[DS]

Thnaks For the advice about the debugger and the Query Grid. I'll be using
that in the future.
DS

 

[DS]

Thanks Allen,
I always appreciate your advice. Thanks'for the article.
DS