Skip to main content
ExLibris
  • 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

     

    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:

    clipboard_eb884c8353e4ba4b5645cde578df2cdf6.png

     

    Here are the initial results:

    clipboard_e1c8d159a76d4d5743457f2de67931138.png

     

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

    clipboard_e82bdd12b6ebe52b75221d3df0b2b7631.png

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

    clipboard_e6b907fa9ecd308163ba103c149dfad0c.png

     

    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