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" <[email protected]> wrote in message
news:[email protected]...
> 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.

[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" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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,
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" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> >
> >
> >

[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.
"NickHK" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" wrote:
> >
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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.
> > >
> > >
> > >
>
>
>