Search Form with Multiple Combo Boxes (1 Viewer)

[ITwannabe]

Hi,

Trying to figure out why my search form only returns results if I include a date range. I want it to return results if any of the fields contain data or return all results if they are left blank.

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] & "*"));

 

[pbaldy]

Like doesn't really work with dates or numbers. Try

Between Nz([Forms]![Search for CA/PIP]![BeginDate], #1/1/1990#) And Nz([Forms]![Search for CA/PIP]![End Date], #12/31/2029#)

where the two dates are outside the range of anything in your data.

 

[ITwannabe]

Thank you pbaldy that did the trick!!!!

You are officially my hero

no more :banghead:for me!

 

[pbaldy]

Happy to help!

When I have a number of optional parameters, I often use dynamic SQL instead of complex queries like that. The method is demonstrated in the sample db here:

http://www.baldyweb.com/BuildSQL.htm