SQL String Help

[Secret Squirrel]

I'm trying to write a string query to pull records but I'm not having much
luck using the "AND" statement. I'm using a listbox to select multiple
records but why doesn't the AND statement pull them? I keep getting a blank
result. Here is the code I'm using:

Set ctl = Me.SkillList
strSQL = "Select EmpName from qryEmployeeID"

intI = 0
For Each varItem In ctl.ItemsSelected
intI = intI + 1
If intI = 1 Then
strSQL = strSQL & " WHERE [CapacityID] = " & ctl.ItemData(varItem)
Else
strSQL = strSQL & " AND [CapacityID]= " & ctl.ItemData(varItem)
End If

Next varItem

 

[Secret Squirrel]

I should also note the results I'm trying to achieve. I'm looking to list all
employees who have the selected CapacityID. I only want to return the results
if an employee has all of the selected from the listbox. I don't want to list
employees who have just some of them, they have to have all to show in my
results.

 

[John Spencer]

It sounds as if you need a subquery. No record is going to have capacityID
have more than one value so you can't use AND. Using OR would get you matches
where any value matched.

You would need a query along the lines of the following. This ASSUMES that an
employeeID will be associated at most ONE time with any specific CapacityID.
Also that CapacityID is a number field.

SELECT EmployeeID
FROM qryEmployeeID
WHERE 5 =
(SELECT Count(*)
FROM qryEmployeeID as Temp
WHERE Temp.EmployeeID = QryEmployeeID
AND CapacityID IN (22,35,17,29,30))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore

I should also note the results I'm trying to achieve. I'm looking to list all
employees who have the selected CapacityID. I only want to return the results
if an employee has all of the selected from the listbox. I don't want to list
employees who have just some of them, they have to have all to show in my
results.

I'm trying to write a string query to pull records but I'm not having much
luck using the "AND" statement. I'm using a listbox to select multiple
records but why doesn't the AND statement pull them? I keep getting a blank
result. Here is the code I'm using:

Set ctl = Me.SkillList
strSQL = "Select EmpName from qryEmployeeID"

intI = 0
For Each varItem In ctl.ItemsSelected
intI = intI + 1
If intI = 1 Then
strSQL = strSQL & " WHERE [CapacityID] = " & ctl.ItemData(varItem)
Else
strSQL = strSQL & " AND [CapacityID]= " & ctl.ItemData(varItem)
End If

Next varItem

 

[Secret Squirrel]

Hi John,

I'm not sure I follow you totally. What does the "5" in the where clause
represent?

In your example how do I specify which of the CapacityID's it will filter my
results with? I'm using a multiselect listbox to select the Capacity ID's and
then using that to filter my employees. It works not but when I select say 2
capacityID's it gives me all employees that have either of those. I just want
to see the employees that have both of those selections, not just 1 or the
other.

The underlying table where the qryEmployeeID is pulling the data from has
multiple records for the same employee but with different capacityID's. So
say I selected CapacityID 111 & 112 I would want to see only the employees
that have records for both of those.

It sounds as if you need a subquery. No record is going to have capacityID
have more than one value so you can't use AND. Using OR would get you matches
where any value matched.

You would need a query along the lines of the following. This ASSUMES that an
employeeID will be associated at most ONE time with any specific CapacityID.
Also that CapacityID is a number field.

SELECT EmployeeID
FROM qryEmployeeID
WHERE 5 =
(SELECT Count(*)
FROM qryEmployeeID as Temp
WHERE Temp.EmployeeID = QryEmployeeID
AND CapacityID IN (22,35,17,29,30))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore

I should also note the results I'm trying to achieve. I'm looking to list all
employees who have the selected CapacityID. I only want to return the results
if an employee has all of the selected from the listbox. I don't want to list
employees who have just some of them, they have to have all to show in my
results.

I'm trying to write a string query to pull records but I'm not having much
luck using the "AND" statement. I'm using a listbox to select multiple
records but why doesn't the AND statement pull them? I keep getting a blank
result. Here is the code I'm using:

Set ctl = Me.SkillList
strSQL = "Select EmpName from qryEmployeeID"

intI = 0
For Each varItem In ctl.ItemsSelected
intI = intI + 1
If intI = 1 Then
strSQL = strSQL & " WHERE [CapacityID] = " & ctl.ItemData(varItem)
Else
strSQL = strSQL & " AND [CapacityID]= " & ctl.ItemData(varItem)
End If

Next varItem

 

[John Spencer]

The five is the number of different capacityid values that would have to be
matched. So if you wanted to match 2 different capacityId values, the number
would be two.

Your query would have to built on the fly. A code snippet that may help you
with that process

Dim strWhere as string
Dim strSQL as String
Dim Ctl as Control

Set ctl = Me.SkillList
strSQL = "Select EmpName from qryEmployeeID"

intI = 0
For Each varItem In ctl.ItemsSelected
intI = intI + 1
StrWhere = StrWhere & " OR [CapacityID] = " & ctl.ItemData(varItem)
Next varItem

strSQL = StrSQL & " WHERE " & int1 & " = " & _
"(SELECT Count(CapacityID) FROM qryEmployeeID as Temp" & _
" WHERE Temp.EmpName = qryEmployeeID.EmpName" & _
" AND (" & Mid(strWhere,4) & "))"

Now use this query to get your results.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi John,

I'm not sure I follow you totally. What does the "5" in the where clause
represent?

In your example how do I specify which of the CapacityID's it will filter my
results with? I'm using a multiselect listbox to select the Capacity ID's and
then using that to filter my employees. It works not but when I select say 2
capacityID's it gives me all employees that have either of those. I just want
to see the employees that have both of those selections, not just 1 or the
other.

The underlying table where the qryEmployeeID is pulling the data from has
multiple records for the same employee but with different capacityID's. So
say I selected CapacityID 111 & 112 I would want to see only the employees
that have records for both of those.

It sounds as if you need a subquery. No record is going to have capacityID
have more than one value so you can't use AND. Using OR would get you matches
where any value matched.

You would need a query along the lines of the following. This ASSUMES that an
employeeID will be associated at most ONE time with any specific CapacityID.
Also that CapacityID is a number field.

SELECT EmployeeID
FROM qryEmployeeID
WHERE 5 =
(SELECT Count(*)
FROM qryEmployeeID as Temp
WHERE Temp.EmployeeID = QryEmployeeID
AND CapacityID IN (22,35,17,29,30))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore

I should also note the results I'm trying to achieve. I'm looking to list all
employees who have the selected CapacityID. I only want to return the results
if an employee has all of the selected from the listbox. I don't want to list
employees who have just some of them, they have to have all to show in my
results.

:

I'm trying to write a string query to pull records but I'm not having much
luck using the "AND" statement. I'm using a listbox to select multiple
records but why doesn't the AND statement pull them? I keep getting a blank
result. Here is the code I'm using:

Set ctl = Me.SkillList
strSQL = "Select EmpName from qryEmployeeID"

intI = 0
For Each varItem In ctl.ItemsSelected
intI = intI + 1
If intI = 1 Then
strSQL = strSQL & " WHERE [CapacityID] = " & ctl.ItemData(varItem)
Else
strSQL = strSQL & " AND [CapacityID]= " & ctl.ItemData(varItem)
End If

Next varItem

 

[Dirk Goldgar]

I'm trying to write a string query to pull records but I'm not having much
luck using the "AND" statement. I'm using a listbox to select multiple
records but why doesn't the AND statement pull them? I keep getting a
blank
result. Here is the code I'm using:

Set ctl = Me.SkillList
strSQL = "Select EmpName from qryEmployeeID"

intI = 0
For Each varItem In ctl.ItemsSelected
intI = intI + 1
If intI = 1 Then
strSQL = strSQL & " WHERE [CapacityID] = " & ctl.ItemData(varItem)
Else
strSQL = strSQL & " AND [CapacityID]= " & ctl.ItemData(varItem)
End If

Next varItem

No record can have multiple values for CapacityID, so using AND to connect
them is out. Are you looking to return records that contain any of the
selected values? If so, you'd have to use OR, not AND, to connect the items
in the list if doing a value by value comparison. But I would probably use
the In() operator if there were more than 1 value selected, like this:

Dim strCriteria As String

With Me.SkillList
intI = 0
For Each varItem In .ItemsSelected
intI = intI + 1
strCriteria = strCriteria & ", " & .ItemData(varItem)
Next varItem
End With

If intI > 0 Then
If intI = 1 Then
strCriteria = "= " & Mid$(strCriteria, 3)
Else
strCriteria = "In(" & Mid$(strCriteria, 3) & ")"
End If
strSQL = strSQL & " WHERE CapacityID " & strCriteria
End If

 

[Bob Quintal]

=?Utf-8?B?U2VjcmV0IFNxdWlycmVs?=

I should also note the results I'm trying to achieve. I'm looking
to list all employees who have the selected CapacityID. I only
want to return the results if an employee has all of the selected
from the listbox. I don't want to list employees who have just
some of them, they have to have all to show in my results.

I'm trying to write a string query to pull records but I'm not
having much luck using the "AND" statement. I'm using a listbox
to select multiple records but why doesn't the AND statement pull
them? I keep getting a blank result. Here is the code I'm using:

Set ctl = Me.SkillList
strSQL = "Select EmpName from qryEmployeeID"

intI = 0
For Each varItem In ctl.ItemsSelected
intI = intI + 1
If intI = 1 Then
strSQL = strSQL & " WHERE [CapacityID] = " &
ctl.ItemData(varItem)
Else
strSQL = strSQL & " AND [CapacityID]= " &
ctl.ItemData(varItem)
End If

Next varItem

The trick to get this to work is to use an Integer or Long for
CapacityId, but limited to powers of 2. calculate 2 to the power of
CapacityID and SUM the value.
You then need to AND this value with the desired quantity to return a
true or false false.

so given 5 capacityID possibilities, 1,2,4,8,16 you want to find
anyone who has 1 and 4, you sum the EmployeeID_CapacityID table.
John 1
john 2
John 4
Mark 1
Mark 4
Ann 1
Ann 8
Pete 2
Pete 4

which returns
Ann 9
John 7
Mark 5
Pete 6


Ann (9 and 5) = 5 is false
John (7 AND 5) = 5 is true
Mark (5 and 5) = 5 is true
Pete (6 and 5) = 5 is false

for some reason I could not get the AND to work in the query, so I
created a little function:

Public Function blAnd(param1, param2) As Boolean
If IsNull(param1) Or IsNull(param2) Then
blAnd = False
Else
blAnd = ((param1 And param2) = param2)
End If
End Function

This SQL demonstrates

PARAMETERS capacities Long;
SELECT EmployeeCapacity.EmployeeID, Sum(EmployeeCapacity.CapacityID)
AS SumOfCapacityID, blAnd(Sum([capacityID]),[capacities]) AS Expr1
FROM EmployeeCapacity
GROUP BY EmployeeCapacity.EmployeeID;

and can be reduced to

PARAMETERS capacities Long;
SELECT EmployeeCapacity.EmployeeID
FROM EmployeeCapacity
GROUP BY EmployeeCapacity.EmployeeID
HAVING (((bland(Sum([capacityID]),[capacities]))=True));