runtime error 3075

C

Charno

I've copied some code to a search form that i'm using to filter by search a
table by certain criteria.

I think there are a few errors in the code, but currently i'm stuck on a
particular error.
with the form blank, if i click search then it lists all data in my
Chill_Intake_subform, however if i enter a haulier in the Haulier field on
the form i get the folloing error,

Run time error'3075'
Syntax error (missing operator) in quert '1=1 AND Chill Intake.[Haulier]='1".

this is the code i'm using
Private Sub Search_Click()
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String
Dim strError As String

strWhere = "1=1"

' If ASN Number
If Not IsNull(Me.ASN_Number) Then
'Create Predicate
strWhere = strWhere & " AND " & "Chill Intake.[ASN Number] = '" &
Me.ASN_Number & "'"

End If

' If Haulier
If Not IsNull(Me.Haulier) Then
'Add the predicate
strWhere = strWhere & " AND " & "Chill Intake.[Haulier] = '" &
Me.Haulier & "'"
End If

' If Bay
If Nz(Me.Bay) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Chill Intake.[Bay] = " & Me.Bay & ""
End If

' If ASN Available
If Nz(Me.ASN_Available) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Chill Intake.[ASN Available] = '" &
Me.ASN_Available & "'"
End If

' If Colleague
If Nz(Me.Colleague) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Chill Intake.[Colleague] = '" &
Me.Colleague & "'"
End If


' If DateFrom
If IsDate(Me.DateFrom) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Chill Intake.[Date] >= " &
GetDateFilter(Me.DateFrom)
ElseIf Nz(Me.DateFrom) <> "" Then
strError = cInvalidDateError
End If

' If DateTo
If IsDate(Me.DateTo) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Chill Intake.[Date] <= " &
GetDateFilter(Me.DateTo)
ElseIf Nz(Me.DateTo) <> "" Then
strError = cInvalidDateError
End If

' If Booking Ref
If Nz(Me.Booking_Ref) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "Chill Intake.[Booking Ref] Like '*"
& Me.Booking_Ref & "*'"
End If


If strError <> "" Then
MsgBox strError
Else
'DoCmd.OpenForm "Chill Intake", acFormDS, , strWhere, acFormEdit,
acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Me.Chill_Intake_subform.Form.Filter = strWhere
Me.Chill_Intake_subform.Form.FilterOn = True
End If
End Sub

It keeps highlighting an error at the line >
Me.Chill_Intake_subform.Form.Filter = strWhere
but i don't know why....... can anyone help?
 
D

Daryl S

Charno -

If Chill Intake is the name of a table, then it needs to have square
brackets around it (due to the space in the name), like this: [Chill Intake]

Try that and let us know. If you have any special characters or reserved
words in your table / field names, enclose them in square brackets to help
Access interperet them properly.

--
Daryl S


Charno said:
I've copied some code to a search form that i'm using to filter by search a
table by certain criteria.

I think there are a few errors in the code, but currently i'm stuck on a
particular error.
with the form blank, if i click search then it lists all data in my
Chill_Intake_subform, however if i enter a haulier in the Haulier field on
the form i get the folloing error,

Run time error'3075'
Syntax error (missing operator) in quert '1=1 AND Chill Intake.[Haulier]='1".

this is the code i'm using
Private Sub Search_Click()
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String
Dim strError As String

strWhere = "1=1"

' If ASN Number
If Not IsNull(Me.ASN_Number) Then
'Create Predicate
strWhere = strWhere & " AND " & "Chill Intake.[ASN Number] = '" &
Me.ASN_Number & "'"

End If

' If Haulier
If Not IsNull(Me.Haulier) Then
'Add the predicate
strWhere = strWhere & " AND " & "Chill Intake.[Haulier] = '" &
Me.Haulier & "'"
End If

' If Bay
If Nz(Me.Bay) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Chill Intake.[Bay] = " & Me.Bay & ""
End If

' If ASN Available
If Nz(Me.ASN_Available) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Chill Intake.[ASN Available] = '" &
Me.ASN_Available & "'"
End If

' If Colleague
If Nz(Me.Colleague) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Chill Intake.[Colleague] = '" &
Me.Colleague & "'"
End If


' If DateFrom
If IsDate(Me.DateFrom) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Chill Intake.[Date] >= " &
GetDateFilter(Me.DateFrom)
ElseIf Nz(Me.DateFrom) <> "" Then
strError = cInvalidDateError
End If

' If DateTo
If IsDate(Me.DateTo) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Chill Intake.[Date] <= " &
GetDateFilter(Me.DateTo)
ElseIf Nz(Me.DateTo) <> "" Then
strError = cInvalidDateError
End If

' If Booking Ref
If Nz(Me.Booking_Ref) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "Chill Intake.[Booking Ref] Like '*"
& Me.Booking_Ref & "*'"
End If


If strError <> "" Then
MsgBox strError
Else
'DoCmd.OpenForm "Chill Intake", acFormDS, , strWhere, acFormEdit,
acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Me.Chill_Intake_subform.Form.Filter = strWhere
Me.Chill_Intake_subform.Form.FilterOn = True
End If
End Sub

It keeps highlighting an error at the line >
Me.Chill_Intake_subform.Form.Filter = strWhere
but i don't know why....... can anyone help?
Click to expand...
 
C

Charno

Worked a treat thanks, i've now managed to get on and fix the rest of the
code to work with my form.
I'd been stuck on that bit for a day and it was something simple.

Cheers

Charno

Daryl S said:
Charno -

If Chill Intake is the name of a table, then it needs to have square
brackets around it (due to the space in the name), like this: [Chill Intake]

Try that and let us know. If you have any special characters or reserved
words in your table / field names, enclose them in square brackets to help
Access interperet them properly.

--
Daryl S


Charno said:
I've copied some code to a search form that i'm using to filter by search a
table by certain criteria.

I think there are a few errors in the code, but currently i'm stuck on a
particular error.
with the form blank, if i click search then it lists all data in my
Chill_Intake_subform, however if i enter a haulier in the Haulier field on
the form i get the folloing error,

Run time error'3075'
Syntax error (missing operator) in quert '1=1 AND Chill Intake.[Haulier]='1".

this is the code i'm using
Private Sub Search_Click()
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String
Dim strError As String

strWhere = "1=1"

' If ASN Number
If Not IsNull(Me.ASN_Number) Then
'Create Predicate
strWhere = strWhere & " AND " & "Chill Intake.[ASN Number] = '" &
Me.ASN_Number & "'"

End If

' If Haulier
If Not IsNull(Me.Haulier) Then
'Add the predicate
strWhere = strWhere & " AND " & "Chill Intake.[Haulier] = '" &
Me.Haulier & "'"
End If

' If Bay
If Nz(Me.Bay) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Chill Intake.[Bay] = " & Me.Bay & ""
End If

' If ASN Available
If Nz(Me.ASN_Available) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Chill Intake.[ASN Available] = '" &
Me.ASN_Available & "'"
End If

' If Colleague
If Nz(Me.Colleague) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Chill Intake.[Colleague] = '" &
Me.Colleague & "'"
End If


' If DateFrom
If IsDate(Me.DateFrom) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Chill Intake.[Date] >= " &
GetDateFilter(Me.DateFrom)
ElseIf Nz(Me.DateFrom) <> "" Then
strError = cInvalidDateError
End If

' If DateTo
If IsDate(Me.DateTo) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Chill Intake.[Date] <= " &
GetDateFilter(Me.DateTo)
ElseIf Nz(Me.DateTo) <> "" Then
strError = cInvalidDateError
End If

' If Booking Ref
If Nz(Me.Booking_Ref) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "Chill Intake.[Booking Ref] Like '*"
& Me.Booking_Ref & "*'"
End If


If strError <> "" Then
MsgBox strError
Else
'DoCmd.OpenForm "Chill Intake", acFormDS, , strWhere, acFormEdit,
acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Me.Chill_Intake_subform.Form.Filter = strWhere
Me.Chill_Intake_subform.Form.FilterOn = True
End If
End Sub

It keeps highlighting an error at the line >
Me.Chill_Intake_subform.Form.Filter = strWhere
but i don't know why....... can anyone help?
Click to expand...
Click to expand...
 

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

runtime Error.. cant assign value to this object 0
Search Form runtime error 2448 1
Subform Filter 5
Change filter source from table to query 4
Problem with Runtime Error 2448 4
Capture filtered records only on report 4
export the search results in the subform to an excel spreadsheet. 0
Error when Filtering on Unbound Form with Subform 7

Top