Good Morning
I am struggling to work out how to add to my existing query. I have a search form so that users can search for companies in the database. There are several 'filter' options that they wish to be available.
I have managed to get the query working so that it filters a) by Company Type and b) by the companies status. I now need to also be able to filter on the companies 'Provider', 'Member' and 'SuppType'
All othese filters need to work independantly from one another so that the user can choose whether to include no filters, just 1 filter or all filters etc...
Can anyone give me any advice, would be MUCH appreciated...
I have pasted the SQL view of my query below... if anyone needs more information in order to help then please let me know
SELECT Tbl_ContactDetails.ContactRef, Tbl_Company.CompanyRef, Tbl_Company.Company, Tbl_ContactDetails.Forename, Tbl_ContactDetails.Surname, Tbl_ContactDetails.Archived, Tbl_Company.Archived, Tbl_ContactDetails.MainContact, Tbl_Company.Status, Tbl_Company.Provider, Tbl_Company.Member, Tbl_Company.SuppType
FROM Tbl_Company RIGHT JOIN Tbl_ContactDetails ON Tbl_Company.CompanyRef = Tbl_ContactDetails.CompanyRef
WHERE (((Tbl_ContactDetails.Archived)=False) AND ((Tbl_Company.Archived)=False) AND ((Tbl_ContactDetails.MainContact)=True) AND ((Tbl_Company.Status)=[Forms]![Frm_Search]![lstStatus]) AND ((Tbl_Company.CompanyType)=[Forms]![Frm_Search]![OptSearch]) AND (([Company] & " " & [Forename] & " " & [Surname]) Like "*" & [Forms]![Frm_Search]![txtSearch].[Text] & "*")) OR (((Tbl_ContactDetails.Archived)=False) AND ((Tbl_Company.Archived)=False) AND ((Tbl_ContactDetails.MainContact)=True) AND ((Tbl_Company.CompanyType)=[Forms]![Frm_Search]![OptSearch]) AND (([Company] & " " & [Forename] & " " & [Surname]) Like "*" & [Forms]![Frm_Search]![txtSearch].[Text] & "*") AND (([Forms]![Frm_Search]![lstStatus])="(All)"))
ORDER BY Tbl_Company.Company;
Many Thanks in anticipation
I am struggling to work out how to add to my existing query. I have a search form so that users can search for companies in the database. There are several 'filter' options that they wish to be available.
I have managed to get the query working so that it filters a) by Company Type and b) by the companies status. I now need to also be able to filter on the companies 'Provider', 'Member' and 'SuppType'
All othese filters need to work independantly from one another so that the user can choose whether to include no filters, just 1 filter or all filters etc...
Can anyone give me any advice, would be MUCH appreciated...
I have pasted the SQL view of my query below... if anyone needs more information in order to help then please let me know
SELECT Tbl_ContactDetails.ContactRef, Tbl_Company.CompanyRef, Tbl_Company.Company, Tbl_ContactDetails.Forename, Tbl_ContactDetails.Surname, Tbl_ContactDetails.Archived, Tbl_Company.Archived, Tbl_ContactDetails.MainContact, Tbl_Company.Status, Tbl_Company.Provider, Tbl_Company.Member, Tbl_Company.SuppType
FROM Tbl_Company RIGHT JOIN Tbl_ContactDetails ON Tbl_Company.CompanyRef = Tbl_ContactDetails.CompanyRef
WHERE (((Tbl_ContactDetails.Archived)=False) AND ((Tbl_Company.Archived)=False) AND ((Tbl_ContactDetails.MainContact)=True) AND ((Tbl_Company.Status)=[Forms]![Frm_Search]![lstStatus]) AND ((Tbl_Company.CompanyType)=[Forms]![Frm_Search]![OptSearch]) AND (([Company] & " " & [Forename] & " " & [Surname]) Like "*" & [Forms]![Frm_Search]![txtSearch].[Text] & "*")) OR (((Tbl_ContactDetails.Archived)=False) AND ((Tbl_Company.Archived)=False) AND ((Tbl_ContactDetails.MainContact)=True) AND ((Tbl_Company.CompanyType)=[Forms]![Frm_Search]![OptSearch]) AND (([Company] & " " & [Forename] & " " & [Surname]) Like "*" & [Forms]![Frm_Search]![txtSearch].[Text] & "*") AND (([Forms]![Frm_Search]![lstStatus])="(All)"))
ORDER BY Tbl_Company.Company;
Many Thanks in anticipation
