"Query is too complex" - limit to number of parameters in query?

[Guest]

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

 

[[MVP] S.Clark]

I prefer to create an inline SQL statement in code.

 

[Guest]

what does that mean? How do create "an inline SQL statement in code"?

Thanks,
Scott

I prefer to create an inline SQL statement in code.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

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

 

[Chris2]

what does that mean? How do create "an inline SQL statement in code"?

Thanks,
Scott

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.