String concatenation in SQL statement VBA

Question

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, );"

The last comma looks supsicious: "TEXT, );" Is that causing a syntax error? — Logard
– Logard, Commented Jan 5, 2015 at 15:38

Codeek · Accepted Answer · 2015-01-05 15:38:07Z

1

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

answered Jan 5, 2015 at 15:38

Codeek

1,6341 gold badge11 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Pablo Over a year ago

"CREATE TABLE ThisTable (StateMedicaid TEXT, Commercial TEXT, HIX TEXT, MMP TEXT, CMSPartDCY" & year & " TEXT,  CMSPartDCY " & (year + 1) & " TEXT )"

This is what I put in and it is causing a syntax error in the field definition.

Codeek Over a year ago

"CMSPartDCY " & (year + 1)"" is whats causing you problem, notice the space after Y

Pablo Over a year ago

Thank you. Would it be possible to use parentheses in the field names? I was confused by your explanation in the answer above.

Codeek Over a year ago

It is, but is highly recommended not to use them as it will simply make things more complex (also mentioned by @HansUp). Still if you are so much into differentiating the year from text use _ instead. And I'll try to be better at explanations next time onwards. You see writing down explanation is not something I am good at. :P

HansUp · Accepted Answer · 2015-01-05 15:59:31Z

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

Collectives™ on Stack Overflow

String concatenation in SQL statement VBA

2 Answers 2

4 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Related