Question

How to format a value as currency in Prepackaged Access Reports.

Answer

Preface: Monetary values can be tricky to work with. In Access they are Text fields (recall that Access does not recognize numbers--all fields are either Text or Date/Time), and are integers stored in the smallest unit. In Access, the Val() Function converts Text to Number (example: Val(HISTORICAL_CHARGES)).

Let's say we want to format the FINE_FEE_BALANCE from the FINE_FEE table as currency.

Here's our initial query, which uses the "Total:" row to sum the patron's FINE_FEE_BALANCE:

Here are the initial results:

We want to format the fine total as currency.  To do that, follow the steps listed out below:

To format as currency we use the CCur Function:

1. divide (FINE_FEE_BALANCE by 100 to change it from cents to dollars
2. put CCur(    in front and    )    in back

Run your query (we've also filtered out patrons with 0 fines/fees and tidied up the column label):

Note that Access will plug in square brackets and remove the name of the TABLE. 

In this example there is only one FINE_FEE_BALANCE field in the tables.  If this field exists in more than one table, you need to PREFIX the table name to the field name and separate with a period, otherwise you'll get an error.

Additional Information

For more on using the CCur function see: Using the CCur function in Voyager Prepackaged Access Reports

• Article last edited: 21-Jun-2021