Microsoft Access 2010 Bible

Chapter 20: Advanced Access Report Techniques

735

FIGURE 20.28

txtPageNumber jumps from right to left.

Using different formats in the same text box

On some reports, you may want the format of certain fields in a record to change according to the

values in other fields on the report. A good example is a journal voucher report in a multicurrency

financial system in which the voucher detail debit or credit amount format varies according to the

number of decimal places used to display the currency value.

Unfortunately, a control in a Detail section of a report can have but a single format specified in its

Property Sheet. Use the following trick to flexibly set the format property at run time. The

FlexFormat() function uses the iFmt argument to return a string specifying the desired format:

Function FlexFormat (iFmt As Integer) As String

Select Case iFmt

Case 1 : FlexFormat = “##0.0;(##0.0)”

Case 2 : FlexFormat = “##0.00;(##0.00)”

Case 3 : FlexFormat = “##0.000;(##0.000)”

Case 4 : FlexFormat = “##0.0000;(##0.0000)”

End Select

End Function

Assume that the field to be dynamically formatted has its ControlSource set to [Amount]. The

format of the Amount text box should vary depending on the value of the CurrDecPlaces field

in the same record. CurrDecPlaces is an Integer data type. To use FlexFormat, change the

ControlSource property of the Amount text box to the following:

=Format([Amount],FlexFormat([CurrDecPlaces]))