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 |
#1
|
|||
|
|||
"Query is too complex" - limit to number of parameters in query?
I have a form with eight (8) text boxes whose values are passed to a query.
The query ran fine a few times but now when I try to run it, I get a message box that say "Query is too complex". The query criteria for each field is Like "*" & [Forms]![frmQuery]![Proj_Number] & "*" Or [Forms]![frmQuery]![Proj_Number] Is Null Any I approaching this wrong? Is there a better way to collect paramters from a form and then pass it to a query? The user will probably only enter two or three (of the eight possible) pieces of information to perform the search. Thanks, Scott |
#2
|
|||
|
|||
I prefer to create an inline SQL statement in code.
-- Steve Clark, Access MVP FMS, Inc. www.fmsinc.com/consulting "Scott" wrote in message ... I have a form with eight (8) text boxes whose values are passed to a query. The query ran fine a few times but now when I try to run it, I get a message box that say "Query is too complex". The query criteria for each field is Like "*" & [Forms]![frmQuery]![Proj_Number] & "*" Or [Forms]![frmQuery]![Proj_Number] Is Null Any I approaching this wrong? Is there a better way to collect paramters from a form and then pass it to a query? The user will probably only enter two or three (of the eight possible) pieces of information to perform the search. Thanks, Scott |
#3
|
|||
|
|||
what does that mean? How do create "an inline SQL statement in code"?
Thanks, Scott "[MVP] S.Clark" wrote: I prefer to create an inline SQL statement in code. -- Steve Clark, Access MVP FMS, Inc. www.fmsinc.com/consulting "Scott" wrote in message ... I have a form with eight (8) text boxes whose values are passed to a query. The query ran fine a few times but now when I try to run it, I get a message box that say "Query is too complex". The query criteria for each field is Like "*" & [Forms]![frmQuery]![Proj_Number] & "*" Or [Forms]![frmQuery]![Proj_Number] Is Null Any I approaching this wrong? Is there a better way to collect paramters from a form and then pass it to a query? The user will probably only enter two or three (of the eight possible) pieces of information to perform the search. Thanks, Scott |
#4
|
|||
|
|||
"Scott" wrote in message ... what does that mean? How do create "an inline SQL statement in code"? Thanks, Scott "[MVP] S.Clark" wrote: I prefer to create an inline SQL statement in code. I believe he means that he would assemble the query in a String, using Visual Basic for Applications (VBA) computer language programming code. Example (Northwind.mdb): ----------------------------------------------------------- Public Sub InsertNewShipper() Dim db As DAO.Database Dim strSQL As String Set db = CurrentDb() strSQL = "INSERT INTO Shippers ([CompanyName], [Phone])" strSQL = strSQL & "VALUES (""Airborne Parcel Service"", ""(800) 555-0000"");" db.Execute strSQL, dbFailOnError db.Close Set db = Nothing End Sub ----------------------------------------------------------- Note how the SQL statement gradually assembled, one line at a time, via the & concatenation operator. You may add variables (to get changing/dynamic data into the SQL string) to this by contatenating them into the string, wherever they are needed. 1) Go to the menus: Tools Macro Visual Basic Editor. 2) Under "Project - Northwind" (left hand upper pane), expand the Modules folder. 3) Right-click the Modules folder, and go to Insert Module. 4) Change the name of the new module to TestCode. 5) Paste the above Sub into the blank TestCode module. 6) cntl-s to save. 7) Go to the menus: Run Run Sub/UserForm. 8) Open the Shippers table in Datasheet View to see if the row was added in. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
complex number | alyot | Using Forms | 3 | November 6th, 2004 10:28 PM |
RANKING | gambler | Running & Setting Up Queries | 22 | November 5th, 2004 12:53 AM |
Union Query Not Returning A Value | Jeff G | Running & Setting Up Queries | 2 | October 19th, 2004 05:47 PM |
query a number stored as text | Lee | Running & Setting Up Queries | 19 | October 13th, 2004 04:10 AM |
Big number gives error! | Sara Mellen | Running & Setting Up Queries | 8 | October 11th, 2004 02:48 AM |