A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old January 28th, 2005, 04:49 PM
Scott
external usenet poster
 
Posts: n/a
Default "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  
Old January 28th, 2005, 06:19 PM
[MVP] S.Clark
external usenet poster
 
Posts: n/a
Default

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  
Old January 28th, 2005, 06:31 PM
Scott
external usenet poster
 
Posts: n/a
Default

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  
Old January 29th, 2005, 02:29 PM
Chris2
external usenet poster
 
Posts: n/a
Default


"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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 02:05 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.