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

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

    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