CommandText with string variable

[Doug Howell]

I currently have a bit of VBA that sets command text:

Set batchinfo4 = _
Sheets("pressuredata").QueryTables(1)
With batchinfo4
.CommandType = xlCmdSql
.CommandText = _
"SELECT PURP3.[TIME], PURP3.[PURP3PT1], PURP3.[PURP3JPT] FROM
PURP3.dbo.PURP3 PURP3 WHERE PURP3.[TIME] BETWEEN '" & Sheets("Pre-
Batch Checklist").Range("B3").Value & " 6:00:00 AM' AND '" & Sheets
("Pre-Batch Checklist").Range("B3").Value & " 10:00:00 PM'"
End With

I have a string variable "purtank" that I want to substitute in for
anywhere you see "PURP3" above.

What's the correct syntax for doing this?

Thanks for any help.


Doug

 

[Nigel]

Since the select statement is a string you need to concatenate the text part
with variable name for example in the first part

"SELECT PURP3.[TIME],

becomes

"SELECT" & purtank & ".[TIME],

etc...

 

[Doug Howell]

Since the select statement is a string you need to concatenate the text part
with variable name for example in the first part

"SELECT PURP3.[TIME],

becomes

"SELECT" &  purtank & ".[TIME],

etc...

--

Regards,
Nigel
(e-mail address removed)

I currently have a bit of VBA that sets command text:

Set batchinfo4 = _
   Sheets("pressuredata").QueryTables(1)
With batchinfo4
   .CommandType = xlCmdSql
   .CommandText = _
       "SELECT PURP3.[TIME], PURP3.[PURP3PT1], PURP3.[PURP3JPT]FROM
PURP3.dbo.PURP3 PURP3 WHERE PURP3.[TIME] BETWEEN '" & Sheets("Pre-
Batch Checklist").Range("B3").Value & " 6:00:00 AM' AND '" & Sheets
("Pre-Batch Checklist").Range("B3").Value & " 10:00:00 PM'"
End With

I have a string variable "purtank" that I want to substitute in for
anywhere you see "PURP3" above.

What's the correct syntax for doing this?

Thanks for any help.

Doug- Hide quoted text -

- Show quoted text -

Thanks!