Incorrect Code for a Unbound Search form

S

Spag

Hello,

I've written the below code to enable a search however it is coming back
with an error could anybody tell me where I'm going wrong.

Thanks
 
S

Spag

Spag said:
Hello,

I've written the below code to enable a search however it is coming back
with an error could anybody tell me where I'm going wrong.
Click to expand...
Thanks
Click to expand...


Here is the code:

Private Sub Multiformat_Search_Click()


Dim stDocName As String

Dim sqlfilter As String

If Not IsNull([FromDate]) And Not IsNull([ToDate]) Then
sqlfilter = "[multiformat job].date >= #" & Format([FromDate],
"mm/dd/yyyy") & "#"
sqlfilter = sqlfilter & " AND [multiformat job].date <= #" &
Format([ToDate], "mm/dd/yyyy") & "#"

Else
If Not IsNull([ResdptID]) Then
If sqlfilter = "" Then
sqlfilter = "(Multiformat Job]).ResdptID = " & [ResdptID]
Else
sqlfilter = sqlfilter & " AND (Multiformat Job]).ResdptID = " &
[ResdptID]
End If


Else
If Not IsNull([Tape Number]) Then
If sqlfilter = "" Then
sqlfilter = "[Multiformat Tape info].([Tape Number]) = " & [Tape
Number]
Else
sqlfilter = sqlfilter & " AND [Multiformat Tape Info].[Tape Number]
= " & ([Tape Number])

End If

Else
If Not IsNull([Tape Title]) Then
If sqlfilter = "" Then
sqlfilter = "[Multiformat job].([Tape Title]) = " & [Tape Title]

Else
sqlfilter = sqlfilter & " AND [Multiformat job].([Tape Title]) = " &
[Tape Title]

End If

Else
If Not IsNull([Tape Title]) Then
If sqlfilter = "" Then
sqlfilter = "([Multiformat job]).([Job Ref]) = " & ([Job Ref])

Else
sqlfilter = sqlfilter & " AND [Multiformat job].([Job Ref]) = " &
([Job Ref])

End If

Else
If Not IsNull(TechnicianID) Then
If sqlfilter = "" Then
sqlfilter = "[Multiformat job].(TechnicianID) = " & (TechnicianID)

Else
sqlfilter = sqlfilter & " AND [Multiformat job].(TechnicianID) = " &
(TechnicianID)

End If

Else
If Not IsNull([Tape Ref]) Then
If sqlfilter = "" Then
sqlfilter = "[Multiformat Tape Info].([Tape Ref]) = " & ([Tape Ref])

Else
sqlfilter = sqlfilter & " AND [Multiformat Tape Info].([Tape Ref]) =
" & ([Tape Ref])

End If

End If

Debug.Print sqlfilter

'DoCmd.Close

DoCmd.OpenForm "Multiformat Search Results", acNormal, , sqlfilter

Exit_btnCreateReport_Click:
Exit Sub

Err_btnCreateReport_Click:
MsgBox Err.Description
Resume Exit_btnCreateReport_Click
End Sub
 
P

Pieter Wijnen

Mainly stuff like this

([Multiformat job]).([Job Ref])
Should be
[Multiformat job].[Job Ref]

I personally would recommend getting rid of the spaces (you can always use
them in Captions)

ie
Multiformat_Job.Job_Ref

or simply

MultiformatJob.JobRef

HTH

Pieter

Spag said:
Spag said:
Hello,

I've written the below code to enable a search however it is coming back
with an error could anybody tell me where I'm going wrong.
Click to expand...
Thanks
Click to expand...


Here is the code:

Private Sub Multiformat_Search_Click()


Dim stDocName As String

Dim sqlfilter As String

If Not IsNull([FromDate]) And Not IsNull([ToDate]) Then
sqlfilter = "[multiformat job].date >= #" & Format([FromDate],
"mm/dd/yyyy") & "#"
sqlfilter = sqlfilter & " AND [multiformat job].date <= #" &
Format([ToDate], "mm/dd/yyyy") & "#"

Else
If Not IsNull([ResdptID]) Then
If sqlfilter = "" Then
sqlfilter = "(Multiformat Job]).ResdptID = " & [ResdptID]
Else
sqlfilter = sqlfilter & " AND (Multiformat Job]).ResdptID = " &
[ResdptID]
End If


Else
If Not IsNull([Tape Number]) Then
If sqlfilter = "" Then
sqlfilter = "[Multiformat Tape info].([Tape Number]) = " & [Tape
Number]
Else
sqlfilter = sqlfilter & " AND [Multiformat Tape Info].[Tape Number]
= " & ([Tape Number])

End If

Else
If Not IsNull([Tape Title]) Then
If sqlfilter = "" Then
sqlfilter = "[Multiformat job].([Tape Title]) = " & [Tape Title]

Else
sqlfilter = sqlfilter & " AND [Multiformat job].([Tape Title]) = "
&
[Tape Title]

End If

Else
If Not IsNull([Tape Title]) Then
If sqlfilter = "" Then
sqlfilter = "([Multiformat job]).([Job Ref]) = " & ([Job Ref])

Else
sqlfilter = sqlfilter & " AND [Multiformat job].([Job Ref]) = " &
([Job Ref])

End If

Else
If Not IsNull(TechnicianID) Then
If sqlfilter = "" Then
sqlfilter = "[Multiformat job].(TechnicianID) = " & (TechnicianID)

Else
sqlfilter = sqlfilter & " AND [Multiformat job].(TechnicianID) = "
&
(TechnicianID)

End If

Else
If Not IsNull([Tape Ref]) Then
If sqlfilter = "" Then
sqlfilter = "[Multiformat Tape Info].([Tape Ref]) = " & ([Tape
Ref])

Else
sqlfilter = sqlfilter & " AND [Multiformat Tape Info].([Tape Ref])
=
" & ([Tape Ref])

End If

End If

Debug.Print sqlfilter

'DoCmd.Close

DoCmd.OpenForm "Multiformat Search Results", acNormal, , sqlfilter

Exit_btnCreateReport_Click:
Exit Sub

Err_btnCreateReport_Click:
MsgBox Err.Description
Resume Exit_btnCreateReport_Click
End Sub
Click to expand...
 
Top