query wizard error

Hi! I need help with a query error. I have an error that comes up everytime i try to edit a query using the query wizard. It says "This query cannot be edited by the Query Wizard". I have rebuilt the actual query using SQL, and deleting the sheet and rebuilding the query in the wizard, and each time I go back to edit it
the error comes up again, for an unknown reason. If i don't specify any limitations it runs, but if i specify I only want certain data between 2 dates, it brings up the data requested, but if i go back to edit that query in the wizard it won't let me! I need help so my end users can use this report!
Also, i have been told to do this:
On the 'Data' Menu, select "Get External Data..." and "New Database Query"
On the dialog that comes up, find the checkbox for "Use the Query Wizard to
create/edit queries" at the bottom and uncheck it.
Exit this dialog by hitting "Cancel".

I cannot do this though because my end users do not know how to use microsoft query, they have to use the wizard to switch the criteria on their reports!
Thanks!!

Christen

In the Wizard, you specify something like

StartDate some date
EndDate some date

but in the actual CommandText (the SQL statement), and even if you view the
query in MSQuery, it shows as ONE criterion, namely "(StartDatesomedate)
And (EndDatesomedate)". MSQuery can't handle And's and Or's in criteria
expressions.

So that's what is happening, here's how I would fix it. Use parameters to
allow the users to enter the dates. See here

http://www.dicks-clicks.com/excel/Ex...htm#Parameters

That way you'll never have to edit the query. Post back if you need more
help.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Christen" wrote in message
...
Hi! I need help with a query error. I have an error that comes up
everytime i try to edit a query using the query wizard. It says "This query
cannot be edited by the Query Wizard". I have rebuilt the actual query
using SQL, and deleting the sheet and rebuilding the query in the wizard,
and each time I go back to edit it
the error comes up again, for an unknown reason. If i don't specify any
limitations it runs, but if i specify I only want certain data between 2
dates, it brings up the data requested, but if i go back to edit that query
in the wizard it won't let me! I need help so my end users can use this
report!
Also, i have been told to do this:
On the 'Data' Menu, select "Get External Data..." and "New Database Query"
On the dialog that comes up, find the checkbox for "Use the Query Wizard
to
create/edit queries" at the bottom and uncheck it.
Exit this dialog by hitting "Cancel".

I cannot do this though because my end users do not know how to use
microsoft query, they have to use the wizard to switch the criteria on their
reports!
Thanks!!

When I went to your website, I followed the directions on making a parameter. I would enter as the parameter "Please enter a start date", when i would click off of that the prompt box would pop up. Then i would enter in the date that I wanted to start with and click OK. Once I would hit ok, a box popped up saying "Function Sequence Error", I would click on OK on that box, and it would wipe out all the windows on Microsoft Query. Now i don't know how to fix it. I also need to have them put in an end date as well, so how do i put two parameters for one field? Reply back to this post ASAP. Thanks so much for your help!!
Christen

"Dick Kusleika" wrote:

Christen

In the Wizard, you specify something like

StartDate some date
EndDate some date

but in the actual CommandText (the SQL statement), and even if you view the
query in MSQuery, it shows as ONE criterion, namely "(StartDatesomedate)
And (EndDatesomedate)". MSQuery can't handle And's and Or's in criteria
expressions.

So that's what is happening, here's how I would fix it. Use parameters to
allow the users to enter the dates. See here

http://www.dicks-clicks.com/excel/Ex...htm#Parameters

That way you'll never have to edit the query. Post back if you need more
help.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Christen" wrote in message
...
Hi! I need help with a query error. I have an error that comes up
everytime i try to edit a query using the query wizard. It says "This query
cannot be edited by the Query Wizard". I have rebuilt the actual query
using SQL, and deleting the sheet and rebuilding the query in the wizard,
and each time I go back to edit it
the error comes up again, for an unknown reason. If i don't specify any
limitations it runs, but if i specify I only want certain data between 2
dates, it brings up the data requested, but if i go back to edit that query
in the wizard it won't let me! I need help so my end users can use this
report!
Also, i have been told to do this:
On the 'Data' Menu, select "Get External Data..." and "New Database Query"
On the dialog that comes up, find the checkbox for "Use the Query Wizard
to
create/edit queries" at the bottom and uncheck it.
Exit this dialog by hitting "Cancel".

I cannot do this though because my end users do not know how to use
microsoft query, they have to use the wizard to switch the criteria on their
reports!
Thanks!!

Christen

I've never seen that error before. I'll just bet that it has something to
do with the prompt field being a date (just a guess though). To get back to
where you were before you followed my advice, we need to look at the SQL
string. Open the VBE from Excel (Alt-F11) and show the Immediate Window if
it's not already (Ctl-G). In the immediate window, type

?Sheet1.QueryTables(1).CommandText

replacing Sheet1 with your sheets codename and QueryTables(1) with the
number of the querytable on that sheet that is at issue. In that
CommandText, you'll have a clause that looks like

....WHERE (DateField ?) ...

The question mark tells MSQuery that it's a parameter and that's what we
need to change. You can delete the entire WHERE clause (or just the part of
it that has the question mark if you have other criteria). I would do it
like this: In the Immediate Window, type a statement like this

Sheet1.QueryTables(1).CommandText =
Replace(Sheet1.QueryTables(1).CommandText,"WHERE (DateField ?)","")
Sheet1.QueryTables(1).Refresh

That should get you back to even. If you have trouble with any of that, put
the CommandText in a reply to this post and I can walk you through it with
more specifics.

Now to fix the problem. If I'm right and the date format is the culprit,
then you need to determine what you need to type in the prompt box to get
MSQuery to accept it. What is the database that you are querying, Access,
SQLServer, etc.? If you put the criteria in MSQuery manually, how do enter
it? Like this?

#6/1/2004#

or like this

'2004-06-01'

or some other way. That will be the most telling clue to what is really in
that "date" field.

Once we get that worked out, you can use two parameters in one field like
this

[Enter Start Date] And [Enter End Date]

Two bracket enclosed prompts in the same criterion will do it.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Christen" wrote in message
...
When I went to your website, I followed the directions on making a
parameter. I would enter as the parameter "Please enter a start date", when
i would click off of that the prompt box would pop up. Then i would enter
in the date that I wanted to start with and click OK. Once I would hit ok,
a box popped up saying "Function Sequence Error", I would click on OK on
that box, and it would wipe out all the windows on Microsoft Query. Now i
don't know how to fix it. I also need to have them put in an end date as
well, so how do i put two parameters for one field? Reply back to this post
ASAP. Thanks so much for your help!!
Christen

"Dick Kusleika" wrote:

Christen

In the Wizard, you specify something like

StartDate some date
EndDate some date

but in the actual CommandText (the SQL statement), and even if you view
the
query in MSQuery, it shows as ONE criterion, namely
"(StartDatesomedate)
And (EndDatesomedate)". MSQuery can't handle And's and Or's in
criteria
expressions.

So that's what is happening, here's how I would fix it. Use parameters
to
allow the users to enter the dates. See here

http://www.dicks-clicks.com/excel/Ex...htm#Parameters

That way you'll never have to edit the query. Post back if you need
more
help.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Christen" wrote in message
...
Hi! I need help with a query error. I have an error that comes up
everytime i try to edit a query using the query wizard. It says "This
query
cannot be edited by the Query Wizard". I have rebuilt the actual query
using SQL, and deleting the sheet and rebuilding the query in the
wizard,
and each time I go back to edit it
the error comes up again, for an unknown reason. If i don't specify
any
limitations it runs, but if i specify I only want certain data between 2
dates, it brings up the data requested, but if i go back to edit that
query
in the wizard it won't let me! I need help so my end users can use this
report!
Also, i have been told to do this:
On the 'Data' Menu, select "Get External Data..." and "New Database
Query"
On the dialog that comes up, find the checkbox for "Use the Query
Wizard
to
create/edit queries" at the bottom and uncheck it.
Exit this dialog by hitting "Cancel".

I cannot do this though because my end users do not know how to use
microsoft query, they have to use the wizard to switch the criteria on
their
reports!
Thanks!!

I didn't do anything that you said in the first part of your post because i just reopened my file and my query was still there. I am querying an Approach database, (i know it sucks!!) and the way the dates are formatted in the table are 2004-06-01. The date field is actually named "Press Date". I tried to put in the two parameters in one field, and typed in the date in the prompt box like above, and i still got that old error. I still need help, sorry!?! Thanks so much for helping tho, i know we'll get this eventually!!
Christen

p.s. this is my SQL code that is generated by the wizard to define my query. I don't know if this will help you, but maybe it will help you understand what the users are trying to query.
Thanks, Christen

"Dick Kusleika" wrote:

Christen

I've never seen that error before. I'll just bet that it has something to
do with the prompt field being a date (just a guess though). To get back to
where you were before you followed my advice, we need to look at the SQL
string. Open the VBE from Excel (Alt-F11) and show the Immediate Window if
it's not already (Ctl-G). In the immediate window, type

?Sheet1.QueryTables(1).CommandText

replacing Sheet1 with your sheets codename and QueryTables(1) with the
number of the querytable on that sheet that is at issue. In that
CommandText, you'll have a clause that looks like

....WHERE (DateField ?) ...

The question mark tells MSQuery that it's a parameter and that's what we
need to change. You can delete the entire WHERE clause (or just the part of
it that has the question mark if you have other criteria). I would do it
like this: In the Immediate Window, type a statement like this

Sheet1.QueryTables(1).CommandText =
Replace(Sheet1.QueryTables(1).CommandText,"WHERE (DateField ?)","")
Sheet1.QueryTables(1).Refresh

That should get you back to even. If you have trouble with any of that, put
the CommandText in a reply to this post and I can walk you through it with
more specifics.

Now to fix the problem. If I'm right and the date format is the culprit,
then you need to determine what you need to type in the prompt box to get
MSQuery to accept it. What is the database that you are querying, Access,
SQLServer, etc.? If you put the criteria in MSQuery manually, how do enter
it? Like this?

#6/1/2004#

or like this

'2004-06-01'

or some other way. That will be the most telling clue to what is really in
that "date" field.

Once we get that worked out, you can use two parameters in one field like
this

[Enter Start Date] And [Enter End Date]

Two bracket enclosed prompts in the same criterion will do it.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Christen" wrote in message
...
When I went to your website, I followed the directions on making a
parameter. I would enter as the parameter "Please enter a start date", when
i would click off of that the prompt box would pop up. Then i would enter
in the date that I wanted to start with and click OK. Once I would hit ok,
a box popped up saying "Function Sequence Error", I would click on OK on
that box, and it would wipe out all the windows on Microsoft Query. Now i
don't know how to fix it. I also need to have them put in an end date as
well, so how do i put two parameters for one field? Reply back to this post
ASAP. Thanks so much for your help!!
Christen

"Dick Kusleika" wrote:

Christen

In the Wizard, you specify something like

StartDate some date
EndDate some date

but in the actual CommandText (the SQL statement), and even if you view
the
query in MSQuery, it shows as ONE criterion, namely
"(StartDatesomedate)
And (EndDatesomedate)". MSQuery can't handle And's and Or's in
criteria
expressions.

So that's what is happening, here's how I would fix it. Use parameters
to
allow the users to enter the dates. See here

http://www.dicks-clicks.com/excel/Ex...htm#Parameters

That way you'll never have to edit the query. Post back if you need
more
help.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Christen" wrote in message
...
Hi! I need help with a query error. I have an error that comes up
everytime i try to edit a query using the query wizard. It says "This
query
cannot be edited by the Query Wizard". I have rebuilt the actual query
using SQL, and deleting the sheet and rebuilding the query in the
wizard,
and each time I go back to edit it
the error comes up again, for an unknown reason. If i don't specify
any
limitations it runs, but if i specify I only want certain data between 2
dates, it brings up the data requested, but if i go back to edit that
query
in the wizard it won't let me! I need help so my end users can use this
report!
Also, i have been told to do this:
On the 'Data' Menu, select "Get External Data..." and "New Database
Query"
On the dialog that comes up, find the checkbox for "Use the Query
Wizard
to
create/edit queries" at the bottom and uncheck it.
Exit this dialog by hitting "Cancel".

I cannot do this though because my end users do not know how to use
microsoft query, they have to use the wizard to switch the criteria on
their
reports!
Thanks!!

Christen


"Christen" wrote in message
...
p.s. this is my SQL code that is generated by the wizard to define my
query. I don't know if this will help you, but maybe it will help you
understand what the users are trying to query.

Did you forget to post it, or am I missing something?

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

wow im losing it, here it is!

SELECT PAPER.SEASON, PAPER."STORE CODE", PAPER.EVENT, PAPER."MFG MTH", PAPER."PO NUMBER", PAPER."PRESS DATE", PAPER.PRINTER, PAPER."BRAND PREFERENCE", PAPER."GRADE NBR"
FROM CPP.PAPER PAPER

thanks!

"Dick Kusleika" wrote:

Christen


"Christen" wrote in message
...
p.s. this is my SQL code that is generated by the wizard to define my
query. I don't know if this will help you, but maybe it will help you
understand what the users are trying to query.

Did you forget to post it, or am I missing something?

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

did you see anything wrong with the SQL, or maybe im typing the parameters wrong??
Christen

"Dick Kusleika" wrote:

Christen


"Christen" wrote in message
...
p.s. this is my SQL code that is generated by the wizard to define my
query. I don't know if this will help you, but maybe it will help you
understand what the users are trying to query.

Did you forget to post it, or am I missing something?

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

Christen

I don't see any criteria in your SQL. Can you hard code a criterion in
there (or two) and post that SQL?


--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Christen" wrote in message
...
did you see anything wrong with the SQL, or maybe im typing the parameters
wrong??
Christen

"Dick Kusleika" wrote:

Christen


"Christen" wrote in message
...
p.s. this is my SQL code that is generated by the wizard to define my
query. I don't know if this will help you, but maybe it will help you
understand what the users are trying to query.

Did you forget to post it, or am I missing something?

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com