Skip to main content
ExLibris

Knowledge Assistant

BETA
 
  • Subscribe by RSS
  • Back
    Voyager

     

    Ex Libris Knowledge Center
    1. Search site
      Go back to previous article
      1. Sign in
        • Sign in
        • Forgot password
    1. Home
    2. Voyager
    3. Knowledge Articles
    4. Brief overview of string functions in Voyager Prepackaged Access Reports

    Brief overview of string functions in Voyager Prepackaged Access Reports

    1. Last updated
    2. Save as PDF
    3. Share
      1. Share
      2. Tweet
      3. Share
    1. Question
    2. Answer
    3. Examples of commonly used string functions 
    4. Examples of BIB and MFHD string operations
    5. Additional Information
    • 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()

    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
    View article in the Exlibris Knowledge Center
    1. Back to top
      • Brief overview of Running Queries in Voyager Prepackaged Access Reports
      • Brief Overview of Tables in Voyager Prepackaged Access Reports
    • Was this article helpful?

    Recommended articles

    1. Article type
      Topic
      Language
      English
      Product
      Voyager
    2. Tags
      This page has no tags.
    1. © Copyright 2025 Ex Libris Knowledge Center
    2. Powered by CXone Expert ®
    • Term of Use
    • Privacy Policy
    • Contact Us
    2025 Ex Libris. All rights reserved