Inconsistant results with if then statement

[AZdesertdog]

Using cr10 with MSSQL db.

I must have something wrong with my structure of the following if/then statement. If I individually comment out all but one of the 4 conditions and add the totals I get my desired results, but when they are all in the formula I get a much smaller number than expected. Any help would be appreciated. Thanks.

if
({InvPayorAllView.ReSequenceNumber}=0)
or
(isnull ({Header.FirstBillDate}) and {InvPayorAllView.ReSequenceNumber}=10)
or
(({Header.LastBillDate}={Header.PrivatePayDate})and {InvPayorAllView.ReSequenceNumber}=10)
or
({Header.LastBillDate}={InvPayorAllView.LastBillDate})
then {Header.CurrentBalance}else 0

 

[Jameson55]

Does it make any difference if you enclose everything from the IF statement to the THEN statement in parentheses? -JB

 

[AZdesertdog]

No change with the parens.

 

[lbass]

I think the problem is that if a record meets the first criterion, it won't be evaluated for later ones, so if you have records that meet more than one criteria and you want to sum these records each time they meet any criterion, then your formula won't work.

-LB

 

[AZdesertdog]

LB-
I see what you're saying. If the first (or second, or third) criteria is met and it then skips to the 'then' part, that's fine. It's when it doesn't evaluate all the lines that seems to be the problem. Something else I've noticed is I get some blank fields even though I have the "else 0" ending to the statement. Don't know if that lends a clue to the problem or not.

 

[mbarron]

Another thing to try would be to move the isnull to the first location:

if
(isnull ({Header.FirstBillDate}) and {InvPayorAllView.ReSequenceNumber}=10)
or...


Mike

 

[Madawc]

Do an experimental version, or maybe an extra section that you can hide or remove once you have the problem solved. Split the tests into several formulae and see what you get.

Madawc Williams (East Anglia)