Skip to main content
  • Subscribe by RSS
  • Ex Libris Knowledge Center

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

    • 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:

    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


    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