CODEWITHSUNDEEP
sqlms-accessvbams-access-2010
Pablo
Pablo

Reputation: 93

String concatenation in SQL statement VBA

I am writing a SQL statement as a string in an access VBA macro. I want to add a dynamic variable into the string statement but I am unsure how to concatenate it correctly. Any help would be greatly appreciated.

"CREATE TABLE ThisTable " & _
        "(StateMedicaid TEXT, Commercial TEXT, HIX TEXT, MMP TEXT, CMS Part D (CY " & _ year " TEXT,  CMS Part D (CY " & _ (year+1) " TEXT, );"

Upvotes: 0

Views: 4213

Answers (2)

HansUp
HansUp

Reputation: 97131

Codeek described the problems in your CREATE TABLE statement. Still it can be easy to misplace the required [] brackets when dealing with a field name which contains spaces and punctuation characters. (Actually, Access development is easier if you can avoid spaces and punctuation in object names.)

I add a line break after each field definition to make it easier for me to spot problems. Here is the statement produced by the following code sample. I tested it in Access 2010 and it executed without error. 

CREATE TABLE ThisTable (
    StateMedicaid TEXT,
    Commercial TEXT,
    HIX TEXT,
    MMP TEXT,
    [CMS Part D (CY 2015)] TEXT,
    [CMS Part D (CY 2016)] TEXT
    );

Dim strCreate As String
Dim intYear As String
intYear = 2015

strCreate = "CREATE TABLE ThisTable (" & vbCrLf & _
            "StateMedicaid TEXT," & vbCrLf & _
            "Commercial TEXT," & vbCrLf & _
            "HIX TEXT," & vbCrLf & _
            "MMP TEXT," & vbCrLf & _
            "[CMS Part D (CY " & intYear & ")] TEXT," & vbCrLf & _
            "[CMS Part D (CY " & (intYear + 1) & ")] TEXT" & vbCrLf & _
            ");"
Debug.Print strCreate

Note I changed the variable name from year to intYear because there is a function named Year().

Upvotes: 1

Codeek
Codeek

Reputation: 1624

just use & for concatenation

"CREATE TABLE ThisTable (StateMedicaid TEXT, Commercial TEXT, HIX TEXT, MMP TEXT, CMS Part D (CY" & year &"] TEXT,  CMS Part D ([CY " & (year+1) &"] TEXT );"

Notice : first

How & is used and not &_

second

while naming columns, don't use spaces or use [] if you want to give spaces, I've show both of them. first one removes spaces after CY so that your column name is formed without spaces, and in second one I've included [] so that you column name works with spaces as well

and already pointed out last comma should be omitted

Upvotes: 1

Related Questions