Check for empty date in RunSQL command

[Guest]

trying to check for an empty date field in a runsql command like this:
DoCmd.RunSQL "UPDATE my_table " & _
" set ls_versand_dat = '" & Now() & "' WHERE " & _
" ls_datum = " & my_filter & " and " &
IsEmpty(ls_datum) & " = true;"
But this isn't working.

Any helpful hint???

jokobe

 

[Douglas J. Steele]

Try to explain in words what you're trying to do!

First of all, you don't use ' as a delimiter for date values: you use #. You
also need to ensure that the date is in a recognizable format to Access,
just in case your user has set his/her Regional Settings for a Short Date
format of dd/mm/yyyy. My recommendation would be:

" set ls_versand_dat = " & Format(Now(), \#mm\/dd\/yyyy hh\:nn\:ss\#") & "
WHERE " & _

What are you hoping that " and " & IsEmpty(ls_datum) & " = true;" is going
to do? Remember that you're building up the SQL string in VBA, so
IsEmpty(ls_datum) is going to be evaluated before the SQL string is
executed. In other words, it doesn't refer to ls_datum in my_table, but to a
variable in VBA.

Fields in tables aren't empty, they're Null. If you're trying to set a date
for those records where ls_datum doesn't exist in the table, try:

DoCmd.RunSQL "UPDATE my_table " & _
" SET ls_versand_dat = " & Format(Now(), \#mm\/dd\/yyyy hh\:nn\:ss\#") &
_
" WHERE ls_datum IS NULL"

I notice, though, that you've also got " ls_datum = " & my_filter. If
ls_datum is Null, that filter isn't going to do anything.

 

[Guest]

HI Douglas,

without the delimiter I receive an error message.
I tried the "ls_datum is null " - but it is not catching the empty
recordsets.



Jokobe
(Joachim )

 

[Douglas J Steele]

Sorry, typo in what I suggested. It should be:

Format(Now(), "\#mm\/dd\/yyyy hh\:nn\:ss\#")

What's the exact SQL statement you've got now?