If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
IIF statement failing
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 |
#2
|
|||
|
|||
IIF statement failing
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" wrote: 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 |
#3
|
|||
|
|||
IIF statement failing
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" wrote in message ... 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 |
#4
|
|||
|
|||
IIF statement failing
Thanks to you and Jeff. Works great now.
I need to study more! "Klatuu" wrote in message ... 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" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|