Brief overview of string functions in Voyager Prepackaged Access Reports
- Product: Voyager
- Relevant for Installation Type: Multi-Tenant Direct, Dedicated-Direct, Local, TotalCare
Question
A basic overview of string functions in Voyager Prepackaged Access Reports.
Answer
Functions are small programs that always returns a value based on some calculation, comparison, or evaluation that the Function performs.
Examples:
- Convert data from one type to another (text -> number)
- Financial/math functions (sum)
- Display a field in a specific format (CCur)
Access SQL Functions can be quickly identified because they always end with parentheses ( ).
Examples:
- Now () returns current date/time.
- RND() returns a random number
- Ucase() returns the uppercase string
There are several types of functions:
- Conversion
- Date/Time
- Financial
- Mathematical
- String Manipulation
Although Conversion and Date/Time functions are important to users of Voyager Prepackaged Access Reports, string functions define an additional set of parser functions that operate on strings, and may be the most important for users.
Examples of commonly used string functions
Left()
Left function returns the leftmost n characters of a string.
Left("WebCheatSheet",3) returns "Web"
MID()
Mid function returns a string containing a specified number of characters from a string.
Mid ("access functions",8,4) returns "func"
RIGHT()
Right function returns the rightmost n characters of a string.
Right ("WebCheatSheet",11) returns "CheatSheet"
UCase()
UCase function returns a string in which all letters of an argument have been converted to uppercase.
UCase("Have a nice day") returns "HAVE A NICE DAY"
LEN()
LEN function returns the length of the specific string.
Len ("webcheatsheat.com") returns 17
Len ("hello world") returns 11
CCur function displays a field in a specific format (Currency Function)
CCur([8977]*0.01) returns $89.77
StrConv()
StrConv function converts a string to upper case, lower case, proper case (i.e., converts first letter of every word in string to upper case), or several other formats.
StrConv( "access functions",3 ) returns "Access Functions"
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.
Examples of BIB and MFHD string operations
BIB: Type:
SELECT BIB_TEXT.BIB_ID, Mid([bib_format],1,1) AS Expr1
FROM BIB_TEXT
WHERE (((Mid([bib_format],1,1)) Like "a*"));
BIB: Type of Con Resource:
SELECT BIB_TEXT.BIB_ID, Mid([bib_text]![field_008],22,1) AS Expr1
FROM BIB_TEXT
WHERE (((Mid([bib_text]![field_008],22,1))="p"));
MFHD: Display Call Number:
SELECT MFHD_MASTER.MFHD_ID, Left([MFHD_MASTER]![DISPLAY_CALL_NO],50) AS Expr1
FROM MFHD_MASTER;
MFHD: Method of Acquisition:
SELECT MFHD_MASTER.MFHD_ID, Mid([MFHD_MASTER]![FIELD_008],8,1) AS Expr1
FROM MFHD_MASTER
WHERE (((Mid([MFHD_MASTER]![FIELD_008],8,1))="p"));
Additional Information
There are hundreds of functions included with Access. A good MS Access SQL book will help you identify and use them.
Another good source of help is this Microsoft web site: https://docs.microsoft.com/en-us/office/vba/language/reference/functions-visual-basic-for-applications
The BLOB functions are a special set of functions created by Ex Libris and included in the Reports.MDB file.
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