Multi fields search form

M

Mrodrz

I have a form with multiple combo boxes. I need to find all records that
maches the seleted criteria (Combo boxes). The criteria runs from multiple
tables. My problem is that I cannot get it to work, so It will return only
exact matches since the user will not always select all fields. Sometimes
they will select only two fields, and it causes a problem with nulls values
from the form.

SELECT Tbl_Employee.Employee_Name, Tbl_Employee.Department,
TblEmp_Domain_Knowledge.[Domain Knowledge],
TblEmp_System_Knowledge.Emp_System_Knowledge, TblTaskForce.TForce_Name
FROM ((Tbl_Employee INNER JOIN TblEmp_Domain_Knowledge ON
Tbl_Employee.Employee_ID = TblEmp_Domain_Knowledge.Emp_ID) INNER JOIN
TblEmp_System_Knowledge ON Tbl_Employee.Employee_ID =
TblEmp_System_Knowledge.Emp_ID) INNER JOIN (TblTaskForce INNER JOIN
TblTaskForceEmployees ON TblTaskForce.TForce_Name =
TblTaskForceEmployees.TblTforceEmp_TforceName) ON Tbl_Employee.Employee_ID =
TblTaskForceEmployees.TblTforceEmp_ID
WHERE
(((Tbl_Employee.Department)=[Forms]![FrmSelectCriteria].[TxtDepartment]) AND
((TblEmp_Domain_Knowledge.[Domain Knowledge]) In
([Forms]![FrmSelectCriteria]![TxtDomain1],[Forms]![FrmSelectCriteria]![TxtDomain2],[Forms]![FrmSelectCriteria]![TxtDomain3],[Forms]![FrmSelectCriteria]![TxtDomain4]))
AND ((TblEmp_System_Knowledge.Emp_System_Knowledge) In
([Forms]![FrmSelectCriteria]![TxtSystem1],[Forms]![FrmSelectCriteria]![TxtSystem2],[Forms]![FrmSelectCriteria]![TxtSystem3],[Forms]![FrmSelectCriteria]![TxtSystem4]))
AND ((TblTaskForce.TForce_Name) In
([Forms]![FrmSelectCriteria]![Tforce1],[Forms]![FrmSelectCriteria]![Tforce2],[Forms]![FrmSelectCriteria]![Tforce3],[Forms]![FrmSelectCriteria]![Tforce4])));


How can I avoid null text boxes in the form? Or how to refer to if not null
in the select statement?
 
J

Jeff Boyce

How depends on what, as in what data structure you are using ...

Based on your description, it sounds like there's a chance your data
structure would benefit from additional normalization.

If your response is either ?!huh-"normalization"?!, or "yeah, yeah, we don't
need normalization", keep in mind that MS Access is a relational database,
and its features/functions are optimized for well-normalized data. If you
don't feed it what it expects, how can you expect it to answer?

More info, please...

Good luck

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Mrodrz said:
I have a form with multiple combo boxes. I need to find all records that
maches the seleted criteria (Combo boxes). The criteria runs from multiple
tables. My problem is that I cannot get it to work, so It will return
only
exact matches since the user will not always select all fields. Sometimes
they will select only two fields, and it causes a problem with nulls
values
from the form.

SELECT Tbl_Employee.Employee_Name, Tbl_Employee.Department,
TblEmp_Domain_Knowledge.[Domain Knowledge],
TblEmp_System_Knowledge.Emp_System_Knowledge, TblTaskForce.TForce_Name
FROM ((Tbl_Employee INNER JOIN TblEmp_Domain_Knowledge ON
Tbl_Employee.Employee_ID = TblEmp_Domain_Knowledge.Emp_ID) INNER JOIN
TblEmp_System_Knowledge ON Tbl_Employee.Employee_ID =
TblEmp_System_Knowledge.Emp_ID) INNER JOIN (TblTaskForce INNER JOIN
TblTaskForceEmployees ON TblTaskForce.TForce_Name =
TblTaskForceEmployees.TblTforceEmp_TforceName) ON Tbl_Employee.Employee_ID
=
TblTaskForceEmployees.TblTforceEmp_ID
WHERE
(((Tbl_Employee.Department)=[Forms]![FrmSelectCriteria].[TxtDepartment])
AND
((TblEmp_Domain_Knowledge.[Domain Knowledge]) In
([Forms]![FrmSelectCriteria]![TxtDomain1],[Forms]![FrmSelectCriteria]![TxtDomain2],[Forms]![FrmSelectCriteria]![TxtDomain3],[Forms]![FrmSelectCriteria]![TxtDomain4]))
AND ((TblEmp_System_Knowledge.Emp_System_Knowledge) In
([Forms]![FrmSelectCriteria]![TxtSystem1],[Forms]![FrmSelectCriteria]![TxtSystem2],[Forms]![FrmSelectCriteria]![TxtSystem3],[Forms]![FrmSelectCriteria]![TxtSystem4]))
AND ((TblTaskForce.TForce_Name) In
([Forms]![FrmSelectCriteria]![Tforce1],[Forms]![FrmSelectCriteria]![Tforce2],[Forms]![FrmSelectCriteria]![Tforce3],[Forms]![FrmSelectCriteria]![Tforce4])));


How can I avoid null text boxes in the form? Or how to refer to if not
null
in the select statement?
Click to expand...
 
Top