We help IT Professionals succeed at work.

MS Access Query Question

mboulton
mboulton asked
on
Medium Priority
270 Views
Last Modified: 2008-03-17
I'm using MSAccess vie OLEDB from a web page so the query comes from an ASP page, the way i'm doing it is trying every possibility in an OR statement, but i get an error saying Query too Complex from OLE DB.

I have 3 tables with 5 fields (see below)
I want to be able to create a search for any combination of 3 of the 5 fields in any order, meaning
If i have searchstring1, searchstring2 & searchstring3, i want to be able to find them in any order in the database. They could be in any of the fields. For instance searchstring1 could be in Table2 Field3 while searchstring2 could be in Table1 Field2.


Table1

Field1
Field2

Table2

Field3
Field4

Table3

Field5

I hope this makes sense.
Let me know.
Comment
Watch Question

Commented:
please post your query

Author

Commented:
My query is way too long,
it's basically
Select Field1, Field2, Field3, Field4, Field5 from AllTables where
Field1 like 'searchstring1' and field2 like 'searchsting2' or
Field1 like 'searchstring2' and field2 like 'searchsting1' or
Field1 like 'searchstring1' and field2 like 'searchsting3

etc.. but it's ridiculously long because of all of the possibilities, and i guess that's why it failed in the first place, it was too long.

Commented:
try to enclose the combinations in brackets like this:

Select Field1, Field2, Field3, Field4, Field5 from AllTables where
(Field1 like 'searchstring1' and field2 like 'searchsting2')  OR
(Field1 like 'searchstring2' and field2 like 'searchsting1')  OR
(Field1 like 'searchstring1' and field2 like 'searchsting3)

Author

Commented:
it's not actually the select statement that's wrong, it's that with 3 search strings in five fields there are about 60 different combinations possible, and i want to figure a way to do it without writing out each OR string.
CERTIFIED EXPERT

Commented:
Is it possible that you have a design flaw in the tables?  Not knowing what they represent makes it harder for us to really give a good answer.  Perhaps there is a better way of design that would allow easier search queries.

Just a thought....

Author

Commented:
True, i may have, but since they're already populated with about 8000 records and there are pages tied to them, i'd rather not change them if possible.

Commented:
You can try something like this:

Select Field1, Field2, Field3, Field4, Field5
from AllTables
Where  CHARINDEX('searchstring1', Field1 + Field2 + Field3 + Field4 + Field5) > 0
OR CHARINDEX('searchstring2', Field1 + Field2 + Field3 + Field4 + Field5) > 0
OR CHARINDEX('searchstring3', Field1 + Field2 + Field3 + Field4 + Field5) > 0

Author

Commented:
I like this idea, will this work in MS Access though? I know it works on MS Sql, but i didn't think it would work for Access
Commented:
I am sorry, I thought of SQL Server...

Use INSTR instead of CHARINDEX in Access...

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.