If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
query wizard error
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" wrote: 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!! |
#12
|
|||
|
|||
query wizard error
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" wrote: 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 |
#13
|
|||
|
|||
query wizard error
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 -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "Christen" wrote in message ... 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" wrote: 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 |
|
Thread Tools | |
Display Modes | |
|
|