CommandText with string variable
 

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

CommandText with string variable
 

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




"Doug Howell" wrote in message
...
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

CommandText with string variable
 

On Nov 3, 9:08*am, "Nigel" wrote:
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


"Doug Howell" wrote in message

...



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!