mboulton
asked on
MS Access Query Question
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.
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.
please post your query
ASKER
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.
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.
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)
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)
ASKER
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.
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....
Just a thought....
ASKER
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.
You can try something like this:
Select Field1, Field2, Field3, Field4, Field5
from AllTables
Where CHARINDEX('searchstring1',
OR CHARINDEX('searchstring2',
OR CHARINDEX('searchstring3',
Select Field1, Field2, Field3, Field4, Field5
from AllTables
Where CHARINDEX('searchstring1',
OR CHARINDEX('searchstring2',
OR CHARINDEX('searchstring3',
ASKER
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
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.