Go Back   Access World Forums > Microsoft Access Reference > Code Repository

 
Closed Thread
 
Thread Tools Rate Thread Display Modes
Old 03-24-2019, 01:34 AM   #1
essaytee
Need a good one-liner.
 
essaytee's Avatar
 
Join Date: Oct 2008
Location: Melbourne, Australia
Posts: 512
Thanks: 9
Thanked 123 Times in 119 Posts
essaytee is on a distinguished road
Code snippet - to quote strings - single or double or something else

I always find double quoting very difficult on the eyes and so easy to stuff it up, missing quotes or too many quotes. To make life easier, why not write a little function where your string to be quoted is passed in and the output is a correctly quoted string. I use the following, hope you find it of value.

Code:
Code:
Function Enclose(Optional pStr As Variant = "", Optional pIntType As Integer = 2) As String
    
    ' use this instead of directly writing " " "  or " ' "  or " # " in SQL statements, eases confusion
    ' It will make writing SQL code easier, easier code to view.
    ' Author:   Steven Taylor aka essaytee
    ' Date:     23 Feb 2019
    '
    ' INPUTS    pStr        The string value to be enclosed     (default value is an empty string)
    '           pIntType    0 = Not enclosed
    '                       1 = Single quote '
    '                       2 = Double quote "                  (default value if not passed in)
    '                       3 = Pound/Hash #
    '                       4 = Less/Greater than < >
    '                       5 = Square brackets [ ]
    '                       6 = Curly braces { }
    '                       7 = Parenthesis ( )
    '
    ' OUTPUT    The passed in string (pStr) enclosed in quotes or hash or <> []
    '
    '
    ' NOTES     This simple function could be further developed to account for other enclosing symbols
    '           To find the Chr$ number of symbol, Immediate window eg. ? Asc("<") will give you the number
    '
    ' Sample sql
    ' Dim strSql as string
    ' strSql = "SELECT * FROM tblWhatever WHERE FldName = " & Enclose(Me.txtSearchName) & " AND FldAddress = " & Enclose(Me.txtSearchAddress) & ";"
    ' As written in line above, the string values will be returned enclosed in double quotes.
    '
    ' Optional:     Why does pStr default to an empty string.  Legacy issue for me, I did have functions just returning " or ' or #
    ' Lastly:       In relation to dates I've created another similar function that accounts for the US format, required for SQL, and encloses in #
    '
    '
    
    Dim strEncloseLeft As String
    Dim strEncloseRight As String
            
    Select Case pIntType
        Case 1 ' Single quote - think 1 as single quote
            strEncloseLeft = Chr$(39)
            strEncloseRight = Chr$(39)
        Case 2 ' Double quote - think 2 as two quotes, therefore double quote
            strEncloseLeft = Chr$(34)
            strEncloseRight = Chr$(34)
        Case 3 ' Pound/Hash #
            strEncloseLeft = Chr$(35)
            strEncloseRight = Chr$(35)
        Case 4 ' Less than, greater than   < >
            strEncloseLeft = Chr$(60)
            strEncloseRight = Chr$(62)
        Case 5 ' Square brackets [ ]
            strEncloseLeft = Chr$(91)
            strEncloseRight = Chr$(93)
        Case 6 ' Curly braces { }
            strEncloseLeft = Chr$(123)
            strEncloseRight = Chr$(125)
        Case 7 ' parenthesis ( )
            strEncloseLeft = Chr$(40)
            strEncloseRight = Chr$(41)
    End Select
        
    If pStr = "" Then
        satEnclose = strEncloseLeft
    Else
        satEnclose = strEncloseLeft & pStr & strEncloseRight
    End If
End Function
Obviously, you can change the name of the function, shorten it, whatever, but I'm sure you get the idea of what is going on.

I did post this the other day against another thread but have since thought it may be more useful in its own thread.

__________________
Steve.

I know UNIX, PASCAL, C, FORTRAN, COBOL, and nineteen other high-tech words.

Last edited by essaytee; 03-28-2019 at 05:27 PM. Reason: satEnclose() to Enclose()
essaytee is offline  
The Following 2 Users Say Thank You to essaytee For This Useful Post:
NauticalGent (03-24-2019), Zydeceltico (04-09-2019)
Old 03-24-2019, 05:18 AM   #2
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 1,994
Thanks: 442
Thanked 295 Times in 256 Posts
NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about
Re: Code snippet - to quote strings - single or double or something else

Not bad, a little work on the front end but I can definitely see practical use for this. Thanks for sharing.
__________________
- Ciao, John
NauticalGent is online now  
Closed Thread

Tags
double quotes , single quotes , sql

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
String search for a literal Double Quote gblack Modules & VBA 18 01-18-2019 01:21 PM
Concatenate with Double Quote razorking Queries 2 09-17-2010 12:30 PM
How to eliminate a double quote Jacob Mathai General 3 11-13-2007 06:54 AM
Access import fails at any double quote SimpleSimeon General 0 03-09-2006 01:17 PM
[SOLVED] Removing double-quote from field cmiker Queries 5 09-03-2003 11:08 AM




All times are GMT -8. The time now is 05:41 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World