Passing select statement to query to report problems

[jberna2]

I am having trouble with my syntax somewhere. I have 3 tables:

Employee
--------
Employee_Number (pk)
Employee_FirstName
Employee_MiddleI
Employee_LastName
Area

Infraction
---------
Infraction_Code (pk)
Infraction_Description


Inventory
----------
Employee_Number (foreign from Emp table)
Infraction_Code (foreign from Inf table)
Comments
Date




I'll rename inventory to something else in a bit, it is also the name of the report I'm using. I'm working off another DB I allready had used for tracking some inventory. Anyways, I'm trying to be able to search by any of these fields to produce a report. My sub reads:


Private Sub cmdsearch_Click()
Dim stDocName As String
stDocName = "Inventory"

'Set the Dimensions of the Module
Dim strsql As String, strOrder As String, strWhere As String
Dim db As Database
Dim qryDef As QueryDef
Set db = CurrentDb()

'Make sure there is something to search for
If Len(cmbempnum) + Len(cmbarea) + Len(cmbfirst) + Len(cmbinfcode) + Len(cmblast) + Len(cmbcomments) + Len(cmbdate) + Len(cmbenddate) < 1 Then
MsgBox "Please enter a field to search"
ElseIf IsNull(cmbempnum) And IsNull(cmbarea) And IsNull(cmbfirst) And IsNull(cmbinfcode) And IsNull(cmblast) And IsNull(cmbcomments) And IsNull(cmbdate) And IsNull(cmbenddate) Then
MsgBox "Please enter a field to search"
Else

'constant select statement for the query definition
strsql = "SELECT Employee.Employee_Number, Employee.Employee_LastName, Employee.Employee_FirstName, Employee.Area, Infraction.Infraction_Description, Inventory.Comments, Inventory.Date FROM Inventory, Employee, Infraction"
strWhere = "WHERE Employee.Employee_Number = Inventory.Employee_Number AND Infraction.Infraction_Code = Inventory.Infraction_Code AND"
strOrder = "ORDER BY Employee.Employee_LastName;"

'choose fields to search
If Not IsNull(Me.cmbempnum) Then
strWhere = strWhere & " (Employee.Employee_Number) = '" & Me.cmbempnum & "' AND"
End If

If Not IsNull(Me.cmbarea) Then
strWhere = strWhere & " (Employee.Area) = '" & Me.cmbarea & "' AND"
End If

If Not IsNull(Me.cmbfirst) Then
strWhere = strWhere & " (Employee.Employee_FirstName) Like '*" & Me.cmbfirst & "*' AND"
End If

If Not IsNull(Me.cmblast) Then
strWhere = strWhere & " (Employee.Employee_LastName) Like '*" & Me.cmblast & "*' AND"
End If

If Not IsNull(Me.cmbinfcode) Then
strWhere = strWhere & " (Infraction.Infraction_Description) Like '*" & Me.cmbinfcode & "*' AND"
End If

If Not IsNull(Me.cmbcomments) Then
strWhere = strWhere & " (Inventory.Comments) Like '*" & Me.cmbcomments & "*' AND"
End If

If Not IsNull(Me.cmbdate) Then
strWhere = strWhere & " (Inventory.Date) Like '*" & Me.cmbdate & "*' AND"
End If

'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Pass the QueryDef to the query
Set qryDef = db.QueryDefs("Infractions")
qryDef.SQL = strsql & " " & strWhere & "" & strOrder

'remove the WHERE from the constraint
strWhere = Right(strWhere, Len(strWhere) - 6)
'open report
DoCmd.OpenReport stDocName, acViewPreview, , strWhere

End If
End Sub


Ok, my problem is that when I run this seach, I am prompted for Inventory.Emplyee_Number, Infraction.Infraction_Code, and Inventory.Infraction_Code.

The report it then gives me is blank. But! If I close it out, and then just open my query or report, it displays just what I'm wanting it to. I'm not sure if I'm getting in trouble the way I'm using WHERE to do my joins, or what, but I have been stare'n at this since early this morning and I can't figure it out. Anything jump out at anyone that I should be doing different? Thanks in advance!

-JB

 

[jberna2]

I removed the strWhere from the open report statement.

It is now working as intended. I figured if I filtered the query I didn't really need to put the same filter again in the report, and somehow it fixed itself.

My only problem now is that if I try to print out only one Employee_Number or Infraction_Code that I get a type mismatch error. I think the way I have my join worded is messing things up.

I usually just deal with inventory DB's, and they usually only have on table. I'm not sure how to join multiple tables on a value, like Employee_Number, then also use that field to narrow down search results and display only one employee by numbter.

 

[PHV]

For each NUMERIC field get rid of the single quotes in your where clause.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jberna2]

Thanks! that worked great.

-JB