Filter bug needs squishing...

[phual]

I've produced the following code:

Function FilterServiceUser()
 
Dim SUFilter As String
Dim varFilter_Surfacs As String
Dim varFilter_Surname As String
Dim varFilter_FirstName As String
Dim varFilter_DOB As String
 
'Set values, making sure to set them empty strings if the filed id empty (by adding a "" at the end)
varFilter_Surfacs = Forms!frmListServiceUsers!Filter_Surfacs & ""
varFilter_Surname = Forms!frmListServiceUsers!Filter_Surname & ""
varFilter_FirstName = Forms!frmListServiceUsers!Filter_FirstName & ""
varFilter_DOB = Forms!frmListServiceUsers!Filter_DOB & ""
 
'Set filter for Surfacs ONLY if Filter_Surfacs is set
If varFilter_Surfacs = "" Then
    SUFilter = ""
    Else
    SUFilter = "Surfacs Like '*" & varFilter_Surfacs & "*' AND "
End If
 
'Append filter for surname and first name whether set or not
SUFilter = SUFilter & "Surname Like '*" & varFilter_Surname & "*' AND "
SUFilter = SUFilter & "FirstName Like '*" & varFilter_FirstName & "*'"
 
'Append filter for DOB ONLY if Filter DOB is set
If Forms!frmListServiceUsers!Filter_DOB <> "" Then
    SUFilter = SUFilter & " AND DOB = #" & (varFilter_DOB) & "#"
End If
 
'For some reason, does not find DOB when the surfacs value is set (the actual
'value, not the filter). For the time being, DOB search is broken.
MsgBox (SUFilter)
 
'Apply filter
Forms!frmListServiceUsers!frmListServiceUsers_Sub.Form.Filter = SUFilter
Forms!frmListServiceUsers!frmListServiceUsers_Sub.Form.FilterOn = True
 
End Function

For some perculiar reason, the filter on DOB works fine when the surfacs ID (an alphnumeric string) is not set in the table, but returns no results when it is.

This is either going to be really simple, or fiendishly subtle. Any suggestions?

Stuart

 

[boblarson]

Is SurfacsID really text in the table or is it numeric?

 

[phual]

Really text. The format is a seven-digit number followed by a letter.

Stuart

 

[DCrake]

Spot the deliberate mistake

SUFilter = SUFilter & " AND DOB = #" & (varFilter_DOB) & "#"

Should this not be at the end of the line

SUFilter = SUFilter & " DOB = #" & (varFilter_DOB) & "# AND "

Each of your other statements adds the And to the end of the filter string. If you do not have anything in the varFilter_Surfacs the sql will read

...WHERE AND DOB = #01/01/2009#


syntactically incorrect. also if you do have something in it will read AND AND

David

 

[phual]

No, the syntax does work. Here are tested examples:

Filter_Surfacs = null
Filter_Surname = ith
Filter_FirstName = null
Filter_DOB = Null
Gives SUFilter = Surname Like '*ith*' AND FirstName Like '**'

Filter_Surfacs = 1111111A
Filter_Surname = null
Filter_FirstName = null
Filter_DOB = Null
Gives SUFilter = Surfacs Like '*1111111A*' AND Surname Like '**' AND FirstName Like '**'

Filter_Surfacs = 42
Filter_Surname = null
Filter_FirstName = null
Filter_DOB = 21/06/2005
Gives SUFilter = Surfacs Like '*42*' AND Surname Like '**' AND FirstName Like '**' AND DOB = #21/06/2005#

You see, it only includes Filter_Surfacs in the filter if it is not empty, ALWAYS includes both Filter_Surname and Filter_FirstName, and only includes Filter_DOB if it is not empty. The 'AND' is handled inconsistenly according to whether the next portion of the clause is a certainty or a possibility.

You might say that it's a bit inelegant, but it was easy to chuck out and debug... apart from my little problem that is.

Stuart

 

[DCrake]

Ok, but why include surname and/or forename filtering if one or both do not contain any values? This is giving Access additional processes to carry out when there is no need to.

David

 

[phual]

It's just the way that it evolved and hasn't been fixed yet because it would require me to think about elegant coding to decide if an operator was needed or not. At this point in time, I'm more interested in getting filter_DOB to work and then worry about making it nice and neat.

Speaking of which, any suggestions...

Stuart

 

[DCrake]

Just been playing about with the DateAdd and DateDiff functions

If we know that day one is 13/12/1899

In the immediate window I entered

?datediff("d",1,#03/06/1932#)

it returned 11753

However if you then type in

?dateadd("d",11753,1)


You get 06/03/1932

How weird.

Anyway if you get the julien number for the date and query the dob on that it should work.

David