Problem with bit based form filtering in MS Access 2003

[JamesK]

As most people are aware, Microsoft Access and Microsoft SQL Server 2000 have
different mechanisms for the storage to bit data in records. Access utilizes
0 (false) and -1 (true), whereas SQL Server utilizes 0 (false) and 1 (true).
A problem arises when using Access as a “front end†to a SQL Server based
catalog because upon linking the tables, Access “converts†the bit data’s
true representation in the tables as -1. While this would seem logical it
posses several problems with form functions. The primary problem is that a
filter by form using a check box control where the form’s record source is a
linked table either a) fails to return any records or b) returns all records
in the table. This is because the filter created by Access attempts to match
the bit field data to -1. Which would seem logical as it is displayed in
Access as -1 if you open the table directly. But the “real†value is 1, and
the when applying the filter Access appears to obtain the resulting result
set by reading the “real†data.

The solution in Microsoft Access XP (2002) [through SP2] was to “edit†the
form’s Filter in the OnApplyFilter event and set all occurrences of -1 to 1
for any bit fields utilized on that form.

The following code works in Access XP (2002):

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)

Dim strFilter As String
strFilter = Me.Filter

If ApplyType = acApplyFilter And strFilter <> "" Then

strFilter = parseYesNoFilterOnApply(strFilter, "rCapRR")
strFilter = parseYesNoFilterOnApply(strFilter, "rCapTrdr")
strFilter = parseYesNoFilterOnApply(strFilter, "rCapSpvr")
strFilter = parseYesNoFilterOnApply(strFilter, "rCapSolicitor")
strFilter = parseYesNoFilterOnApply(strFilter, "rCapIAR")
strFilter = parseYesNoFilterOnApply(strFilter, "rCapAgent")
strFilter = parseYesNoFilterOnApply(strFilter, "rCapPM")
strFilter = parseYesNoFilterOnApply(strFilter, "rCapOther")
strFilter = parseYesNoFilterOnApply(strFilter, "rCapOther2")

strFilter = parseYesNoFilterOnApply(strFilter, "rHoldC")
strFilter = parseYesNoFilterOnApply(strFilter, "rHoldCAg")
strFilter = parseYesNoFilterOnApply(strFilter, "rAdCompHold")
strFilter = parseYesNoFilterOnApply(strFilter, "rPMCompHold")

strFilter = parseYesNoFilterOnApply(strFilter, "rOmitFromMailings")
strFilter = parseYesNoFilterOnApply(strFilter, "rOmitFromEMails")

strFilter = parseYesNoFilterOnApply(strFilter, "rReqDel")

End If

If ApplyType = acApplyFilter Then Me.Filter = strFilter

End Sub

Public Function parseYesNoFilterOnApply(strIn As String, _
strField As String) As String

Dim strFilter, strLookup As String
Dim intLoc1 As Long

strFilter = strIn
strLookup = strField & "=-1"

intLoc1 = InStr(1, strFilter, strLookup, vbTextCompare)
If intLoc1 > 0 Then strFilter = Left(strFilter, intLoc1 - 1) &
Replace(strFilter, "-", "", intLoc1, 1)

parseYesNoFilterOnApply = strFilter

End Function

In Microsoft Access XP (2002) this results in a properly returned filtered
record set. The problem, in Microsoft Access 2003 [through SP1] this code
returns an empty record set. The interesting thing is that if you remove the
filter utilizing the toolbar and then immediately reapply the filter (again
utilizing the toolbar) the properly filtered record set is returned.

Any help with this issue would be greatly appreciated as I have not been
able to figure out an alternative to use in Access 2003.