Need to add multiple conditional And statements to query

[zy0n1c]

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.

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.

[Minty]

If you really break things using VBA I would be very surprised, and this task would be easier in VBA.
However you can apply the same technique as the combo boxes, and again - get rid of the Like expression.
This depends on if your check boxes are triple state, e.g. Yes/No/Null , and whether you allow null values in your records for those Yes/No fields?

EmployeeDB.[Basic+] = forms!EmployeeListMenu!CheckBasicPlus or forms!EmployeeListMenu!CheckBasicPlus is null

If not then a different approach will be required, as you will always have a value to check against.

[zy0n1c]

Where would I find the property for allowing null values?

I've got "Read Only When Disconnected", Subdatasheet Expanded, Subdatasheet Height, Orientation, Description, Default View, Validation Rule, Validation Text, Filter, Order By, Subdatasheet Name, Link Child Fields, Link Master Fields, Filter On Load, Order By On Load.


If this isn't a property setting, then I don't understand the question. What is the difference in "No" vs. "null" ?

In regards to the VBA - I may just be clueless as to where to put code at, but following the few examples I have has made it so I've had to restore from a backup because I couldn't open a form or query at all without an error about the VBA/Module code.

[Minty]

The property is on the field in the table.
In your form it's referred to as triple state under the data tab? You can set a default value for a check box which would determine the state. If its' null it will appear as a darkened box(neither ticked or un-ticked, Null), if you allow triple state.

[zy0n1c]

Well, I set the default value to "False" because I wasn't sure what behavior that null value would have had. This automatically changed the property to "No" for triple state. Let me set it back to null and see what kind of results I can get.


Also. I totally changed it from Like to = but at some point restoring an older version to correct another problem it probably reverted the code and I never changed it back.

[zy0n1c]

The null value and the new argument you gave me is working great -- Except, I still don't know how to compound the rest of the checkboxes behind that one. I believe you've mentioned before that you don't often use the SQL editor and let the design view input the parenthesis/brackets for you?

[Beetle]

I think a question that needs to asked here, or at least something that needs to be pointed out, is your table structure. That is really the root cause of the problem that you're dealing with now. For example, your EmployeeDB table contains these fields, all of which are boolean (Yes/No) fields I'm guessing;

Basic+,TWIC, HAZWOPER, Hydroblasting, Scaffold Builder, Confined Spaces, OSHA 10, OSHA 40, CDL w/VAC, CDL (standard)

These appear to be different types of certifications or classifications that an employee can have. You're storing data as field names. These should not be fields in your Employee table, this should be data in a separate table. In reality what you have is a many-to-many relationship between Employees and Certifications (or whatever you consider these). This needs to be modeled with three tables, one for Employees, one for Certification types and a junction table to manage the relationship.

There are a multiple problems associated with your current structure, one of which you are currently struggling with (queries can become a nightmare). One of the other significant problems is future design issues. Let's suppose that at some point in the future more types of Certifications get thrown into the mix. With your current structure you will need to do the following;

1) Add the additional necessary fields to the table (which is really data, not fields).
2) Redesign every query that uses that table as a record source, selecting the additional fields and adding additional criteria to the where clause.
3) Redesign every form or report that uses any of those queries (or the table) as a record source, adding additional controls to represent the new choices.
4) If your application uses any VBA or macros that that reference the certification type fields, you will need to find all those instances and rewrite that as well.
5) Probably a few other things that aren't coming to mind right now.

With a proper table structure you would simply add the additional data to the Certifications table and the new choices would instantly be available everywhere that you need them, no need to redesign anything.

Some food for thought.

[ssanfu]

In addition to Sean's comments, your naming convention WILL cause you problems.

Basic+,TWIC, HAZWOPER, Hydroblasting, Scaffold Builder, Confined Spaces, OSHA 10, OSHA 40, CDL w/VAC, CDL (standard), Valid Driver's License

In the "field" names, you have spaces, "+", "/", "(", ")", and "'".

Some suggestions:
Use only letters and numbers (exception is the underscore) for object names. (Object names = fields, table, queries, forms, modules and reports)
Do not use spaces, punctuation or special characters in object names.
Do not use look up FIELDS, multi-value fields or calculated fields in tables.
Do not begin object names with a number.