Viewing the added and built-in Voyager Prepackaged Access Reports functions
- Product: Voyager
- Relevant for Installation Type: Multi-Tenant Direct, Dedicated-Direct, Local, TotalCare
Question
How do I see the available built-in and Ex Libris-added Prepackaged Access Reports (PpR) functions?
Answer
An expression, or calculated field, takes the values of one or more fields and uses them to create new values. String functions (or just "functions") allow you to create expressions that manipulate text in a variety of ways. For example, you may want to take just part of a field, or change a field to upper case.
There are both built-in functions that come with Microsoft Access, and functions added to the PpR Reports.MDB file by Ex Libris.
These functions can be viewed in Access Expression Builder.
Expression Builder is available when creating or editing reports or queries.
When editing a query, open the Expression Builder dialog box by selecting the query in design view, then placing your cursor in one of the fields, right-clicking and selecting Build from the menu.
Once you are in the Expression Builder dialog box, you can display the list of available built-in and added functions.
For example:
Under "Functions" you'll see the "Built-In Functions" that come with Microsoft Access, and below that a "Reports" functions element ("Reports" being the filename of your MDB file) that includes the Ex Libris added functions such as the BaseCurrency example in the above screenshot.
You can display a list of the available functions by clicking on the appropriate Expression Element and then Expression Category.
PRO TIP: Once you know the Functions, you can type them directly into your query.
Note that the name of added Expression Element will be the filename of your Reports.MDB file. In the above example the filename is Reports-support-server.MDB (most customers' files will simply be Reports.MDB).
Having trouble seeing a long BLOB function or other lengthy expression? Put your cursor in the cell you want to view and hit Shift-F2. This opens the contents of the cell in an Access zoom box.
Additional Information
See the Reporter User's Guide, Chapter 7, "Using the Expression Builder."
See also the Microsoft help pages for Visual Basic Functions: https://docs.microsoft.com/en-us/office/vba/language/reference/functions-visual-basic-for-applications
Some useful functions include1:
UCase: Convert to upper case
- UCase(CITY)
- UCase(“Navasota, Tex.”) = “NAVASOTA, TEX.”
LCase: Convert to lower case
- LCase(CITY)
- LCase(“College Station, Tex.”) = “college station, tex.”
Left: Left part of text
- Left(CALLNUM,3)
- Left(“004.12 A324d”,3) = “004”
Right: Right part of text
- Right(CALLNUM,3)
- Right(“004.12 A324d”,3) = “24d”
Mid: Part of text
- Mid(CALLNUM,5,4)
- Mid(“004.12 A324d”,5,4) = “12 A”
Mid: Remainder of text
- Mid(CALLNUM,5)
- Mid(“004.12 A324d”,5) = “12 A324d”
Date: Current date
- Date()
- Date() = #7/26/2021#
- CCur(AMOUNT)
- CCur(53.9) = $53.90
1For more information about string functions see: Brief overview of string functions 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: 26-Jul-2021