Hi,
Let me start by saying, while I'm not new to Access, I am new to SQL and VBA...so be gentle
I've built a form in a database (Access 2007) that runs a query to search a table and bring back results.
The form contains multiple combo boxes. I built it out so the user can enter full or partial information in one field or multiple fields, or it will return all records if all fields are left blank. It also clears the form of data after the search is completed...at least that works.
It was working fine until I was asked to add a date range combo box
. Now if I get the date range search to work, the rest will not, and vice versa.
As it stands right now, the date range is the only search that works. Below is the SQL for the query.
I'd appreciate any assistance, but remember, I'm no coder so if you make code change suggestions, the more specific the better (like where to put it and exactly what to write). If you can help I will be your bff for life...virtually of course, I'm a data person...not a people person
SELECT tblCorrectiveActions2018.ID, tblCorrectiveActions2018.Name, tblCorrectiveActions2018.[Case #], tblCorrectiveActions2018.Location, tblCorrectiveActions2018.Mgr, tblCorrectiveActions2018.Program, tblCorrectiveActions2018.DOH, tblCorrectiveActions2018.[Date Issued], tblCorrectiveActions2018.[CA Level], tblCorrectiveActions2018.[# of Weeks (PIP)], tblCorrectiveActions2018.[Reason(s)], tblCorrectiveActions2018.[Active or Inactive], tblCorrectiveActions2018.Retracted, tblCorrectiveActions2018.[Final Status], tblCorrectiveActions2018.[HR Advisor], tblAssociates.[HR Business Partner]
FROM tblAssociates RIGHT JOIN tblCorrectiveActions2018 ON tblAssociates.[Employee ID] = tblCorrectiveActions2018.ID
WHERE (((tblCorrectiveActions2018.ID) Like "*" & [Forms]![Search for CA/PIP]![EmpID] & "*") AND ((tblCorrectiveActions2018.Name) Like "*" & [Forms]![Search for CA/PIP]![Name] & "*") AND ((tblCorrectiveActions2018.Location) Like "*" & [Forms]![Search for CA/PIP]![Location] & "*") AND ((tblCorrectiveActions2018.Mgr) Like "*" & [Forms]![Search for CA/PIP]![Mgr] & "*") AND ((tblCorrectiveActions2018.[Date Issued]) Between [Forms]![Search for CA/PIP]![BeginDate] And [Forms]![Search for CA/PIP]![End Date]) AND ((tblCorrectiveActions2018.[CA Level]) Like "*" & [Forms]![Search for CA/PIP]![CALevel] & "*") AND ((tblCorrectiveActions2018.[Reason(s)]) Like "*" & [Forms]![Search for CA/PIP]![Reason] & "*") AND ((tblCorrectiveActions2018.[HR Advisor]) Like "*" & [Forms]![Search for CA/PIP]![HRAdvisor] & "*") AND ((tblAssociates.[HR Business Partner]) Like "*" & [Forms]![Search for CA/PIP]![HRBP] & "*") AND ((([tblCorrectiveActions2018].[Date Issued]) Like "*") Between [Forms]![Search for CA/PIP]![BeginDate] And [Forms]![Search for CA/PIP]![End Date] & "*"));
Thanks in advance!
Let me start by saying, while I'm not new to Access, I am new to SQL and VBA...so be gentle
I've built a form in a database (Access 2007) that runs a query to search a table and bring back results.
The form contains multiple combo boxes. I built it out so the user can enter full or partial information in one field or multiple fields, or it will return all records if all fields are left blank. It also clears the form of data after the search is completed...at least that works.
It was working fine until I was asked to add a date range combo box
. Now if I get the date range search to work, the rest will not, and vice versa.As it stands right now, the date range is the only search that works. Below is the SQL for the query.
I'd appreciate any assistance, but remember, I'm no coder so if you make code change suggestions, the more specific the better (like where to put it and exactly what to write). If you can help I will be your bff for life...virtually of course, I'm a data person...not a people person
SELECT tblCorrectiveActions2018.ID, tblCorrectiveActions2018.Name, tblCorrectiveActions2018.[Case #], tblCorrectiveActions2018.Location, tblCorrectiveActions2018.Mgr, tblCorrectiveActions2018.Program, tblCorrectiveActions2018.DOH, tblCorrectiveActions2018.[Date Issued], tblCorrectiveActions2018.[CA Level], tblCorrectiveActions2018.[# of Weeks (PIP)], tblCorrectiveActions2018.[Reason(s)], tblCorrectiveActions2018.[Active or Inactive], tblCorrectiveActions2018.Retracted, tblCorrectiveActions2018.[Final Status], tblCorrectiveActions2018.[HR Advisor], tblAssociates.[HR Business Partner]
FROM tblAssociates RIGHT JOIN tblCorrectiveActions2018 ON tblAssociates.[Employee ID] = tblCorrectiveActions2018.ID
WHERE (((tblCorrectiveActions2018.ID) Like "*" & [Forms]![Search for CA/PIP]![EmpID] & "*") AND ((tblCorrectiveActions2018.Name) Like "*" & [Forms]![Search for CA/PIP]![Name] & "*") AND ((tblCorrectiveActions2018.Location) Like "*" & [Forms]![Search for CA/PIP]![Location] & "*") AND ((tblCorrectiveActions2018.Mgr) Like "*" & [Forms]![Search for CA/PIP]![Mgr] & "*") AND ((tblCorrectiveActions2018.[Date Issued]) Between [Forms]![Search for CA/PIP]![BeginDate] And [Forms]![Search for CA/PIP]![End Date]) AND ((tblCorrectiveActions2018.[CA Level]) Like "*" & [Forms]![Search for CA/PIP]![CALevel] & "*") AND ((tblCorrectiveActions2018.[Reason(s)]) Like "*" & [Forms]![Search for CA/PIP]![Reason] & "*") AND ((tblCorrectiveActions2018.[HR Advisor]) Like "*" & [Forms]![Search for CA/PIP]![HRAdvisor] & "*") AND ((tblAssociates.[HR Business Partner]) Like "*" & [Forms]![Search for CA/PIP]![HRBP] & "*") AND ((([tblCorrectiveActions2018].[Date Issued]) Like "*") Between [Forms]![Search for CA/PIP]![BeginDate] And [Forms]![Search for CA/PIP]![End Date] & "*"));
Thanks in advance!
