Query by Form - "Query is too complex" error

[Roham]

I have a query by form that accesses 7 fields with wildcard search capability. It was working a while ago and after some use, it now gives the above error. I'm pretty sure that the guy that was using it did not change the design. The only other thing that was done was another person exported it to Excel. I'm not sure which is the more likely culprit.

Thanks,
Mike

 

[Rolliee]

YOu need a means of looking at the "too complex" query. I have, at times, written a text file with the values I wonder about into the text file.

example.


private sub TellMe(SQLstr as string)

open "\MyFolder\TellMe.txt" for output as #1

print #1, "My Helps " & space(23) & now()
print #1, "The SQL was ";
print #1, SQLstr

close #1

exit sub

Then go and look at the file that was written. If you have multiple users, combine the user's name with the file name of the written file.

Rollie E

 

[Roham]

What do I associate that code with? I'm a novice when it comes to the VB side. I tried associating it with the Search button to no effect. I modified the path for the text file, but wasn't sure what else needed to be modified.

Mike

 

[Rolliee]

Place it just before the event that is causing the problem. What causes the problem event???

Rollie E

 

[Roham]

I am using a form with a command button that calls the query. The query is where the problem is. I noticed this because it gives me the same error when I try to directly open the query as well. I'm not sure what I can do when I get into the design mode for the query though. It's a large matrix of Like "*" & [Forms]![QBF_Search]![PART#] & "*" and Is Null statements. I didn't type all of that in originally, I just did the Like statements with an OR in between the Is Null statements. Access then populated the query with it's matrix. I hope this makes a modicum of sense.

Mike

 

[BobJacksonNCI]

Mike,

Does the query reference more than one table?
If so, it sounds to me like a relationship between tables has been lost.

Also, it would be helpful if you brought up the query in design view, then select view, then SQL view. Copy the contents of the resulting window and paste it here. That would allow folks here to get a clearer picture...

 

[Roham]

Thanks Bob, I didn't remember how to get to the SQL view. It's a big tangled mess. I'd assume that there is a more streamlined way to do it. As I said, I typed in the 7 individual statements, and it gave me the the mess below.
It is too big to post, so I have linked it. I'm guessing that you all won't have to read through it, you'll be able to tell at a glance that there's a better way to do it.

http://www.oceannetwork.com/roham/database.htm

Thanks,
Mike

 

[jfgambit]

Roham:

Take a look at the following two threads:

thread702-563930 provide a link to download a sample search db that will help you eliminate all the queirs that you have created and thereby removing the "Query to Complex" error.



"I know what you're t'inkin', ma petite. Dat Gambit... still de suave one, no?"

 

[BobJacksonNCI]

Mike,

I won't be able to look at it today. I may not even be able to access your site due to my company's control on internet sites I can visit. But, you can paste the SQL here:
Open the query in design view.
Select View on the toolbar at the top.
Then select SQL view from the drop down list.
Copy the contents of the SQL window and paste here.
Then, someone else may be able to assist immediately.

BTW, did you check on relationships?
If a relationship between tables used in the query is accidently deleted, then the query will try to match ALL rows of one table to ALL rows of the other - I've seen this happen and result in the message you're seeing.

Good luck!
Bob

 

[Roham]

Thanks for your reply Bob. I only have one table in this database at the current time, but thanks for your suggestion. I can't paste the SQL because it's too big, haha. The page keeps coming back bad, well you live and you learn I guess. It is just a phenomenally long string of SQL code that Access put together for me.

I think I will check out jfgambit's sample database, many people seem to have had success with it. I obviously need to learn more about the Query by Form technique. Simply implementing the solution from Microsoft's database obviously isn't good enough.

 

[BobJacksonNCI]

Mike,

I was able to access the link you posted.

Uh, "phenomenally long string of SQL code that Access put together for me" is EXACTLY CORRECT!

You must need this to work or you wouldn't have posted.

If you saved a copy before you modified it, you should test the "before" copy to verify it works.

Hopefully the above is true.

When you modified and Access accepted your changes, that confirmed your changes are syntactically correct. It did not confirm they are logically correct. So, you need to go back to your starting point and try again. I expect you tested the first time, but probably did not test all possible combinations.

Good luck!
Bob

 

[Roham]

Well I tested each field in my form to make sure that it was doing wildcard searching. It seemed to all work fine. The Microsoft article that I used instructed me to place each statement with an OR and then an IsNull statement in each field in my form. As a result, it constructed that monstrosity. I first tested it with the sample Northwind database with flying colors. I noticed that I had to do all of the statements in one sitting, otherwise it would populate it and then I didn't know where to place additional statements.

It makes me realize that I'm a novice and that I need to learn SQL and VB. I knew that there had to be a better way than populating a humongous query to cross-reference any input scenario. Thanks to all who helped me, I'll have to do some reading to figure out how jgambit's code works in his sample database.

 

[jfgambit]

Roham:

The database sample uses VB code to pass the SQL string to a listbox or QueryDef. It allows for wildcard searches, exact match searches, date and time searches, etc.

To view the code simply open the form in design mode and click the code button on the ttolbar. I have included descriptions in the code to help you better understand how it works.

If you need any help, let me know and I will give you some more direction, or have you send me a sample of you database and see if I can incorportate the search form into the database.



"I know what you're t'inkin', ma petite. Dat Gambit... still de suave one, no?"

 

[Roham]

Well I feel kinda funny asking too many questions when I don't have a complete understanding of how the different aspects of Access work. I'm learning as I go along. This database is one that the parts guy asked me to create for him. I didn't anticipating it breaking of course. Haha. I'll post here to give my status once I get a chance to try out the sample database.