filter doesn't always filter

[Jean-Paul De Winter]

Hi,
have following situation:
a form with 3 listboxes
one has following entries: " ";"A";"B";"C"
I have following filter when I click one of the listboxes:

Private Sub klas_Click()
Me.Sub11.Form.FilterOn = True
Me.Sub11.Form.Filter = "klas='" & Forms![Rapporten ingeven]![Klas] & "'
and jaar='" & Forms![Rapporten ingeven]![jaar] & "' and afdeling='" &
Forms![Rapporten ingeven]![Afdeling] & "'"
Me.Sub11.Form.Refresh
End Sub

When I choose "A", "B" or "C" it all works perfectly
When I choose the " ", the filter doesn't return anything

What can be wrong?
Thanks
JP

 

[Allen Browne]

Firstly set the FilterOn property after the Filter property. The Refresh is
not needed.

The main issue, though is that the Filter string is not correctly formed.
Create a string so you can see if it is correct:

Private Sub klas_Click()
Dim strWhere As String

With Forms![Rapporten ingeven]
strWhere = "(klas = """ & ![Klas] & """) AND (jaar = """ & ![jaar] &
_
""") AND (afdeling = """ & ![Afdeling] & """"
End With

Debug.Print strWhere

Me.Sub11.Form.Filter = strWhere
Me.Sub11.Form.FilterOn = True
End Sub

When you run the code, if you open the Immediate window (Ctrl+G) you will be
able to see the string, and so figure out if it is not correct.

The code assumes that the 3 fields (klas, jaar, and afdeling) are all of
type Text. If they are of type Number or Currency, drop the extra quotes. If
Date/Time, use # as the delimiter.

 

[Jean-Paul De Winter]

Thanks for your kind reply.
Ctrl-G gives me:

(klas = "1") AND (jaar = " ") AND (afdeling = "Observatie")

The problem is with the " "
When I enter this in a query (with both other values) I get nothing
When I delete the " " and enter nothing in the criteria-field I get the
records I need

Don't know how to solve this one!!!

Thanks
JP

Op Sat, 9 Oct 2004 16:29:48 +0800 schreef Allen Browne

Firstly set the FilterOn property after the Filter property. The Refresh
is
not needed.

The main issue, though is that the Filter string is not correctly formed.
Create a string so you can see if it is correct:

Private Sub klas_Click()
Dim strWhere As String

With Forms![Rapporten ingeven]
strWhere = "(klas = """ & ![Klas] & """) AND (jaar = """ &
![jaar] &
_
""") AND (afdeling = """ & ![Afdeling] & """"
End With

Debug.Print strWhere

Me.Sub11.Form.Filter = strWhere
Me.Sub11.Form.FilterOn = True
End Sub

When you run the code, if you open the Immediate window (Ctrl+G) you
will be
able to see the string, and so figure out if it is not correct.

The code assumes that the 3 fields (klas, jaar, and afdeling) are all of
type Text. If they are of type Number or Currency, drop the extra
quotes. If
Date/Time, use # as the delimiter.

 

[Allen Browne]

Perhaps what you need to do is to build the filter string based only on
those text boxes where something has been entered.

This kind of thing:

Private Sub klas_Click()
Dim strWhere As String
Dim lngLen As Long

With Forms![Rapporten ingeven]
If Len(Trim(!Klas)) > 0 Then
strWhere = strWhere & "(klas = """ & ![Klas] & """) AND "
End If
If Len(Trim(!jaar)) > 0 Then
strWhere = strWhere & "(klas = """ & ![Klas] & """) AND "
End If
If Len(Trim(!Afdeling)) > 0 Then
strWhere = strWhere & "(afdeling = """ & ![Afdeling] & """) AND
"
End If
End With

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lenLen > 0 Then
strWhere = Left$(strWhere, lngLen)
Debug.Print strWhere
Me.Sub11.Form.Filter = strWhere
Me.Sub11.Form.FilterOn = True
Else
MsgBox "No criteria."
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for your kind reply.
Ctrl-G gives me:

(klas = "1") AND (jaar = " ") AND (afdeling = "Observatie")

The problem is with the " "
When I enter this in a query (with both other values) I get nothing
When I delete the " " and enter nothing in the criteria-field I get the
records I need

Don't know how to solve this one!!!

Thanks
JP

Op Sat, 9 Oct 2004 16:29:48 +0800 schreef Allen Browne

Firstly set the FilterOn property after the Filter property. The Refresh
is
not needed.

The main issue, though is that the Filter string is not correctly formed.
Create a string so you can see if it is correct:

Private Sub klas_Click()
Dim strWhere As String

With Forms![Rapporten ingeven]
strWhere = "(klas = """ & ![Klas] & """) AND (jaar = """ &
![jaar] &
_
""") AND (afdeling = """ & ![Afdeling] & """"
End With

Debug.Print strWhere

Me.Sub11.Form.Filter = strWhere
Me.Sub11.Form.FilterOn = True
End Sub

When you run the code, if you open the Immediate window (Ctrl+G) you
will be
able to see the string, and so figure out if it is not correct.

The code assumes that the 3 fields (klas, jaar, and afdeling) are all of
type Text. If they are of type Number or Currency, drop the extra
quotes. If
Date/Time, use # as the delimiter.

 

[Geof Wyght]

Jean-Paul,
Is jaar really equal to 4 spaces, or is it equal to
vbNullstring, or is it Null? Debug.Print Me.jarr.value
for that scenario. That should give you a clue. Or
Debug.Print Len(Me.jarr.value).
Geof.

-----Original Message-----
Thanks for your kind reply.
Ctrl-G gives me:

(klas = "1") AND (jaar = " ") AND (afdeling = "Observatie")

The problem is with the " "
When I enter this in a query (with both other values) I get nothing
When I delete the " " and enter nothing in the criteria-field I get the
records I need

Don't know how to solve this one!!!

Thanks
JP

Op Sat, 9 Oct 2004 16:29:48 +0800 schreef Allen Browne

Firstly set the FilterOn property after the Filter property. The Refresh
is
not needed.

The main issue, though is that the Filter string is not correctly formed.
Create a string so you can see if it is correct:

Private Sub klas_Click()
Dim strWhere As String

With Forms![Rapporten ingeven]
strWhere = "(klas = """ & ![Klas] & """) AND (jaar = """ &
![jaar] &
_
""") AND (afdeling = """ & ![Afdeling] & """"
End With

Debug.Print strWhere

Me.Sub11.Form.Filter = strWhere
Me.Sub11.Form.FilterOn = True
End Sub

When you run the code, if you open the Immediate window (Ctrl+G) you
will be
able to see the string, and so figure out if it is not correct.

The code assumes that the 3 fields (klas, jaar, and afdeling) are all of
type Text. If they are of type Number or Currency, drop the extra
quotes. If
Date/Time, use # as the delimiter.

 

[Jean-Paul De Winter]

Thanks to what Allen said I wrote:

With Forms![Rapporten ingeven]
If Me!jaar <> " " Then
strWhere = "(klas = """ & ![Klas] & """) AND (jaar = """ & ![jaar] &
""") AND (afdeling = """ & ![Afdeling] & """)"
Else
strWhere = "(klas = """ & ![Klas] & """) AND (afdeling = """ &
![Afdeling] & """)"
End If
End With

Problem solved
Thanks to you both of course
JP

Op Sat, 9 Oct 2004 05:42:08 -0700 schreef Geof Wyght

Jean-Paul,
Is jaar really equal to 4 spaces, or is it equal to
vbNullstring, or is it Null? Debug.Print Me.jarr.value
for that scenario. That should give you a clue. Or
Debug.Print Len(Me.jarr.value).
Geof.

-----Original Message-----
Thanks for your kind reply.
Ctrl-G gives me:

(klas = "1") AND (jaar = " ") AND (afdeling = "Observatie")

The problem is with the " "
When I enter this in a query (with both other values) I get nothing
When I delete the " " and enter nothing in the criteria-field I get the
records I need

Don't know how to solve this one!!!

Thanks
JP

Op Sat, 9 Oct 2004 16:29:48 +0800 schreef Allen Browne

Firstly set the FilterOn property after the Filter property. The Refresh
is
not needed.

The main issue, though is that the Filter string is not correctly formed.
Create a string so you can see if it is correct:

Private Sub klas_Click()
Dim strWhere As String

With Forms![Rapporten ingeven]
strWhere = "(klas = """ & ![Klas] & """) AND (jaar = """ &
![jaar] &
_
""") AND (afdeling = """ & ![Afdeling] & """"
End With

Debug.Print strWhere

Me.Sub11.Form.Filter = strWhere
Me.Sub11.Form.FilterOn = True
End Sub

When you run the code, if you open the Immediate window (Ctrl+G) you
will be
able to see the string, and so figure out if it is not correct.

The code assumes that the 3 fields (klas, jaar, and afdeling) are all of
type Text. If they are of type Number or Currency, drop the extra
quotes. If
Date/Time, use # as the delimiter.