IIF statement failing

[Hank]

I use the following in a query:
PastDue: IIf([DueDate]<Date(),Format(25+[FeeDue]-[AmtPaid],"currency"),"")

Something has changed. The code worked great for a while, but not it will
not display a past due amount UNLESS there is a value in the FeeDue and
AmtPaid fields. If the amount in those fields is blank there is no PastDue
amount showing.

Where have I gone wrong?
Hank

 

[Klatuu]

Use the Nz function to convert Null fields to 0; otherwise, a null value will
be returned if either of the fields is Null. If any field in a calculation
is Null, the calculation returns Null. I did this in the debug window as an
example:

?3 - null
Null
?null - 3
Null

This is the fix:
PastDue:
IIf([DueDate]<Date(),Format(25+Nz([FeeDue],0)-Nz([AmtPaid],0),"currency"),"")

 

[Jeff Boyce]

Hank

Define "blank"... no really!

Blank might mean nothing at all in there (i.e., Null), in which case your
expression is trying to do calculations on "nothing at all". Access handles
this by assuming that nothing can be determined if at least one of the
terms/factors is "nothing at all".

Or blank might mean "zero-length string" (i.e., ""), in which case how would
you do math on it anyway?!

Take a look at the Nz() function and consider what you want to have happen
if [FeeDue] or [AmtPaid] does not exist.

Alternatively, you could modify your query to ONLY select rows in which
[FeeDue] AND [AmtPaid] are neither Null nor "".

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Hank]

Thanks to you and Jeff. Works great now.
I need to study more!

Use the Nz function to convert Null fields to 0; otherwise, a null value
will
be returned if either of the fields is Null. If any field in a
calculation
is Null, the calculation returns Null. I did this in the debug window as
an
example:

?3 - null
Null
?null - 3
Null

This is the fix:
PastDue:
IIf([DueDate]<Date(),Format(25+Nz([FeeDue],0)-Nz([AmtPaid],0),"currency"),"")
--
Dave Hargis, Microsoft Access MVP

I use the following in a query:
PastDue:
IIf([DueDate]<Date(),Format(25+[FeeDue]-[AmtPaid],"currency"),"")

Something has changed. The code worked great for a while, but not it
will
not display a past due amount UNLESS there is a value in the FeeDue and
AmtPaid fields. If the amount in those fields is blank there is no
PastDue
amount showing.

Where have I gone wrong?
Hank