Print some rows as currency and some as percents

[Guest]

I'm printing a report from a table that I built. The data is very complex and
involves lots of calculations. The report is just rows and columns of figures
but some figures are currency amounts, some are just numbers and some are
percentages. I have a colum in my table called 'format' which I set to 'C',
'P' or 'N' - now I'd like to set the FORMAT and DECIMAL PLACES properties of
the report controls dynamically based on this value (which is a hidden
control on my form). Can this be done and if so, what is the syntax?
I've tried it in the FORMAT event but I can't refer to those properties.
-David

 

[Allen Browne]

Try setting the Control Source of the text box to something like this:
=Switch([FormatType] = "C", Format([Amount], "Currency"),
[FormatType] = "P", Format([Amount], "Percent"),
True, Format([Amount], "0." & String([Places],"0")

Notes:

1. Assumes:
- the number field is named Amount;
- the format type is named FormatType;
- the number of decimal places is in a field named Places.

2. Do not set the Format property of this text box. To Access it will be
text (not a number), but you will not want to add up something that is a mix
of types anyway.

3. Make sure the Name of this text box is not the same as any field names.
Something like Text9 is okay, but not Amount, FormatType, Places, etc.

4. Sometimes Access reports have trouble referring to fields that are not on
the report. If necessary, add text boxes for Amount, FormatType, or Places.

5. The True in the 3rd pair is a way of creating an Else in the Switch()
function. This will error if Places is Null, so you might need to take it a
bit further.