Wrong Report from Query

  • Thread starter Thread starter Robert Painter
  • Start date Start date
R

Robert Painter

Hi,
I have the following code for a query resulting in a report opening with the
result.

The problem is if there are no matches it seems to fill with all employees.
How can i stop this please

Private Sub cmdOK_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCategory As String
Dim strSkill As String
Dim strOption As Integer
Dim mySql As String

' getting choices from list box (note list categories not used)

For Each varItem In Me.LstSkill.ItemsSelected
strSkill = strSkill & "," & Me.LstSkill.ItemData(varItem)
Next varItem
' checking length of resulting string
If Len(strSkill) = 0 Then
strSkill = "Like '*'"
Else
strSkill = Right(strSkill, Len(strSkill) - 1)
strSkill = "NOT IN(" & strSkill & ")"
' getting option group into stroption
strOption = Me.grpStatus
End If
Set db = CurrentDb
mySql = "SELECT DISTINCT tblEmployees.EmployeeName " & _
"FROM tblEmployees LEFT JOIN tblEmployeeSkills " & _
"ON tblEmployees.EmployeeID = tblEmployeeSkills.EmployeeIDFK " & _
"WHERE tblEmployeeSkills.SkillIDFK " & strSkill & "" & _
" And tblEmployees.EmploymentStatus = " & strOption & "" & _
" ORDER BY tblEmployees.EmployeeName"
'MsgBox mySql
Set qdf = db.QueryDefs("qryskillsListQuery")
qdf.SQL = mySql
'DoCmd.OpenQuery "qrySkillsListQuery", acViewPreview
DoCmd.Close acForm, "frmSkillListQuery"
Set qdf = Nothing
Set db = Nothing

End Sub

Robert
 
hi Robert,

Robert said:
The problem is if there are no matches it seems to fill with all employees.
How can i stop this please

Dim mySql As String
Click to expand...
Shame upon you .)
For Each varItem In Me.LstSkill.ItemsSelected
strSkill = strSkill & "," & Me.LstSkill.ItemData(varItem)
Next varItem
' checking length of resulting string
If Len(strSkill) = 0 Then
strSkill = "Like '*'"
Else
strSkill = Right(strSkill, Len(strSkill) - 1)
strSkill = "NOT IN(" & strSkill & ")"
' getting option group into stroption
strOption = Me.grpStatus
End If
Click to expand...
What does MsgBox mySQL shows you?
Set qdf = db.QueryDefs("qryskillsListQuery")
qdf.SQL = mySql
Click to expand...
You can directly assign a SQL condition to the DoCmd.OpenReport.


mfG
--> stefan <--
 
Hi Stefan
Why the shame upon you ??
anyway
msgbox mysql shows the sql mysql with result of strskill which is numbers of
skills (skillid), and stroption which is the number from option group.
And no i didn't know you can run report that way.

Robert
 
hi Robert,

Robert said:
Why the shame upon you ?? mysql.org

msgbox mysql shows the sql mysql with result of strskill which is
numbers of skills (skillid), and stroption which is the number from
option group.
Click to expand...
Ahmm, can you post it, maybe the entier SQL?


mfG
--> stefan <--
 
Hi Stefan

herewith code for list box selection initial query

Robert


Private Sub cmdOK_Click()
' to carry out search of employee skills from multioption list boxes
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCategory As String
Dim strSkill As String
Dim strOption As Integer
Dim mySql As String

' getting choices from list box (note list categories not used)

For Each varItem In Me.LstSkill.ItemsSelected
strSkill = strSkill & "," & Me.LstSkill.ItemData(varItem)
Next varItem
' checking length of resulting string

If Len(strSkill) = 0 Then
strSkill = "Like '*'"
Else
strSkill = Right(strSkill, Len(strSkill) - 1)
strSkill = "NOT IN(" & strSkill & ")"

' getting option group into stroption
strOption = Me.grpStatus

End If
Set db = CurrentDb
mySql = "SELECT DISTINCT
tblEmployees.Title,tblEmployees.EmployeeName,tblEmployees.FirstName,tblEmployees.HomePhone,tblEmployees.MobilePhone
" & _
"FROM tblEmployees LEFT JOIN tblEmployeeSkills " & _
"ON tblEmployees.EmployeeID = tblEmployeeSkills.EmployeeIDFK " & _
"WHERE tblEmployeeSkills.SkillIDFK " & strSkill & "" & _
" And tblEmployees.EmploymentStatus = " & strOption & "" & _
" ORDER BY tblEmployees.EmployeeName,tblEmployees.FirstName"
'MsgBox mySql
Set qdf = db.QueryDefs("qryskillsListQuery")
qdf.SQL = mySql

'DoCmd.OpenQuery "qrySkillsListQuery"
DoCmd.OpenReport "qryskillslistquery", acViewPreview
DoCmd.Close acForm, "frmSkillSListQuery"
Set qdf = Nothing
Set db = Nothing

End Sub
 
hi Robert,

Robert said:
mySql = "SELECT DISTINCT
tblEmployees.Title,tblEmployees.EmployeeName,tblEmployees.FirstName,tblEmployees.HomePhone,tblEmployees.MobilePhone
" & _
"FROM tblEmployees LEFT JOIN tblEmployeeSkills " & _
"ON tblEmployees.EmployeeID = tblEmployeeSkills.EmployeeIDFK " & _
"WHERE tblEmployeeSkills.SkillIDFK " & strSkill & "" & _
" And tblEmployees.EmploymentStatus = " & strOption & "" & _
" ORDER BY tblEmployees.EmployeeName,tblEmployees.FirstName"
'MsgBox mySql
Click to expand...
I meant that SQL above...


mfG
--> stefan <--
 
Robert Painter said:
Hi,
I have the following code for a query resulting in a report opening with
the result.

The problem is if there are no matches it seems to fill with all
employees.
How can i stop this please

Private Sub cmdOK_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCategory As String
Dim strSkill As String
Dim strOption As Integer
Dim mySql As String

' getting choices from list box (note list categories not used)

For Each varItem In Me.LstSkill.ItemsSelected
strSkill = strSkill & "," & Me.LstSkill.ItemData(varItem)
Next varItem
' checking length of resulting string
If Len(strSkill) = 0 Then
strSkill = "Like '*'"
Else
strSkill = Right(strSkill, Len(strSkill) - 1)
strSkill = "NOT IN(" & strSkill & ")"
' getting option group into stroption
strOption = Me.grpStatus
End If
Set db = CurrentDb
mySql = "SELECT DISTINCT tblEmployees.EmployeeName " & _
"FROM tblEmployees LEFT JOIN tblEmployeeSkills " & _
"ON tblEmployees.EmployeeID = tblEmployeeSkills.EmployeeIDFK " & _
"WHERE tblEmployeeSkills.SkillIDFK " & strSkill & "" & _
" And tblEmployees.EmploymentStatus = " & strOption & "" & _
" ORDER BY tblEmployees.EmployeeName"
'MsgBox mySql
Set qdf = db.QueryDefs("qryskillsListQuery")
qdf.SQL = mySql
'DoCmd.OpenQuery "qrySkillsListQuery", acViewPreview
DoCmd.Close acForm, "frmSkillListQuery"
Set qdf = Nothing
Set db = Nothing

End Sub

Robert
Click to expand...
 
Hi Stefan,
Sorry for delay in responding but have been working away from home for week
with no internet access however i have solved my problem by using count:

skillcount = Me.LstSkill.ItemsSelected.count
Set db = CurrentDb
mySql = "SELECT DISTINCT
tblEmployees.Title,tblEmployees.EmployeeName,tblEmployees.FirstName,tblEmployees.HomePhone,tblEmployees.MobilePhone
" & _
"FROM tblEmployees LEFT JOIN tblEmployeeSkills " & _
"ON tblEmployees.EmployeeID = tblEmployeeSkills.EmployeeIDFK " & _
"WHERE tblEmployeeSkills.SkillIDFK " & strSkill & "" & _
" And tblEmployees.EmploymentStatus = " & strOption & "" & _
" GROUP BY Title,EmployeeName,FirstName,HomePhone,MobilePhone" & _
" HAVING COUNT(*) = " & skillcount & "" & _
" ORDER BY tblEmployees.EmployeeName,tblEmployees.FirstName"
which works a treat.

Thans for your help on this matter.

Robert
 
Back
Top