IsEmpty and If statments

[brainonastick]

I have a calculation field called feesSummary which aggregates the data from a series of other fields depending on whether they are empty or not. However when all referenced fields are empty the hard coded currency text surrounding the fields (eg "$" & fieldName & ".00") still displays though the if statement should result in "". Here is the calculation code for the feesSummary field:

If(IsEmpty(flatFee) = 0; "$" & flatFee & ".00" & ¶; "") &

If(IsEmpty(fullFee) = 0; "$" & fullFee & ".00 " & feesNotes & ¶; "") &

If((IsEmpty(fullFee) = 0 & IsEmpty(vipFee) = 0); "$" & fullFee & ".00/$" & vipFee & ".00 VIP " & feesNotes & ¶; "") &

If((IsEmpty(fullTraining) = 0 & IsEmpty(vipTraining) = 1); "$" & fullTraining & ".00" & ¶; "") &

If((IsEmpty(fullTraining) = 0 & IsEmpty(vipTraining) = 0); "$" & fullTraining & ".00/$" & vipTraining & ".00 VIP" & ¶; "") &

If((IsEmpty(fullTandA) = 0 & IsEmpty(vipTandA) = 1); "$" & fullTandA & ".00" & ¶; "") &

If((IsEmpty(fullTandA) = 0 & IsEmpty(vipTandA) = 0); "$" & fullTandA & ".00/$" & vipTandA & ".00 VIP" & ¶; "") &

If((IsEmpty(fullAssessment) = 0 & IsEmpty(vipAssessment) = 1); "$" & fullAssessment & ".00" & ¶; "") &

If((IsEmpty(fullAssessment) = 0 & IsEmpty(vipAssessment) = 0); "$" & fullAssessment & ".00/$" & vipAssessment & ".00 VIP" & ¶; "") &

If((IsEmpty(day1) = 0 & IsEmpty(vipD1) = 1); "$" & day1 & ".00" & ¶; "") &

If((IsEmpty(day1) = 0 & IsEmpty(vipD1) = 0); "$" & day1 & ".00/$" & vipD1 & ".00 VIP" & ¶; "") &

If((IsEmpty(day2) = 0 & IsEmpty(vipD2) = 1); "$" & day2 & ".00" & ¶; "") &

If((IsEmpty(day2) = 0 & IsEmpty(vipD2) = 0); "$" & day2 & ".00/$" & vipD2 & ".00 VIP" & ¶; "") &

If((IsEmpty(day3) = 0 & IsEmpty(vipD3) = 1); "$" & day3 & ".00" & ¶; "") &

If((IsEmpty(day3) = 0 & IsEmpty(vipD3) = 0); "$" & day3 & ".00/$" & vipD3 & ".00 VIP" & ¶; "") &

If(IsEmpty(earlybirdD1) = 0; "$" & earlybirdD1 & ".00" & ¶; "") &

If(IsEmpty(earlybirdD2) = 0; "$" & earlybirdD2 & ".00" & ¶; "") &

If(IsEmpty(earlybirdD3) = 0; "$" & earlybirdD3 & ".00" & ¶; "")

When all referenced fields are empty the field displays like this:

$.00/$.00 VIP

$.00/$.00 VIP

$.00/$.00 VIP

$.00/$.00 VIP

$.00/$.00 VIP

$.00/$.00 VIP

$.00/$.00 VIP

when it should be empty. I have identical code in other databases and it displays fine.

I have tried it with "Do not evaluate if all referenced fields are empty" switched on and off and it makes no difference.

Calculation result is set as "text".

Something is causing the if statements to read the empty fields as not empty but I don't know what.

Any feedback gratefully received.

[comment]

I have identical code in other databases and it displays fine.

Why don't you post a file showing the issue.

[Vaughan]

Check whether the fields really are empty. No spaces, not containing zero. Empty.

Scrub that... you are using the concatenate operator (&) instead of logical "and".

If((IsEmpty(day1) = 0 AND IsEmpty(vipD1) = 1); "$" & day1 & ".00" & ¶; "") &

I'd be checking the identical code in the other databases, it could be wrong too: it's not doing what you think it is.

You could simplify the expressions somewhat too, but this is points for style more than anything else:

If( not IsEmpty(day1) AND IsEmpty(vipD1) ; "$" & day1 & ".00" & ¶ ) &

[brainonastick]

Thanks Vaughan - that's fixed it. AND not &.