Microsoft query won't allow parameters

[kmoyer]

I have a sql union query that works just fine but I want to let the user
choose a date in an excel field. When I input my sql code into microsoft
query I get the error:

'Parameters are not allowed in queries that can't be graphically displayed.'

I am looking for a solution to the above error.

 

[NickHK]

kmoyer,
Build the SQL yourself and generate your own querytable or recordset
e.g.
Startdate=Inputbox("Enter date")
SQL="SELECT * FROM MyTable1 WHERE SDate=#" & Startdate & "#"
SQL=SQL & " UNION "
SQL=SQL & "SELECT * FROM MyTable2 WHERE SDate=#" & Startdate & "#"
RS.Execute SQL

NickHK

 

[kmoyer]

I am a little new at the microsoft query thing and maybe a little confused
with your solution as I still get the same error message but I may be doing
something wrong. Below is my query. When I use a fixed date like below I do
not get the error message but when I replace it with a ? microsoft query
yells at me.

SELECT t1.seg1_code, ABS(Sum(t1.current_balance))
FROM CCCI002.dbo.glbal t1, CCCI002.dbo.glchart t2
WHERE t1.account_code = t2.account_code AND (t1.balance_date=732311) AND
((t1.seg3_code Between 30000400 And 30000499)OR (t1.seg3_code Between
40000400 And 40000439)
OR (t1.seg3_code Between 50000400 And 50000439)
)
GROUP BY t1.seg1_code


UNION


SELECT t1.seg1_code, ABS(Sum(t1.current_balance))
FROM ccmidwst.dbo.glbal t1, ccmidwst.dbo.glchart t2
WHERE t1.account_code = t2.account_code AND (t1.balance_date=732311)and
((t1.seg3_code Between 30000400 And 30000499) OR (t1.seg3_code
Between 40000400 And 40000439) OR (t1.seg3_code Between 50000400 And
50000439))
GROUP BY t1.seg1_code
order by t1.seg1_code

Thanks
Kelly

 

[NickHK]

kmoyer,
I mean use QueryTables.Add
The arguments are Connection, Destination As Range, [Sql].
So if your SQL is fully formed before you issue this, there should not be
any problem.
Looks like you would have do some conversion of the date value from Excel to
the that expected by your DB also.

NickHK

 

[kmoyer]

I am still trying. I now get a syntax error. I have something incorrect. I
will get this I know I will. Thanks for your help.

kmoyer,
I mean use QueryTables.Add
The arguments are Connection, Destination As Range, [Sql].
So if your SQL is fully formed before you issue this, there should not be
any problem.
Looks like you would have do some conversion of the date value from Excel to
the that expected by your DB also.

NickHK

I am a little new at the microsoft query thing and maybe a little confused
with your solution as I still get the same error message but I may be doing
something wrong. Below is my query. When I use a fixed date like below I do
not get the error message but when I replace it with a ? microsoft query
yells at me.

SELECT t1.seg1_code, ABS(Sum(t1.current_balance))
FROM CCCI002.dbo.glbal t1, CCCI002.dbo.glchart t2
WHERE t1.account_code = t2.account_code AND (t1.balance_date=732311) AND
((t1.seg3_code Between 30000400 And 30000499)OR (t1.seg3_code Between
40000400 And 40000439)
OR (t1.seg3_code Between 50000400 And 50000439)
)
GROUP BY t1.seg1_code


UNION


SELECT t1.seg1_code, ABS(Sum(t1.current_balance))
FROM ccmidwst.dbo.glbal t1, ccmidwst.dbo.glchart t2
WHERE t1.account_code = t2.account_code AND (t1.balance_date=732311)and
((t1.seg3_code Between 30000400 And 30000499) OR (t1.seg3_code
Between 40000400 And 40000439) OR (t1.seg3_code Between 50000400 And
50000439))
GROUP BY t1.seg1_code
order by t1.seg1_code

Thanks
Kelly