- Product: Voyager
- Relevant for Installation Type: Multi-Tenant Direct, Dedicated-Direct, Local, TotalCare
How to format a value as currency in Prepackaged Access Reports.
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:
- divide (FINE_FEE_BALANCE by 100 to change it from cents to dollars
- 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.
For more on using the CCur function see: Using the CCur function in Voyager Prepackaged Access Reports
Constructing custom SQL queries by request and troubleshooting unexpected results from customer-created SQL queries falls outside the scope of Support. The above has been posted for informational purposes. Voyager-L and Developer Network are useful resources for finding helpful custom SQL or obtaining assistance from peers in troubleshooting custom queries.
- Article last edited: 21-Jun-2021