Nz function still a problem

  • Thread starter Thread starter Enderjit Singh
  • Start date Start date
E

Enderjit Singh

I run this module to change the date formats of my fields
from DDMMYY to YYYYMMDD, but as I have fields that I have
no values in them I get error messages. How can I get the
module to run ignoring those Null values?

How do I fit the Nz function in the below query:
LastDNAdate = Format(DateSerial(Right(rbk![LastDNAdate],
2), Mid(rbk![LastDNAdate], 3, 2), Left(rbk![LastDNAdate],
2)), "yyyymmdd")
 
LastDNAdate = IIF(Not IsNull([LastDNAdate], Format(DateSerial(Right(rbk![LastDNAdate],
2), Mid(rbk![LastDNAdate], 3, 2), Left(rbk![LastDNAdate], 2)), "yyyymmdd"))

This will test for Null and process that record if it isn't. Nz will replace the Null with
a default value. You could do the same here by adding in the False part of the IIF.
 
I am still getting problems.

I get a error message when I run the module...Invalid use
of NULL value
 
I misunderstood. Your description included the word "query", and you posted
in the queries newsgroup. Wayne's response explains the difference between
using IIF() (in a query), and If ...Then (in a code module).

Jeff Boyce
<Access MVP>
 
Back
Top