Building a forms filter doesn't work

[Peter Kinross]

I have a 'Score' I wish to filter on, the Scores can be any number from 0 to 20, or a dash '-'. to accommodate the dash, I have the numerals expressed as strings. This creates a problem when sorting. The sort I need to show Scores from 0 - 7, but exclude any with a dash plus 2 other criteria; see the filter string below. I put this as the form's filter in code. However it does nothing. And yes I do include: Me.FilterOn = True.
Nz([RoADuration])="0" OR Nz([RoADuration])="1" OR Nz([RoADuration])="2" OR Nz([RoADuration])="3" OR Nz([RoADuration])="4" OR Nz([RoADuration])<>"5" OR Nz([RoADuration])="6" OR Nz([RoADuration])="7" AND Nz([RoADuration])<>"_" AND Nz([DocsToClient])<> 0 AND Nz([Declined]) = 0

I pasted the filter string into the form's property sheet's filter, still nothing.
I'm stumped.

[SpeakEasy]

I'd be inclined to go with something more like

RoADuration In ("0","1","2","3","4","6","7") AND DOCSToClient=True AND Declined=False

[Peter Kinross]

Ha, ha, thanks SpeakE. That worked, although it loses some of its simplicity when building that in code:
Qt = Chr(34)
Fltr = "Nz(RoADuration) In (" & Qt & "0" & Qt & "," & Qt & 1 & Qt & "," & Qt & "2" & Qt & _
"," & Qt & "3" & Qt & "," & Qt & "4" & Qt & "," & Qt & "6" & Qt & "," & Qt & "7" & Qt & ")" _
& " AND Nz([RoADuration])<>" & Qt & "_" & Qt & " AND Nz([DocsToClient])<> 0 AND Nz([Declined]) = 0"
I guess I could have used BuildCriteria, but probably would not simplify it.
Thanks again.

[HansV]

Slightly simpler:

Fltr = "Nz(RoADuration) In ('0','1','2','3','4','5','6','7') AND Nz([RoADuration])<>'_' AND Nz([DocsToClient])<> 0 AND Nz([Declined]) = 0"

(SQL accepts single quotes as well as double quotes around string values)

[SpeakEasy]

AND Nz([RoADuration])<>'_'

Not convinced the above is needed.

Also not convinced we need NZ either (Null will never match)

[HansV]

AND Nz([RoADuration])<>'_' is definitely not needed.

[Peter Kinross]

Well I'll be!
The simpler the better is very applicable in this case.
I had played around with single quotes, but without using "IN", I could never get it right. errors every time, and with such a long string, I couldn't fix it, so resorted to Chr(34).
But your suggestions sure do work, from the bottom of my heart, thanks guys. You are HUGELY appreciated.

[Peter Kinross]

Problem: when I apply that filter, as I mentioned, it works beautifully. However when I use code to remove the filter, the filter remains.
I apply a lot of other filters to the form, they all also 'stick' when I try to remove them with the code below.

Code: Select all

With Me
    .Filter = vbNullString
    .FilterOn = True
    .OrderBy = "LastName"
    .Requery
    Debug.Print .Filter
End with

The Debug.Print shows the filter is still there. The form shows that the form is still filtered.
I can't see why it isn't working.

[Gasman]

Filters will 'stick' unless you set them to "" I have never set one to vbNullString.
However I have found just setting FilterOn to False works just as well.

If you set a filter and then just set Filteron = False and then look at the form in the design view, you will see the filter property still has a value.

However try setting the FilterOn to False in your code above.

[Peter Kinross]

Yep, that did it. Thanks GM. Does Gasman mean you have gas?
Previously I had also tried "" instead of vbNullString, it made no diff. Still can't work out why.