query wizard error

[Christen]

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!!

 

[Dick Kusleika]

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 "(StartDate>somedate)
And (EndDate<somedate)". 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/ExternalData6.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

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!

 

[Dick Kusleika]

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

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]

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

 

[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

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

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]

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.

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

 

[Christen]

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]

Christen

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

 

[Christen]

Here is the SQL with the parameters, those dates change cause it is a weekly report.
Thanks!
Christen
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
WHERE (PAPER."PRESS DATE">={d '2004-06-01'} And PAPER."PRESS DATE"<={d '2004-06-15'})
ORDER BY PAPER."PRESS DATE"

 

[Christen]

Here is the SQL with the parameters, those dates change cause it is a weekly
report.
Thanks!
Christen
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
WHERE (PAPER."PRESS DATE">={d '2004-06-01'} And PAPER."PRESS DATE"<={d
'2004-06-15'})
ORDER BY PAPER."PRESS DATE"

Also there are other reports of ours that give the same error, but we don't
know why this error is even occuring? Do you know why this error even comes
up? That would be helpful to know!

 

[Dick Kusleika]

Christen

Now that you know what format the dates are in, you can build a string for
you sql and put it into the query. Assume your users will put the start
date in A1 and the end date in A2 (they don't have to be those or any cells,
you could use an Inputbox or Userform to get the dates. Then you might have
code like this to update the querytable.

Sub UpdateQT()

Dim sSQL As String
Dim sNewDate1 As String
Dim sNewDate2 As String

sSQL = "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 " & _
"WHERE (PAPER.'PRESS DATE'>={d '2004-06-01'} And " & _
"PAPER.'PRESS DATE'<={d '2004-06-15'}) " & _
"ORDER BY PAPER.'PRESS DATE'"

sNewDate1 = "{d '" & Format(Range("a1").Value, "yyyy-mm-dd") & "'}"
sNewDate2 = "{d '" & Format(Range("a2").Value, "yyyy-mm-dd") & "'}"

sSQL = Replace(sSQL, "{d '2004-06-01'}", sNewDate1, , 1)
sSQL = Replace(sSQL, "{d '2004-06-15'}", sNewDate2, , 1)

Sheet1.QueryTables(1).CommandText = sSQL
Sheet1.QueryTables(1).Refresh False

End Sub