Using AND or Something?

  • Thread starter Thread starter gngsquared
  • Start date Start date
G

gngsquared

Easy one for the pros but it has me scratching my head in frustration. So
here goes.

[TrainDocTbl] is indexed on autonumber field [TrainDocID]. The table
includes a field [TrainDocName] that will contain 15 to 20 records. The
records are for the different certifications that the employees have. Every
employee has one common certification but there are employees that may have
2, 3, or more additional certification that must be tracked.

The db is form based and my dilemma is constructing a query and listbox that
would allow multiple selections of the certification types. I just cannot get
the correct formula for choosing x and y and z. My efforts always seem to
give zero results or all records.

Any and all help would be greatly appreciated.
 
Here is some code to get all the user selections.
--------------------
Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select
End Function
 
"The table includes a field [TrainDocName] that will contain 15 to 20 records"

What does this mean? Fields cannot contain records. Do you mean it can have
15 to 20 different values?

If a field (column) needs to contain multiple values you need to make the
column a foreign key to another table which will contain the multiple
records. This is called a one-to-many relationship. Your form could have a
subform to set up these values.
Look up Subforms in Access Help.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
Sorry I didn't parse the question correctly.

The main form has a subform. The subform displays the various certifications
of the individual employee along with other data from various tables. This is
done with a one to many relationship as are several other relationships in
the eight tables that the db contains. The question was not intended to be a
'Form' question.

What I want is a way to find which employee is certified in x and y and z
and so on to include in a report or screen display. Ideally, by listbox with
a checkbox to allow the user to select which certifications are required and
get the result of which employees hold the desired certifications.

What I need to figure out is how to choose multiple criteria from the
selected table. It is a simple thing, but something that has me stumped.

Dorian said:
"The table includes a field [TrainDocName] that will contain 15 to 20 records"

What does this mean? Fields cannot contain records. Do you mean it can have
15 to 20 different values?

If a field (column) needs to contain multiple values you need to make the
column a foreign key to another table which will contain the multiple
records. This is called a one-to-many relationship. Your form could have a
subform to set up these values.
Look up Subforms in Access Help.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


gngsquared said:
Easy one for the pros but it has me scratching my head in frustration. So
here goes.

[TrainDocTbl] is indexed on autonumber field [TrainDocID]. The table
includes a field [TrainDocName] that will contain 15 to 20 records. The
records are for the different certifications that the employees have. Every
employee has one common certification but there are employees that may have
2, 3, or more additional certification that must be tracked.

The db is form based and my dilemma is constructing a query and listbox that
would allow multiple selections of the certification types. I just cannot get
the correct formula for choosing x and y and z. My efforts always seem to
give zero results or all records.

Any and all help would be greatly appreciated.
Click to expand...
Click to expand...
 
I see several ways to do this. In one you would add a field to the
[TrainDocTbl] so as to pick requirements - a Yes/No field.
On opening the form for this process have an update query run to un-pick all
criteria. The form to have two subforms. The first to display the
certifications and check box for your picks. The second to list employees
meeting the certification criteria. After picking requirements click a
command button to requery the query feeding the employee subform.
That query would use the Yes/No field as criteria.

You could get real fancy to display those that meet all criteria followed by
those that meet some requirements by sorting on ABS sum of criteria met and
then by name or other items.

--
Build a little, test a little.


gngsquared said:
Sorry I didn't parse the question correctly.

The main form has a subform. The subform displays the various certifications
of the individual employee along with other data from various tables. This is
done with a one to many relationship as are several other relationships in
the eight tables that the db contains. The question was not intended to be a
'Form' question.

What I want is a way to find which employee is certified in x and y and z
and so on to include in a report or screen display. Ideally, by listbox with
a checkbox to allow the user to select which certifications are required and
get the result of which employees hold the desired certifications.

What I need to figure out is how to choose multiple criteria from the
selected table. It is a simple thing, but something that has me stumped.

Dorian said:
"The table includes a field [TrainDocName] that will contain 15 to 20 records"

What does this mean? Fields cannot contain records. Do you mean it can have
15 to 20 different values?

If a field (column) needs to contain multiple values you need to make the
column a foreign key to another table which will contain the multiple
records. This is called a one-to-many relationship. Your form could have a
subform to set up these values.
Look up Subforms in Access Help.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


gngsquared said:
Easy one for the pros but it has me scratching my head in frustration. So
here goes.

[TrainDocTbl] is indexed on autonumber field [TrainDocID]. The table
includes a field [TrainDocName] that will contain 15 to 20 records. The
records are for the different certifications that the employees have. Every
employee has one common certification but there are employees that may have
2, 3, or more additional certification that must be tracked.

The db is form based and my dilemma is constructing a query and listbox that
would allow multiple selections of the certification types. I just cannot get
the correct formula for choosing x and y and z. My efforts always seem to
give zero results or all records.

Any and all help would be greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top