Does not recognize >=10

  • Thread starter Thread starter kratz
  • Start date Start date
K

kratz

I am working on this If statement:

ExpToDate: CCur(IIf(((([CurrentLookup]-[StartLookup])+1)<[Number of
Months]),((([CurrentLookup]-[StartLookup])+1)*[Monthly Deduction]),([Number
of Months]*[Monthly Deduction])))

Monthly payments start at a certain month (StartLookup), continue for a
specified Number of Months, and then stop. I would like to know the amount of
the payments so far. (Some payments may be ended, some not.)

The problem I am having is, when the Number of Months is 10 or greater, it
thinks that all months should have been paid. Example: Payments of $10.00
that started in January and run for 12 months (total will be $120.00). We are
in the 8th month, so the formula should result in $80.00, but it is showing
$120.00.

It is weird because it recognizes 10 to multiply, but not when doing the
comparison.
 
Try this --
ExpToDate: CCur(IIf(((([CurrentLookup]-[StartLookup])+1)<CLng([Number of
Months])),((([CurrentLookup]-[StartLookup])+1)*[Monthly Deduction]),([Number
of Months]*[Monthly Deduction])))
 
What is in the field [CurrentLookup]?

Given your table structure, what happens if [StartLookup] is 8 and the
[Number of months] should be 6?

Is there a chance that [CurrentLookup] or [StartLookup] is NULL? If either
of these is possible, then the [CurrentLookup]-[StartLookup]+1 will return a
NULL, and the comparison to [Number of Months] will also return a NULL, which
the IIF() function will interpret as False).
 
Back
Top