excel vba read SQL SELECT statement in cell

Question

I have an SQL SELECT query in a cell (image). I am attempting to use VBA to use the string in my cell as my string for an SQL query, but it reads only part of the string. In the cell, the query (without quotes) is 685 chars long. In the variable (at run time), the query is (with quotes) 185 chars long. Here is the query in the cell:

SELECT Contacts.[Last Name], 
       Contacts.[First Name], 
       Contacts.[Maricom E-Mail], 
       Contacts.[Project, Org, Team] 
FROM Contacts 
WHERE (((Contacts.[Project, Org, Team])=""Proj - MDM"") 
       AND ((Contacts.[Active?])=Yes)) 
    OR (((Contacts.[Project, Org, Team])=""Team%"") 
       AND ((Contacts.[Active?])=Yes)) 
    OR (((Contacts.[Project, Org, Team])=""Orgs - CM"") 
       AND ((Contacts.[Active?])=Yes)) 
    OR (((Contacts.[Project, Org, Team])=""Orgs - Document Team"") 
       AND ((Contacts.[Active?])=Yes)) 
    OR (((Contacts.[Project, Org, Team])=""Orgs - Process Engineering"") 
       AND ((Contacts.[Active?])=Yes)) 
    OR (((Contacts.[Project, Org, Team])=""Orgs - QA"") 
       AND ((Contacts.[Active?])=Yes)) 
ORDER BY Contacts.[Last Name];

Here is the code I use to read the cell (I used "dim myQuery as string" earlier in the code, and the cell is formatted as text):

myQuery = Range("Project1Query").Value

Here is the query in the variable that has been declared as a string:

"SELECT Contacts.[Last Name], 
        Contacts.[First Name], 
        Contacts.[Maricom E-Mail], 
        Contacts.[Project, Org, Team] 
FROM Contacts 
WHERE (((Contacts.[Project, Org, Team])=""Proj - MDM"") AND "

The variable is then used in the following request for data:

ReviewersData.Open myQuery, ReviewersConn, adOpenStatic, adLockOptimistic

Any thoughts as to how 500 characters got cut off?

Please format your queries. Don't make people scroll to read your query. — Siyual
– Siyual, Commented Jul 3, 2017 at 17:46
The max string length for a fixed length string is 2^16 = 65535 characters. The max length for string with variable length is around 2 billion characters (2^31). When I copy the string into the cell, assign it to a string variable and do Debug.Print sqlStr it gives me the whole string. Debug.Print Len(sqlStr) outputs 685 indeed. — Rik Sportel
– Rik Sportel, Commented Jul 3, 2017 at 17:59

Pierre Delecto · Accepted Answer · 2017-07-03 18:10:40Z

0

Try replacing the double quotes in your worksheet to Chr(34) before you fetch Range().Value.

answered Jul 3, 2017 at 18:10

Pierre Delecto

4513 gold badges8 silver badges28 bronze badges

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

1 Comment

Suzanne Brown Over a year ago

I did try this, thanks. String still gets cut off when reading into variable.

Suzanne Brown · Accepted Answer · 2017-07-07 20:26:49Z

0

I found out that the way I was attempting to access the variable value was causing it to appear truncated. I was right-clicking on the variable value in the "locals" frame of the VB IDE, selecting copy, then pasting what was in the clipboard into notepad. It was there that the string was truncated. When I did a msgbox of the text, it was correct.

answered Jul 7, 2017 at 20:26

Suzanne Brown

13 bronze badges

Collectives™ on Stack Overflow

excel vba read SQL SELECT statement in cell

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related