Hey guys, Im trying to setup a form that allows users to search for information but I need to base it out on 9 different fields. The kicker is that I need for the query to limit based on the entries but if nothing is entered then to treat it as a wildcard. I have managed to get the query to work using only 2 criteria fields and the SQL is quite long, I was hoping that someone here might be able to point me in a more efficient direction of handling this. Thanks for any help
/code
SELECT tblMain.Department, tblCountdetail.[Part no]
FROM tblMain INNER JOIN tblCountdetail ON tblMain.[Count no] = tblCountdetail.[Count no]
WHERE (((tblMain.Department) Like [Forms]![frmCountEditReview]![txtdepartment]) AND ((tblCountdetail.[Part no]) Like [Forms]![frmCountEditReview]![txtsku])) OR (((tblCountdetail.[Part no]) Like [Forms]![frmCountEditReview]![txtsku]) AND (([Forms]![frmCountEditReview]![txtdepartment]) Is Null)) OR (((tblMain.Department) Like [Forms]![frmCountEditReview]![txtdepartment]) AND (([Forms]![frmCountEditReview]![txtsku]) Is Null)) OR ((([Forms]![frmCountEditReview]![txtdepartment]) Is Null) AND (([Forms]![frmCountEditReview]![txtsku]) Is Null));
/code
That is the SQL behind getting 2 fields to work
/code
SELECT tblMain.Department, tblCountdetail.[Part no]
FROM tblMain INNER JOIN tblCountdetail ON tblMain.[Count no] = tblCountdetail.[Count no]
WHERE (((tblMain.Department) Like [Forms]![frmCountEditReview]![txtdepartment]) AND ((tblCountdetail.[Part no]) Like [Forms]![frmCountEditReview]![txtsku])) OR (((tblCountdetail.[Part no]) Like [Forms]![frmCountEditReview]![txtsku]) AND (([Forms]![frmCountEditReview]![txtdepartment]) Is Null)) OR (((tblMain.Department) Like [Forms]![frmCountEditReview]![txtdepartment]) AND (([Forms]![frmCountEditReview]![txtsku]) Is Null)) OR ((([Forms]![frmCountEditReview]![txtdepartment]) Is Null) AND (([Forms]![frmCountEditReview]![txtsku]) Is Null));
/code
That is the SQL behind getting 2 fields to work