Excel VBA variables embedded in SQL statement synatx help needed 1

[knifey]

Hi All,
I wasn't sure if this was the right forum for this but here goes. I'm using Excel VBA to create a database cell query that connects to SQL Server and returns the result to the active cell (the result is always a single value).
The idea is that all of the WHERE clauses in the SQL are controlled by dropdown lists down column A and across the top row of maybe 20 columns of an Excel sheet. I'm a complete beginner when it comes to SQL and am not sure of the correct syntax for inserting VBA variable names into SQL.
My problem:
It just don't work! It falls over with a runetime error 13 when it tries to execute the SQL. I've pasted my SQL string below and an example of how I have dimmed and worked out my variables. But I think the problem lies with the syntax of concatenating SQL and VBA variable names (ie. a comma in the wrong place).
Any help or advice, or even a prod in the right direction would be much appreciated.
Roy

Dim measureIDv As Variant
measureIDv = Cells(iRow, iCol - iCol + 1).Value

'DON'T WORK!
mySQLString = Array("SELECT PISummary.MeasureValue" & Chr(13) & "" & Chr(10) & "FROM HiPPOi_Test.dbo.PISummary" & Chr(13) & "" & Chr(10) & "WHERE PISummary.ActiveLevel ='" & activeLevelv & "'" & Chr(13) & "" & Chr(10) & "AND PISummary.MeasureID ='" & measureIDv & "'" & Chr(13) & "" & Chr(10) & "AND PISummary.MeasureDesc ='" & measureDescv & "'" & Chr(13) & "" & Chr(10) & "AND PISummary.YTDMonthFlag ='" & YTDMonthFlagv & "'" & Chr(13) & "" & Chr(10) & "AND PISummary.MonthText ='" & monthTextv & "'")

 

[SkipVought]

You may have not provided enough of your code, but here goes...

sSQL = "SELECT MeasureValue " 
sSQL = sSQL & "FROM HiPPOi_Test.dbo.PISummary " 
sSQL = sSQL & "WHERE ActiveLevel ='" & activeLevelv & "'" 
sSQL = sSQL & "  AND MeasureID ='" & measureIDv & "'" 
sSQL = sSQL & "  AND MeasureDesc ='" & measureDescv & "'"
sSQL = sSQL & "  AND YTDMonthFlag ='" & YTDMonthFlagv & "'" 
sSQL = sSQL & "  AND MonthText ='" & monthTextv & "'"

I shortened the SQL variable to make thing fit better.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SoCalAccessPro]

Skip,

I do that all the time in lieu of the darn line wraps and underscores _

Kinefy, if any of those variables are numbers, do not use the comma before and after the variable. (')

Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[knifey]

Hi Skip/Tyrone,
Thanks for the advice. I'll try this first thing Monday.
Cheers,
Roy

 

[SkipVought]

There is no COMMA.

There is, however SINGLE QUOTE marks to delimit TEXT value. If your have NUMERIC value,you don't need the SINGLE QUOTE. If your have DATES, you need # delimiters to convert your TEXT to DATE.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[knifey]

Hi Skip,
Thanks for all the info. 'A missing comma' is just my way of saying bad syntax. I did mean the apostrophe or double quotes.
Your sSQL syntax worked great and I'm the new hero in the department! But the code had a side effect that I wasn't expecting.
When I change a dropdown list (WHEN clause variable) in the Excel sheet and try to refresh all queries they still have the same values as before and the SQL statement looks hard coded in MSQuery.
Do I need to alter my code to something like:
With ActiveSheet.QueryTables.Refresh(etc.
Then attach this code to my own refresh button (so it will refresh using my variables)?
Thanks,
Knifey

 

[SkipVought]

In the VB Editor, run this...

Sub GetQuery()
  with Sheets{"YourSheetNname").Querytables(1)
    debug.print .connection
    debug.print .commandtext
  end with  
End Sub

Copy the text in the Immediate Window and post back with the text.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[knifey]

Hi Skip,
Heres what the immediate windows said:
ODBC;DSN=HiPPOi;Description=UCLH Information;UID=RGRIFFI1;APP=Microsoft Office 2003;WSID=PCLCMG15026;Trusted_Connection=Yes
SELECT MeasureValue FROM HiPPOi_Test.dbo.PISummary WHERE ActiveLevel ='All Services' AND MeasureID ='79' AND MeasureDesc ='A&E attendances - Denominator' AND YTDMonthFlag ='Month' AND MonthText ='Apr 08'

But I don't think it matters anymore as I'm finishing this contract in just over a week. Which means I probably only have time to add my own refresh button (after changing my existing code slightly).

Thanks for all the help,
Roy