Thread: Complex Search Criteria

I love to start things by saying "Is there a way to", however over the years I have learned that the answer to that questions is almost always invariably yes. So I will start it this way.

Background:

I have a form that lists all my contacts. My contacts are sometimes suppliers, I have a sub-form "subfrmSupplierContacts" on my contact details form "frmContactDetails". I want to be able to search my "frmContactsList" for example for "dan", and have it return to me all the contacts who have the name dan in either first, last, or company name.(I have this allready)

However, I would like it to search my "subfrmSupplierContacts" first, last and company name also, BUT, only return to me the Single Contact Entry.

Here is what I have tried so far:

I can create a query where the one to many fields are joined, however I have 31 Supplier Contacts for "XX Company" so therefore I have 31 entries with the same Company Name all the time. I only want "xx Company" to show 1 time.

I contemplated doing an ADODB.Recordset to open that "qryContactsAllData" and have it search that and create a very complex Filter Criteria with specific numbers, however I know there has to be a better way to do this. Maybe using some combination of Totals (ie group by)

Does anyone have any suggestions as to how I should create a search that will search for for the info I put in a search box on all the one to many tables, but only return to me the ones in the results windows.

Thanks for any time spent trying to assist me with this,

Dan

Hi Dan,

Starting with the obvious here, but have you tried using a SELECT DISTINCT query from your result?

Cheers,

That looks very promising ConneXionLost. I don't believe I will have the time to test it this week, it will probably be monday before I can give it a shot. Thanks much, will let you know.

Someone else please give ConneXionLost some reputation, as it tells me to spread it around more. LOL, what can I say.

Wanted to post an update on this, I haven't got a chance to test it yet, but it sure looks like it will work. Might not get tested till late this week.

Thanks for your help ConneXionLost!!!
Using a your advice, and This Link.
I am able to accomplish what I need to do.

Again, many thanks.

Dan