Is that a bug in MS Access 2007 ?

[odrap]

On a form with a few columns, i have on top of each column a textbox where the user can fill in a value that will serve as filter for the recordsource of the form.
The tag of each of these textboxes has as value the name of the controlsource of the field of that column.
In the afterupdate event of these textboxes , the function "Setfilt" is called what result in a filtering of the recordsource of the form in accordance with the value entered in the textbox. See code below.

In Access 2.0 this works fine. I converted the program to Access 2007. There the program works fine for all columns, except for columns of datatype True/False ( in Dutch = JA/NEE.)
When the value "JA" or the Value "NEE" is entered in the textbox above such a column i get the following message with as title:"Parameterwaarde opgeven" what translated means : Enter Parameter value"
Below the title stands : "ONWAAR" ( = FALSE) ( e.g when entered "NEE")
and then a textbox to enter the value.
Since the code works fine for all columns except for the true/false columns, I wonder if this is a bug in MS Access 2007?

Private Function getwhere(F, v As Variant) As String

On Error Resume Next
Dim N As Integer
If Len(Trim(v)) < 1 Then v = ""
If v <> "" Then
Select Case VarType(v)
Case 8 'string
If F = "[Factuurnr]" Then
getwhere = " And " & F & " Like """ & v + """"
ElseIf F = "[jan]" Or F = "[feb]" Or F = "[mrt]" Or F = "[apr]" Or F = "[mei]" Or F = "[jun]" Or F = "[jul]" Or F = "[aug]" Or F = "[sep]" Or F = "[oct]" Or F = "[nov]" Or F = "[dec]" Or F = "[stock]" Then
getwhere = " And " & F & "= " & v
ElseIf F = "[klntID]" Then
getwhere = " And " & F & " =" & Str$(v)
Else
getwhere = " And " & F & " Like """ & v + "*"""
End If
Case 7 'date
getwhere = " And " & F & " = " & changedate(v)
Case Else
getwhere = " And " & F & " =" & Str$(v)
End Select
Else
getwhere = ""
End If
End Function

Function setfilt()

Dim Where As String, frm As Form
Dim N As Integer
On Error GoTo ErrorHandler
Set frm = Screen.ActiveForm
On Error Resume Next
Dim ct As Integer
If frm.Name = "OverzichtOrders" Then
N = 10
ElseIf frm.Name = "frmOverzichtgeleverdeArtikelen" Then
N = 15
Else
N = 12
End If
For ct = 1 To N
Where = Where & getwhere(frm("Value" & LTrim$(Str$(ct))).Tag, frm("Value" & LTrim$(Str$(ct))))
Next
If Where <> "" Then
Where = Mid(Where, 6)
frm.RecordSource = "Select * from " & frm.Name & " Where " & Where & ";"
Else
frm.RecordSource = frm.Name
End If

Afsluiten:
On Error GoTo 0
Exit Function

ErrorHandler:
Mededeling = foutbericht("setfilt", "modSystem", Err)
Resume Afsluiten
End Function

 

[MarkK]

I think this is a datatyping problem. Does a Dutch version of Access or SQL recognize Ja or Nee as boolean datatypes? Your code checks for strings and dates but not for booleans.
I bet your code handles these values as strings, and since they don't match any of your listed values, they get handled by ...

getwhere = " And " & F & " Like """ & v + "*"""

... and this'll never be true.

But I'm guessing. Understanding someone else's code is not trivial.
I'd set a breakpoint here ...

frm.RecordSource = "Select * from " & frm.Name & " Where " & Where & ";"

... and test that SQL and make sure you're getting what you expect.
Cheers,

 

[odrap]

thank you very much for the advice
greetings from Brugge - Belgium