Hi -

I'm writing a query on the fly and using it as a filter to open a report. The query works just fine (i.e. - opening the query).

When I try to open the report, I get "Error# 2498 An expression you entered is the wrong data type for one of the arguments" at the line calling OpenReport.

The code follows. Any suggestions?

Private Sub View1_Click()

On Error GoTo View1_Err

Dim dbs As DAO.Database
Dim qryQuery As DAO.QueryDef
Dim strDt1 As String
Dim strDt2 As String
Dim strUnit As String
Dim strDE1 As String
Dim strDE2 As String
Dim strDE3 As String
Dim strClass As String
Dim strOR As String
Dim strComma As String
Dim strSQL As String

Set dbs = CurrentDb
Set qryQuery = dbs.QueryDefs("qryQuery")

strComma = ","
strOR = " OR (AS400DE.[DE#])="

If IsNull(Me!Date11) Then
strDt1 = ">= " & Chr(35) & "1/1/1900" & Chr(35) & " "
Else
strDt1 = ">= " & Chr(35) & Me!Date11 & Chr(35) & " "
End If

If IsNull(Me!Date21) Then
strDt2 = "<= " & Chr(35) & Date & Chr(35) & " "
Else
strDt2 = "<= " & Chr(35) & Me!Date21 & Chr(35) & " "
End If

If IsNull(Me!Unit1) Then
strUnit = "Like '*' "
Else
strUnit = "= '" & Me!Unit1 & "' "
End If

If IsNull(Me!Class1) Then
strClass = "Like '*' "
Else
strClass = Me!Class1
End If

If IsNull(Me!DEs1) Then
strDE3 = "Like '*' "
Else
strDE1 = Me!DEs1
strDE2 = Replace(strDE1, strComma, strOR)
strDE3 = "=" & strDE2 & " "
End If

strSQL = "SELECT Inline.* FROM Inline INNER JOIN AS400DE ON Inline.Examiner = AS400DE.AS400DE WHERE " _
& "((Inline.ReviewDate) " & strDt1 _
& "And (Inline.ReviewDate)" & strDt2 _
& "AND (Inline.Unit)" & strUnit _
& "AND ((AS400DE.[DE#]) " & strDE3 _
& "));"

'Debug.Print strSQL

qryQuery.SQL = strSQL
'DoCmd.OpenQuery ("qryQuery")

DoCmd.OpenReport "Inline", acViewPreview, qryQuery, , acWindowNormal

Set dbs = Nothing
Set qryQuery = Nothing

View1_Exit:
Exit Sub

View1_Err:
If Err.Number = 3075 Then
MsgBox "Check to make sure you entered everything correctly and try again.", vbOKOnly, "Incorrect Form Entries"
Else
MsgBox "Error# " & Err.Number & ": " & Err.Description, vbCritical + vbOKOnly, "Error In Report Procedure"
Resume View1_Exit
End If

End Sub

Just for future reference (since a Google on this error turns up almost nothing) to others who may encounter this problem, I figured it out.

I was defining qryQuery as a QueryDef and then using it as the filter (which must be a variant) in the OpenReport method.

Changing all references to qryQuery in the code above to qdf and then defining a new variable qryQuery as a Variant allows the query to be used as the filter.

Thanks to all who at least looked at the problem. Corrected code follows:

Kayle

Private Sub View1_Click()

On Error GoTo View1_Err

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim strDt1 As String
Dim strDt2 As String
Dim strUnit As String
Dim strDE1 As String
Dim strDE2 As String
Dim strDE3 As String
Dim strClass As String
Dim strOR As String
Dim strComma As String
Dim strSQL As String
Dim qryQuery As Variant

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryQuery")

strComma = ","
strOR = " OR (AS400DE.[DE#])="

If IsNull(Me!Date11) Then
strDt1 = ">= " & Chr(35) & "1/1/1900" & Chr(35) & " "
Else
strDt1 = ">= " & Chr(35) & Me!Date11 & Chr(35) & " "
End If

If IsNull(Me!Date21) Then
strDt2 = "<= " & Chr(35) & Date & Chr(35) & " "
Else
strDt2 = "<= " & Chr(35) & Me!Date21 & Chr(35) & " "
End If

If IsNull(Me!Unit1) Then
strUnit = "Like '*' "
Else
strUnit = "= '" & Me!Unit1 & "' "
End If

If IsNull(Me!Class1) Then
strClass = "Like '*' "
Else
strClass = Me!Class1
End If

If IsNull(Me!DEs1) Then
strDE3 = "Like '*' "
Else
strDE1 = Me!DEs1
strDE2 = Replace(strDE1, strComma, strOR)
strDE3 = "=" & strDE2 & " "
End If

strSQL = "SELECT Inline.* FROM Inline INNER JOIN AS400DE ON Inline.Examiner = AS400DE.AS400DE WHERE " _
& "((Inline.ReviewDate) " & strDt1 _
& "And (Inline.ReviewDate)" & strDt2 _
& "AND (Inline.Unit)" & strUnit _
& "AND ((AS400DE.[DE#]) " & strDE3 _
& "));"

'Debug.Print strSQL

qdf.SQL = strSQL
'DoCmd.OpenQuery ("qryQuery")

DoCmd.OpenReport "Inline", acViewPreview, qryQuery, , acWindowNormal

Set dbs = Nothing
Set qryQuery = Nothing

View1_Exit:
Exit Sub

View1_Err:
If Err.Number = 3075 Then
MsgBox "Check to make sure you entered everything correctly and try again.", vbOKOnly, "Incorrect Form Entries"
Else
MsgBox "Error# " & Err.Number & ": " & Err.Description, vbCritical + vbOKOnly, "Error In Report Procedure"
Resume View1_Exit
End If

End Sub