Workaround needed for adding calculated fields in a query

[Abacus1234]

I have a field used in data entry together with a flag. I put the value in this field into 3 other fields depending on the value in the flag field. I am using an IIF statement in the query expression to do this. The calculated fields end up as three columns in a report. I have been unable to add these fields together so that I can total them in the report. I cannot use Sum because each field is individual, they are not a column in a query. I have tried creating a column in the query which strings the IIF statements with a + in between, but this does not work.

IIf statement

poc816b: (Nz(IIf([816b]="borrower",Format([L816d],'$#,#00.00'),"")))
poc816s: (Nz(IIf([816b]="seller",Format([L816d],'$#,#00.00'),"")))
poc816o: (Nz(IIf([816b]="other",Format([L816d],'$#,#00.00'),"")))

[pbaldy]

Have you tried having these fields return numbers instead of strings (including a zero instead of "")? That should allow you to sum them on a report, or add them up in the query with +. Format them on the form or report, not in the expression.

[Abacus1234]

Hi Paul, Thanks, that works for the query. It leaves me with $0.00 in the report where I would prefer to have blanks. Do you have a suggestion for that? This is the finishing touch to a week of work, so it would be nice to nail it down. Appreciate your help.

[pbaldy]

You can use the Format property of the textbox to hide zeros (look for the 4-part syntax).

[Abacus1234]

Hi Paul, I found the four part syntax, I had to change the "zero" to "" and it worked. Many thanks. $#,##0.00[Green];($#,##0.00)[Red];"Zero";"Null"

[pbaldy]

Happy to help!