Wrong Report from Query

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
 
S

Stefan Hoffmann

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 <--
 
R

Robert Painter

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
 
S

Stefan Hoffmann

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 <--
 
R

Robert Painter

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
 
S

Stefan Hoffmann

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 <--
 
R

rodrigo silva

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...
 
R

Robert Painter

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

IN in query 8
Crosstab Query Building Report 2
recordset query 2
data type mismatch 4
Simplify Code-Delete Query 3
Cleanup Code on Form ?? 4
querying several times ??? 0
HOW TO Simplify following code 4

Top