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

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

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!