Help with Code - 13 Type Mismatch

[sali]

Hi Everyone,
Im creating a search form, where the users inputs various information in
this form and it opens another form with the results. The user can add all
fields on the search form, some fields or none. The information for this
data comes from 4 different tables. When i click on my Search Button
(command button), after I have entered the search criteria, I get a '13 Type
Mismatch' error. Can someone please help me out wiht this. Here is my
code.
(My recordset is commented out, not sure if i needed that or not) Thank you
in advance.


Private Sub Command18_Click()
On Error GoTo Err_Sub
Dim dbs As Database, rst As Recordset, stSQL As String, bAllowed As
Boolean, iAnswer As Variant
Dim stSqlDate As String, stSqlAll As String
Set dbs = CurrentDb
'Set rst = dbs.OpenRecordset("SELECT Case Information.CaseNumber, Case
Information.Agency, Case Information.AgencyNumber, " & _
' "Case Information.OffenseID, Case
Information.OffenseDate, Victims.FirstName " & _
' "AS Victims_FirstName, Victims.LastName AS
Victims_LastName, Suspects.FirstName " & _
' "AS Suspects_FirstName, Suspects.LastName
AS Suspects_LastName FROM (Case Information " & _
' "INNER JOIN (Case Analysis INNER JOIN
Victims ON Case " & _
'
"Analysis.SubmissionID=Victims.SubmissionID) ON Case
Information.CaseNumber=Case Analysis.CaseNumber) " & _
' "INNER JOIN Suspects ON Case
Analysis.SubmissionID=Suspects.SubmissionID", dbOpenDynaset)

stSQL = ("SELECT [Case Information].CaseNumber, [Case
Information].Agency, [Case Information].AgencyNumber, " & _
"[Case Information].OffenseID, [Case
Information].OffenseDate, [Victims].FirstName, " & _
"[Victims].LastName, [Suspects].FirstName,
[Suspects].LastName FROM ([Case Information] " & _
"INNER JOIN ([Case Analysis] INNER JOIN
Victims ON [Case Analysis].SubmissionID=Victims.SubmissionID) " & _
"ON [Case Information].CaseNumber=[Case
Analysis].CaseNumber) " & _
"INNER JOIN Suspects ON [Case
Analysis].SubmissionID=Suspects.SubmissionID")

If Nz(Me.txtCaseNumber, "") = "" Or Nz(Me.txtVictimFirstName, "") = ""
Then
iAnswer = MsgBox("You missed the Case Number and/or Victim's First
Name?", vbYesNo)
If iAnswer = vbNo Then
GoTo Exit_Sub
'Else

'Me.txtCaseNumber.SetFocus
' GoTo Skip_Data
End If
End If

If Nz(Me.txtCaseNumber, "") <> "" And Nz(Me.txtVictimFirstName, "") = ""
Then
stSQL = stSQL & "where [Case Information].CaseNumber = '" &
Me.txtCaseNumber & "'"
ElseIf Nz(Me.txtCaseNumber, "") = "" And Nz(Me.txtVictimFirstName, "")
<> "" Then
stSQL = stSQL & " where [Victims].FirstName = '" &
Me.txtVictimFirstName & "'"
ElseIf Nz(Me.txtCaseNumber, "") <> "" And Nz(Me.txtVictimFirstName, "")
<> "" Then
stSQL = stSQL & " where [Case Information].CaseNumber = '" &
Me.txtCaseNumber & "' And [Victims].FirstName = '" & Me.txtVictimFirstName &
"'"
End If


stSqlAll = "SELECT Case Information.CaseNumber, Case Information.Agency,
Case Information.AgencyNumber, " & _
"Case Information.OffenseID, Case
Information.OffenseDate, Victims.FirstName, " & _
"Victims.LastName, Suspects.FirstName, " & _
"Suspects.LastName FROM (Case Information "
& _
"INNER JOIN (Case Analysis INNER JOIN
Victims ON Case " & _
"Analysis.SubmissionID=Victims.SubmissionID)
ON Case Information.CaseNumber=Case Analysis.CaseNumber) " & _
"INNER JOIN Suspects ON Case
Analysis.SubmissionID=Suspects.SubmissionID"
Skip_Data:
Set rst = dbs.OpenRecordset(stSQL)

If rst.EOF Then
iAnswer = MsgBox("There was no data filtered, Would You Like to see
all the entries? Press No to Try Again", vbYesNo)
If iAnswer = vbNo Then
Me.txtCaseNumber.SetFocus
GoTo Exit_Sub
Else
DoCmd.OpenForm "Search Form", acNormal, , , acFormEdit,
acWindowNormal, stSqlAll
GoTo Exit_Sub
End If
End If

DoCmd.OpenForm "Search Form", acNormal, , , acFormEdit, acWindowNormal,
stSQL

DoCmd.Close acForm, "Search"

Exit_Sub:
Set dbs = Nothing
Set rst = Nothing
Exit Sub

Err_Sub:
MsgBox Err.Number & " " & Err.Description
GoTo Exit_Sub
End Sub

 

[Dan Artuso]

Hi,
That's a lot of code to go through and newsreader line wrapping makes it
even more tedious. Can you tell us which line is generating the error?
Remove your error handler and let the code break on the offending line and then
tell us which one it is.