bug in filter

[lilbit27]

filter error:

One of my filters will not work the first time I run it but will when I
clear and re run.

Here is the code.

Dim stFilter As String
If NZ(Me.CboOpid) <> "" Then stFilter = stFilter & " And [opid] = '" &
Me.CboOpid & "'"
If NZ(Me.CboExclsn) <> "" Then stFilter = stFilter & " And [Excd] = '"
& Me.CboExclsn & "'"
If NZ(Me.CboProd) <> "" Then stFilter = stFilter & " And [ProdCd] = '"
& Me.CboProd & "'"
If NZ(Me.txtPolicy) <> "" Then stFilter = stFilter & " And [polNbr] =
'" & Me.txtPolicy & "'"
If Len(stFilter) > 0 Then stFilter = right(stFilter, Len(stFilter) - 4)
Me.FPastDues.Form.Filter = stFilter
Me.FPastDues.Form.FilterOn = True

When I try to find out the error I get this

me.fpastdues.form.filter="] = 'CC02'"
CC02 is one of the opids that I selected from a combo box to filter on.


Am I missing a quote or have it in the wrong place.

 

[kingston via AccessMonster.com]

Try this:

Dim stFilter As String
If NZ(Me.CboOpid) <> "" Then
stFilter = stFilter & " And [opid] = '" & Me.CboOpid & "'"
End If
If NZ(Me.CboExclsn) <> "" Then
stFilter = stFilter & " And [Excd] = '" & Me.CboExclsn & "'"
End If
If NZ(Me.CboProd) <> "" Then
stFilter = stFilter & " And [ProdCd] = '" & Me.CboProd & "'"
End If
If NZ(Me.txtPolicy) <> "" Then
stFilter = stFilter & " And [polNbr] = '" & Me.txtPolicy & "'"
End If
If Len(stFilter) > 0 Then
stFilter = right(stFilter, Len(stFilter) - 5)
End If
Me.FPastDues.Form.Filter = stFilter
Me.FPastDues.Form.FilterOn = True

filter error:

One of my filters will not work the first time I run it but will when I
clear and re run.

Here is the code.

Dim stFilter As String
If NZ(Me.CboOpid) <> "" Then stFilter = stFilter & " And [opid] = '" &
Me.CboOpid & "'"
If NZ(Me.CboExclsn) <> "" Then stFilter = stFilter & " And [Excd] = '"
& Me.CboExclsn & "'"
If NZ(Me.CboProd) <> "" Then stFilter = stFilter & " And [ProdCd] = '"
& Me.CboProd & "'"
If NZ(Me.txtPolicy) <> "" Then stFilter = stFilter & " And [polNbr] =
'" & Me.txtPolicy & "'"
If Len(stFilter) > 0 Then stFilter = right(stFilter, Len(stFilter) - 4)
Me.FPastDues.Form.Filter = stFilter
Me.FPastDues.Form.FilterOn = True

When I try to find out the error I get this

me.fpastdues.form.filter="] = 'CC02'"
CC02 is one of the opids that I selected from a combo box to filter on.

Am I missing a quote or have it in the wrong place.

 

[lilbit27]

I still get back blanks when I first try to run the filter but then
when I hit clear and try to run it again it works fine.

It's a main\subform. The main form is filtering the subform. When the
open the main form they click on a command button that makes a combobox
visible then they make a selection. once that pick a value a set of
combobox become available that will allow them to do the filter I
listed below.

Am I not turning on the filter in a certain place.

Try this:

Dim stFilter As String
If NZ(Me.CboOpid) <> "" Then
stFilter = stFilter & " And [opid] = '" & Me.CboOpid & "'"
End If
If NZ(Me.CboExclsn) <> "" Then
stFilter = stFilter & " And [Excd] = '" & Me.CboExclsn & "'"
End If
If NZ(Me.CboProd) <> "" Then
stFilter = stFilter & " And [ProdCd] = '" & Me.CboProd & "'"
End If
If NZ(Me.txtPolicy) <> "" Then
stFilter = stFilter & " And [polNbr] = '" & Me.txtPolicy & "'"
End If
If Len(stFilter) > 0 Then
stFilter = right(stFilter, Len(stFilter) - 5)
End If
Me.FPastDues.Form.Filter = stFilter
Me.FPastDues.Form.FilterOn = True

filter error:

One of my filters will not work the first time I run it but will when I
clear and re run.

Here is the code.

Dim stFilter As String
If NZ(Me.CboOpid) <> "" Then stFilter = stFilter & " And [opid] = '" &
Me.CboOpid & "'"
If NZ(Me.CboExclsn) <> "" Then stFilter = stFilter & " And [Excd] = '"
& Me.CboExclsn & "'"
If NZ(Me.CboProd) <> "" Then stFilter = stFilter & " And [ProdCd] = '"
& Me.CboProd & "'"
If NZ(Me.txtPolicy) <> "" Then stFilter = stFilter & " And [polNbr] =
'" & Me.txtPolicy & "'"
If Len(stFilter) > 0 Then stFilter = right(stFilter, Len(stFilter) - 4)
Me.FPastDues.Form.Filter = stFilter
Me.FPastDues.Form.FilterOn = True

When I try to find out the error I get this

me.fpastdues.form.filter="] = 'CC02'"
CC02 is one of the opids that I selected from a combo box to filter on.

Am I missing a quote or have it in the wrong place.

 

[Klatuu]

This should clean it up a bit for you. Note the function at the end of the
code that puts the And in where needed. Also, Single Line If statments are a
bad habilt. Note how will indented and spaced code is easy to read and
understand.

Dim stFilter As String

If Not IsNull(Me.CboOpid) Then
stFilter = stFilter & "[opid] = '" & Me.CboOpid & "'"
End If

If Not IsNull(Me.CboExclsn) Then
strFilter = AddAnd(stFilter)
stFilter = "[Excd] = '" & Me.CboExclsn & "'"
End If

If Not IsNull(Me.CboProd) Then
strFilter = AddAnd(stFilter)
stFilter = stFilter & "[ProdCd] = '" & Me.CboProd & "'"
End If

If Not IsNull(Me.txtPolicy) Then
strFilter = AddAnd(stFilter)
stFilter = stFilter & "[polNbr] = '" & Me.txtPolicy & "'"
End If

Me.FPastDues.Form.Filter = stFilter
Me.FPastDues.Form.FilterOn = True
*****************
Private Function AddAnd(strFilterString As String) As String
If Len(stFilterString) = 0 Then
AddAnd = ""
Else
AddAnd = stFilterString & " And "
End If
End Function

 

[kingston via AccessMonster.com]

Could you please go through the sequence of events in more detail? When does
it work and when does it not work? What happens exactly when you hit clear?
Try to display the filter string using MsgBox to verify it. If it works, the
string is probably correct, and it might just be a matter of the subform
having been saved with a filter that should be cleared.

I still get back blanks when I first try to run the filter but then
when I hit clear and try to run it again it works fine.

It's a main\subform. The main form is filtering the subform. When the
open the main form they click on a command button that makes a combobox
visible then they make a selection. once that pick a value a set of
combobox become available that will allow them to do the filter I
listed below.

Am I not turning on the filter in a certain place.

Try this:

[quoted text clipped - 47 lines]