SQL Statement Syntax - String vs. Parameter - MS Access - VBA

Question

Select SQL Statement Grouping by CNTRY. I'm trying to replace CNTRY with a variable string - strtable. What I've done all morning, thus far, results in syntax errors.

If Left(lngstr1, 2) = 19 Then
    strtable = "DTG" '<---this would replace CNTRY
Else
    strtable = "US" '<---this would replace CNTRY
End If

strsql = "SELECT 'VCAP0112 - CNTRY' AS VCAP0112, [VCAP0112 - " & strtable & "].[RECV IND] AS OMU, [VCAP0112 - " & strtable & "].[LEGACY ACCT], " _
& "Sum([VCAP0112 - " & strtable & "].[1 to 30 Day]) AS [0 - 30], Sum([VCAP0112 - " & strtable & "].[RECV BALANCE]) AS TOTAL " _
& "FROM [VCAP0112 - " & strtable & "] INNER JOIN Urcrosswalk ON [VCAP0112 - " & strtable & "].[LEGACY ACCT] = Urcrosswalk.[Legacy GL]" _
& "Group BY 'VCAP0112 - CNTRY' , [VCAP0112 - " & strtable & "].[RECV IND], [VCAP0112 - " & strtable & "].[LEGACY ACCT] " _
& "HAVING ((([VCAP0112 - " & strtable & "].[RECV IND])='O' Or ([VCAP0112 - " & strtable & "].[RECV IND])='M' Or ([VCAP0112 - " & strtable & "].[RECV IND])='U'));"

As an attempt to resolve this myself, I've gotten to the following:

  strsql = "SELECT 'VCAP0112 - '& " & strtable & " AS VCAP0112,...

But, upon execution, Access asks for parameters for either 'DTG' or 'US' String. When opening up the full Query in Design View, the String ('DTG' or 'US') is an unrecognized Variable. how do I resolve this?

Never mind, I refused to go to lunch until I've found an answer...kept working at it. Just found the correct syntax literally 5 mins after taking time to post this. Sigh... strsql = "SELECT 'VCAP0112 - '& '" & strtable & "' AS VCAP0112 — John Shaw
– John Shaw, Commented May 2, 2016 at 17:27
. . You should either delete the question or post your comment as an answer and accept it. — Gordon Linoff
– Gordon Linoff, Commented May 2, 2016 at 18:08

John Shaw · Accepted Answer · 2016-05-03 16:47:12Z

2

Strtable serves as a placeholder for which either the string 'US' or 'DTG' is to be assigned depending on an IF THEN statement. As I had it originally 1., the string - strtable - will emerge as a variable needing a parameter in the SQL Statement.

To resolve this, single quotes encapsulating the double quotes that surround the variable will turn strtable into an actual placeholder for the string assigned to it 2.

 1.  Parameter: strsql = "SELECT 'VCAP0112 - '& " & strtable & " AS VCAP0112,

 2.  String: strsql = "SELECT 'VCAP0112 - '& '" & strtable & "' AS VCAP0112,

This...this is what I learned today.

edited May 3, 2016 at 16:47

answered May 2, 2016 at 18:26

John Shaw

3584 silver badges12 bronze badges

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

Collectives™ on Stack Overflow

SQL Statement Syntax - String vs. Parameter - MS Access - VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related