Convert nz > iif problem - stumped

[Larry Marshall]

I had a query in Access which used the nz function. Because I needed
to use the query from VB6, I had to replace the nz with iif(isnull).
The query processor gives me an error message:

"The expression you entered has a function containing the wrong
number of arguments"

I don't see it. Can someone help me out of this mess? The following is
a snippet from the query (it's too big for the whole thing). What I
don't understand is the query processor does not mind the second iif
statement, just the first. It gives me the error message when I insert
the first isnull. All I'm trying to do is come up with a new date.

TIA, Larry

((DateAdd("d",
IIf(isnull(SELECT A FROM TblB WHERE Key = M1.Key and Seq =
M1.Seq+1),0,(SELECT A FROM TblB WHERE Key = M1.Key and Seq =
M1.Seq+1)),

IIf(IsNull([M1].[MostRecentMailDate]),Date(),[M1].[MostRecentMailDate])))<=[GetParam])

 

[Michel Walsh]

Hi,


Nz and IsNull are both VBA functions.


To replace Nz( f1, f2)


use iif( f1 IS NULL, f2, f1 )

(is null, in two words )




Hoping it may help,
Vanderghast, Access MVP

I had a query in Access which used the nz function. Because I needed
to use the query from VB6, I had to replace the nz with iif(isnull).
The query processor gives me an error message:

"The expression you entered has a function containing the wrong
number of arguments"

I don't see it. Can someone help me out of this mess? The following is
a snippet from the query (it's too big for the whole thing). What I
don't understand is the query processor does not mind the second iif
statement, just the first. It gives me the error message when I insert
the first isnull. All I'm trying to do is come up with a new date.

TIA, Larry

((DateAdd("d",
IIf(isnull(SELECT A FROM TblB WHERE Key = M1.Key and Seq =
M1.Seq+1),0,(SELECT A FROM TblB WHERE Key = M1.Key and Seq =
M1.Seq+1)),

IIf(IsNull([M1].[MostRecentMailDate]),Date(),[M1].[MostRecentMailDate])))<=[
GetParam])