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
(E-Mail Removed)



"Doug Howell" <(E-Mail Removed)> wrote in message
news:98d9b8d3-ba7a-4ffd-83f3-(E-Mail 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

On Nov 3, 9:08*am, "Nigel" <ni...@no9swspam.co.uk> 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
> nigelnos...@9sw.co.uk
>
> "Doug Howell" <douglasehow...@hotmail.com> wrote in message
>
> news:98d9b8d3-ba7a-4ffd-83f3-(E-Mail 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!