Filter using multiple parameters

[WalkieTalkie]

Hi. I'm having trouble with filtering data in a form. I have unbound text boxes in the form header where the user can type one or more filter criteria, then a command button which applies the filter on the Click event. Here's my code:

Me.Filter = "[Surgeon] = '" & me.txtSurgeon & "' OR [Type] = '" & me.txtType & "' OR [Completed?] = " & me.ckCompleted & " OR [SurgeryDate] Between " & me.txtDate1 & " AND " & me.txtDate2

Me.FilterOn = true

My problem is that if the user leaves any of the criteria text boxes blank, then the filter returns no records. I presume this is because the blank text box actually means a null value, and there are no null values in the fields, so therefore the filter returns no records.

I've tried various things, none of which have worked. There must be a simple way to do this?? I appreciate any help!

 

[danvlas]

Between #" & me.txtDate1 & "# AND #" & me.txtDate2 & "#"

might help a little here in particular.


giving up the bad habit of using reserved words (Type) and special characters in field names (the question mark in [Completed?]

might help a lot in general.


Daniel Vlas
Systems Consultant

 

[MajP]

I personally always do this in a methodical way. This allows me to keep adding controls an more criteria. Normally I use multiselect list boxes so you could pick for example one or more surgeons.

1) I always build this as a seperate function so it can be used in many places in the application. It can be tested in the immediate window part by part
2) I build a string for each control, allowing flexibility to add more controls
3) encapsulate each criteria in parentheses
4) Put either an and or or at the end of each string

Public Function getWhere() As String
  Dim strSurgeon As String
  Dim strCompleted As String
  Dim strType As String
  Dim strSurgeryDate As String
  Dim frm As Access.Form
  Set frm = Forms("frmFilter")
  
  'get Surgery
  If Not Trim(frm.txtSurgeon & " ") = "" Then
    strSurgeon = "([Surgeon] = '" & frm.txtSurgeon & "') OR "
  End If
  
  'get Type
  If Not Trim(frm.txtType & " ") = "" Then
    strType = "([Type] = '" & frm.txtType & "') OR "
  End If
  
  'get Completed
  If Not IsNull(frm.ckCompleted) Then
    strCompleted = "([Completed?] = " & frm.ckCompleted & ") OR "
  End If
  
  'getSurgeryDate
  If IsDate(frm.txtDate1) And IsDate(frm.txtDate2) Then
    strSurgeryDate = "([SurgeryDate] Between #" & frm.txtDate1 & "# AND #" & frm.txtDate2 & "#) OR "
  End If
  
  'Add together
  getWhere = strSurgeon & strType & strCompleted & strSurgeryDate
  
  'Remove the hanging OR
  getWhere = Left(getWhere, Len(getWhere) - 4)
  
End Function

Results using different fields filled in.

?getWhere
([Surgeon] = 'Smith') OR ([Type] = 'Orthopedic') OR ([Completed?] = -1) OR ([SurgeryDate] Between #1/1/2009# AND #3/1/2009#)

?getWhere
([Surgeon] = 'Smith') OR ([Type] = 'Orthopedic') OR ([Completed?] = 0) OR ([SurgeryDate] Between #1/1/2009# AND #3/1/2009#)

?getWhere
([Surgeon] = 'Smith') OR ([Type] = 'Orthopedic') OR ([SurgeryDate] Between #1/1/2009# AND #3/1/2009#)

?getWhere
([Surgeon] = 'Smith') OR ([Type] = 'Orthopedic') OR ([Completed?] = -1)

?getWhere
([Type] = 'Orthopedic') OR ([Completed?] = -1)

I can error check these results to see if I am getting the correct string.

 

[MajP]

This is how you handle any multiselect listbox. Normally it is an AND within the multiselect and OR between the other controls

  dim varItem as variant
  ....
  'getSurgeon Multiselect Listbox
  If Not frm.lstSurgeon.ItemsSelected.Count = 0 Then
     For Each varItem In frm.lstSurgeon.ItemsSelected
        strSurgeon = strSurgeon & "[Surgeon] = '" & frm.lstSurgeon.ItemData(varItem) & "' AND "
     Next varItem
     'Remove the hanging AND
     strSurgeon = Left(strSurgeon, Len(strSurgeon) - 4)
     strSurgeon = "(" & strSurgeon & ") OR "
  End If

results.

?getWhere
([Surgeon] = 'Smith' AND [Surgeon] = 'Jones' AND [Surgeon] = 'Brown' ) OR ([Completed?] = -1) OR ([SurgeryDate] Between #1/3/2009# AND #1/20/2009#)

 

[SeeThru]

Watch for dates as well - they must be in US date format (mm/dd/yyyy), not european format.

If you feed the date froma text box, and your computer uses non-US date format, you can get some odd results.

SeeThru
Synergy Connections Ltd - Uk Telemarketing and Telesales Services and
Synergy Mobile Solutions - UK Mobile phones, land lines and call packages

 

[MajP]

Seems to be a lot of interests in resolving date strings and I was a little lazy. So this is how to do it properly. I always use this function when resolving dates for queries:

Function SQLDate(varDate As Variant) As String
    'Purpose:    Return a delimited string in the date format used natively by JET SQL.
    'Argument:   A date/time value.
    'Note:       Returns just the date format if the argument has no time component,
    '                or a date/time format if it does.
    'Author:     Allen Browne. allen@allenbrowne.com, June 2006 /Modified by MajP to handle other date forms
    If IsDate(varDate) Then
        If Int(CDate(varDate)) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function

Then in my above function

  'getSurgeryDate
  If IsDate(frm.txtDate1) And IsDate(frm.txtDate2) Then
    strSurgeryDate = "([SurgeryDate] Between " & SQLDate(frm.txtDate1) & " AND " & SQLDate(frm.txtDate2) & ") OR "
  End If

Then I could type any acceptable format in my filter form

In my filter form I typed
19 January 09
February 21, 2009

And still got the following:
([Surgeon] = 'Smith' AND [Surgeon] = 'Jones' ) OR ([SurgeryDate] Between #01/19/2009# AND #02/21/2009#)

 

[WalkieTalkie]

Thanks, guys, your help has saved me time and I've learned some stuff along the way.

One more question, though, now that its working so well! - now I want to also include the Patient name in the filter, and I want to use a text box rather than a list box to enter the criteria. I would like the user to be able to type only the first few letters of the name they're looking for. I'm sure the Like operator and a wildcard is what I should be using, but, again, just can't get it to work - I think maybe I'm getting my " and ' all mixed up and in the wrong order? (sigh).

  'get Patient
    If Not IsNull(frm.txtPatient) Then
        strPatient = "([Patient] Like '" & frm.txtPatient & "%') OR "
    End If

Thanks again in advance!

PS - lucky I'm better at my day job than I am at this stuff!

 

[MajP]

Access uses a *
Sql Server a %

 

[WalkieTalkie]

Thanks, but I've tried both - the % in desperation, more than anything else...

 

[MajP]

here is my completed code

Public Function getWhere() As String
  Dim strSurgeon As String
  Dim strCompleted As String
  Dim strType As String
  Dim strSurgeryDate As String
  Dim strPatient As String
  Dim frm As Access.Form
  Dim varItem As Variant
  Set frm = Forms("frmFilter")
  
  'get Surgery
  'If Not Trim(frm.txtSurgeon & " ") = "" Then
  '  strSurgeon = "([Surgeon] = '" & frm.txtSurgeon & "') OR "
  'End If
  
  'get Surgery Multiselect Listbox
  If Not frm.lstSurgeon.ItemsSelected.Count = 0 Then
     For Each varItem In frm.lstSurgeon.ItemsSelected
        strSurgeon = strSurgeon & "[Surgeon] = '" & frm.lstSurgeon.ItemData(varItem) & "' AND "
     Next varItem
     'Remove the hanging AND
     strSurgeon = Left(strSurgeon, Len(strSurgeon) - 4)
     strSurgeon = "(" & strSurgeon & ") OR "
  End If
  
  'get Patient
    If Not Trim(frm.txtPatient & " ") = "" Then
        strPatient = "([Patient] Like '" & frm.txtPatient & "*') OR "
    End If
  
  'get Type
  If Not Trim(frm.txtType & " ") = "" Then
    strType = "([Type] = '" & frm.txtType & "') OR "
  End If
  
  'get Completed
  If Not IsNull(frm.ckCompleted) Then
    strCompleted = "([Completed?] = " & frm.ckCompleted & ") OR "
  End If
  
  'getSurgeryDate
  If IsDate(frm.txtDate1) And IsDate(frm.txtDate2) Then
    strSurgeryDate = "([SurgeryDate] Between " & SQLDate(frm.txtDate1) & " AND " & SQLDate(frm.txtDate2) & ") OR "
  End If
  
  'Add together
  getWhere = strSurgeon & strPatient & strType & strCompleted & strSurgeryDate
  
  'Remove the hanging OR
  getWhere = Left(getWhere, Len(getWhere) - 4)
  
End Function

Function SQLDate(varDate As Variant) As String
    'Purpose:    Return a delimited string in the date format used natively by JET SQL.
    'Argument:   A date/time value.
    'Note:       Returns just the date format if the argument has no time component,
    '                or a date/time format if it does.
    'Author:     Allen Browne. allen@allenbrowne.com, June 2006 /Modified by MajP to handle other date forms
    If IsDate(varDate) Then
        If Int(CDate(varDate)) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function

And here is a Sql I can test in the immediate window.
?getWhere
([Surgeon] = 'Smith' ) OR ([Patient] Like 'john*')

That looks right to me. Can you post a generated sql string? This is why I do it in a function like this. You can test it in the immediate to see if you are getting a properly formed string.

 

[WalkieTalkie]

I'm sorry but I don't know how to generate an sql string in the immediate window! I'm still so new at this. Sounds like a handy thing to do, though. How do you do it? What should I type in the immediate window?

You've fixed my problem, though, I wasn't using the Trim function in strPatient. I've fixed that and now it works well.

Thanks so much for this. Its a great way to learn.

 

[MajP]

To test the above function I build a simple form with the text boxes. Then I type in the VBA immediate window

?getWhere
(or debug.print getWhere)

It then will print out the results of the function. That is how I was printing the results.

The check using the trim function will catch
Null
empty strings ("")
empty spaces

A null, empty string, or blank space are three different things that will appear as an empty text box.

 

[WalkieTalkie]

I kind of figured that must be what you did, but when I do it, nothing happens! The cursor just goes down to the next line. What am I doing wrong? Do I need to turn on something in the settings?

 

[MajP]

Nothing to turn on. Did you write this as a stand alone function like I did?

Public function getWhere() as string
code
...
getWhere = Left(getWhere, Len(getWhere) - 4)
end function

so the function returns as string, and the string is assigned in the last line of the function.

The ? is short hand for "debug.print". If you do not include the ? or the debug.print the function executes, but nothing is printed to the window.

 

[WalkieTalkie]

I copied the code you posted, so its exactly the same.

Make sure I am doing it right: I select or type the criteria I want in the form (eg Smith for surgeon), then I go to the immediate window and type ?getWhere and hit enter. Is that what I am supposed to be doing?

 

[MajP]

Yes that is it. Try running this program in the same module as the function

Public Sub testWhere()
MsgBox getWhere
Debug.Print getWhere
End Sub

Any chance your function is in the form's module not a standard module?

 

[WalkieTalkie]

Sorry for the delay responding - I've been busy playing with my Immediate Window!

Of course you were quite right - I had stupidly copied the code into the form's module, not a standard module. So I transferred it and hey presto!

Thanks again for your help, and thanks especially for sharing your code. Naturally I will acknowledge your input as its author in my application.

Thanks again