IIF statement failing

H

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
 
K

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"),"")
 
J

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
 
H

Hank

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

Klatuu said:
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


Hank said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

IIF statement 6
Past Due Expression 1
Calucation in query 2
Calucation in query 7
Sorting empty fields 1
Mail Merge Help 1
Self Join Problem 1
Need some PWA help 0

Top