Instruction needed on how to have MS Query pull the SQL statement from a sheet in Excel?

Does anyone have any instruction on how to have MS Query pull the SQL statement from a sheet in Excel?  my query is structured as
1st select on transactions from table1 linked to demographics from table2 and table3 where CUSTOMER#=ABC
union all from table2 where CUSTOMER#=ABC
My problem is I want to create a parameter that is pulled from my excel sheet for Customer# and it won't allow me to place the ? in both places where the Customer#= is in my SQL.
I can put the ? in the first statement but when I put it in the second (union all) I receive
[Microsoft][ODBC SQL Server Driver]unable to derive parameter information when parameter marker is a function argument then [Microsoft][ODBC SQL Server Driver] invlald Descriptor Index

My query runs smoothly takes about 20 seconds to return 4500 lines of data, but it is too combersom for the user to edit the SQL each time the customer# has to be changed.  

I am using Excel 2007.  The database I am quering from is at a thrid party site so I cannot create a view.
kgittingerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

joarvCommented:
Hi,
I also run in to about the same problem when I try to add parameters to a "complex" sql query.
My solution is to build the sql statment in the excelfile and then set the commandtext for the query via vba


Sub update()
    Dim w As Workbook
    Dim sh As Worksheet
    Dim q As QueryTable
    Dim sSql As String
    
    Set w = ActiveWorkbook
    Set sh = w.Worksheets("sql") 'the sheet containing querytable
    Set q = sh.QueryTables("querytablename") 'the name of the querytable
    
    sSql = getSql           'collects sql statment
    q.CommandText = sSql    'set the commandtext for querytable
    q.Refresh               'updates querytable
    

End Sub

Private Function getSql()
    Dim w As Workbook
    Dim sh As Worksheet
    Dim rSql As Range
    Dim r As Range
    Dim sSql As String
    
    'Set w = ActiveWorkbook
    'Set sh = w.Worksheets("sql") 'the sheet containing sql statement
    Set rSql = Application.Names("sqlcommand").RefersToRange
    
    'builing sql statment
    For Each r In rSql
        sSql = sSql & r
    Next r
    
    getSql = sSql

End Function

Open in new window

Ms-query.xls

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kgittingerAuthor Commented:
Thanks so much. I'll try to incorporate from here A good starting point!  
kgittingerAuthor Commented:
I am finall getting back to this... Can you help me apply this to a union query?  I can't seem to figure it out.  
I would like to be able to populate the Between values on the spreadsheet
 (1st Select Statement) WHERE (((Trans_POS.SettleDate) Between '20100408' And '20100414'))
(2nd Select Statement) WHERE (((Trans_Man.ReImbDate) Between '20100408' And '20100414'))
Do you need to see my SQL to help me out?

Thank you very much
joarvCommented:
Hello,
I think its best to open another post in this forum for this.
And yes to see the sql statement would help figuring it out.

If you post the link to your new question in this thread it's easy  for me to find it.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.