I should start by saying that anytime I've attempted VBA/modules, I've borked my database. I am trying to do this all in an SQL query. This will be, by far, my most complicated query in my entire project. In addition to "Basic+" or "CheckBasicPlus" I've also got:
Field Names:
Basic+,TWIC, HAZWOPER, Hydroblasting, Scaffold Builder, Confined Spaces, OSHA 10, OSHA 40, CDL w/VAC, CDL (standard), Valid Driver's License.
Checkbox names:
BasicPlus, TWIC, HAZWOPER, OSHA10, OSHA40, CDL, CDLVAC, ValidDriversLicense, Hydroblasting, and Confined Spaces. (Note: The checkboxes are preceded by "Check" like in my Basic+ example in the code)
- not necessarily in any order.
Code:
SELECT EmployeeDB.[Last Name], EmployeeDB.[First Name], EmployeeDB.State, EmployeeDB.[Phone Number 1], EmployeeDB.[Phone Number 2], EmployeeDB.[Basic+], EmployeeDB.TWIC, EmployeeDB.HAZWOPER, EmployeeDB.Hydroblasting, EmployeeDB.[Scaffold Builder], EmployeeDB.[Confined Spaces], EmployeeDB.[OSHA 10], EmployeeDB.[OSHA 40], EmployeeDB.[CDL w/VAC], EmployeeDB.[CDL (standard)], EmployeeDB.[Valid Driver's License], EmployeeDB.[Job Types], EmployeeDB.[Site Specific Training 1], EmployeeDB.[Site Specific Training 2], EmployeeDB.[Site Specific Training 3], EmployeeDB.[Site Specific Training 4], EmployeeDB.[Site Specific Training 5], EmployeeDB.[Site Specific Training 6], EmployeeDB.[Eligible for Rehire], EmployeeDB.Status
FROM EmployeeDB
WHERE (
((([Forms]![EmployeeListMenu]![ComboState1] Is Null) And ([Forms]![EmployeeListMenu]![ComboState2] Is Null) And ([Forms]![EmployeeListMenu]![ComboState3] Is Null) And ([Forms]![EmployeeListMenu]![ComboState4] Is Null)) Or ((EmployeeDB.State = [Forms]![EmployeeListMenu]![ComboState1]) Or EmployeeDB.State = [Forms]![EmployeeListMenu]![ComboState2]
Or EmployeeDB.State = [Forms]![EmployeeListMenu]![ComboState3] Or EmployeeDB.State = [Forms]![EmployeeListMenu]![ComboState4]))
And
((EmployeeDB.[Basic+]) Like IIf(forms!EmployeeListMenu!CheckBasicPlus,True,"*") )
);
So what I'm trying to figure out is: How do I add additional evaluations for more checkboxes where if they're both checked, only show both, but if one is checked, only show the one. Like...How do I omit the unchecked boxes from the WHERE statement of the query? I'm having trouble even wrapping my head around the theory for asking how to do this.
P.S. This code works for JUST Basic+ right now. I can check/uncheck and it filters properly, but when I try to add the others with and & or statements, it returns all or none.