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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
what does that mean? How do create "an inline SQL statement in code"?

Thanks,
Scott

[MVP] S.Clark said:
I prefer to create an inline SQL statement in code.

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

Scott said:
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
Click to expand...
Click to expand...
 
Scott said:
what does that mean? How do create "an inline SQL statement in code"?

Thanks,
Scott
Click to expand...


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.
 
Back
Top