Form Filter 2 text boxes

[SYG]

Hi,

I've created a split form and am trying to make a search field which incorporates 2 separate text boxes.

Now, I've made a button for on click event for a single unbound text box and used the code :

Me.Filter = "[sch_StoreName] = '" & Me.sch_filterText & "'"
Me.FilterOn = True
Me.Requery

That works perfectly

Now I've made another text box <sch_filterDate> and cannot get the one button to filter this text box at the same. Essentially creating two filters on the form at the same time. I've tried "

Me.Filter = "[sch_StoreName] = '" & Me.sch_filterText & "'" And [sch_Date] = ' "_ & Me.sch_filterDate & "'"
Me.FilterOn = True
Me.Requery

which doesn't work.... any ideas??

 

[VilaRestal]

Surround dates with # not ' (like strings)

Dealing with dates can be awkward, you may have to change the regional formatting of them. You may also have to take into account times of day:

1/1/2012 does not equal 1/1/2012 01:00:00

So, as a first draft of your code would be:


Me.Filter = "[sch_StoreName] = '" & Me.sch_filterText & "'" And [sch_Date] = #"_ & Me.sch_filterDate & "#"

in the events for both textboxes (if the user changes the Store Name filter after the date you want the filter to still include the date I assume).

One useful thing about dates though is that they are stored as numbers (the whole number is days, the fraction is times) so alternative way to compare them is:

Me.Filter = "[sch_StoreName] = '" & Me.sch_filterText & "'" And Int([sch_Date]) = "_ & Int(CDate(Me.sch_filterDate))

(That would ignore times in the table)

Also, you might want to test for null in the textboxes and not filter by that textbox if it is.

 

[VilaRestal]

If you make a sub you can reuse it any events that need to calculate and apply the filter:

Private Sub ApplyFilter()
    Dim sFilter As String
    If Nz(Me.sch_filterText,"") <> "" Then sFilter = "[sch_StoreName] = """ & Me.sch_filterText & """"
    If Nz(Me.sch_filterDate,"") <> "" Then
        If IsDate(Me.sch_filterDate) Then
            If Len(sFilter) > 0 Then sFilter = sFilter & " AND "
            sFilter = sFilter & "Int([sch_Date]) = " & Int(CDate(Me.sch_filterDate))
        End If
    End If
    Me.Filter = sFilter
    Me.FilterOn = (Len(sFilter) > 0)
End Sub

 

[SYG]

Yea i figured it was something tricky with the dates. But it also wasnt working when I needed to do just a text and plain number filter. Each one on its own works fine, but when I put the code into one line for a double or triple filter simultaneously it doesn't work.

The code you wrote still says mismatched error.

I'm going crazy just trying to back track and try different ways.

How would I create the sub as you said and reuse that filter code you wrote.

I am relatively new to this so bare with me.

Also, the two filter codes i am putting on an onClick event for a a single button, not in an event in the text box. not sure if that makes a difference

 

[Pat Hartman]

Text values must be enclosed in single or double quotes. Dates must be enclosed in pound signs (#). And numeric values are not delimited. So:
"my string"
#1/5/2012#
13456

I use double quotes to delimit names because names may include the single quote character - O'Doyle - for example or - St. Mary's Hospital - and enclosing these values in single quotes will result in unmatched delimiters because the quote in the string will terminate the string and the trailing quote will not have amatch:
"O'Doyle" is OK
'O'Doyle' is NOT

 

[VilaRestal]

If you put this code in the click event of the button:

    Dim sFilter As String
    If Nz(Me.sch_filterText,"") <> "" Then sFilter = "[sch_StoreName] = """ & Me.sch_filterText & """"
    If Nz(Me.sch_filterDate,"") <> "" Then
        If IsDate(Me.sch_filterDate) Then
            If Len(sFilter) > 0 Then sFilter = sFilter & " AND "
            sFilter = sFilter & "Int([sch_Date]) = " & Int(CSng(CDate(Me.sch_filterDate)))
        End If
    End If
    Me.Filter = sFilter
    Me.FilterOn = (Len(sFilter) > 0)

It should work (I'm assuming sch_Date is a DateTime field).

 

[SYG]

Thank you that worked!

For personal knowledge, what was the main difference from the code i was writing to the one that actually worked.. for me it wouldnt filter both criteria at the same time. for yours it worked like a charm. I imagine other people have the same problem as i had..is this a generic fix for it as in you've answered this same type thread with that code before

 

[VilaRestal]

The differences are:

Enclosing text values in quotes
(SQL needs to know where text starts and ends and it use single or double quotes for that. The "" in the string gets converted to " and the one after it ends the string in the compiler so doesn't go into the string. So for
Dim s As String
s = """"
s will have the value "
-two of the quotes start and end the string in the compiler, the pair in the middle get converted to one " in the string)

Converting dates to numbers
(A handy way to separate date from time components in date fields and values. Also means you don't have to enclose the value in # #, which you would have to do for dates formatted as text)

Some flow control handling null values in the textboxes
(If Nz(Me.sch_filterText,"") <> "" Then for example.)
and checking the text in the date textbox is actually a date
(If IsDate(Me.sch_filterDate) Then)

Building the string up as a variable is essential to the flow control and useful for debugging anyway.

And finally, setting the filter property automatically causes a requery.


It is an extremely common thing to be doing in Access, yes. Each time the code is a little different of course but the requirement is very often needed and always done in, roughly, this sort of way.

 

[SYG]

ok, so what if I wanted to add a third text box (which I do) that filters store number along with the other two filters. I believe i read that access already assumes = me.sch_filterNumber is an integer. So would i have to dim iFilter as integer or can i somehow add [sch_filterNumber]= " into there using another IF.

Dim iFilter as Integer
Dim sFilter As String
If Nz(Me.sch_filterText, "") <> "" Then sFilter = "[sch_StoreName] = """ & Me.sch_filterText & """"
If Nz(Me.sch_filterDate, "") <> "" Then iFilter = " [sch_StoreNumber] = me.sch_filterNumber"
If IsDate(Me.sch_filterDate) Then
If Len(sFilter) > 0 Then sFilter = sFilter & " AND "
sFilter = sFilter & "Int([sch_Date]) = " & Int(CSng(CDate(Me.sch_filterDate)))
End If
End If
Me.Filter = sFilter
Me.FilterOn = (Len(sFilter) > 0)

MY attempt:

Dim sFilter As String
If Nz(Me.sch_filterText, "") <> "" Then sFilter = "[sch_StoreName] = """ & Me.sch_filterText & """"
If Nz(me.sch_filterNumber,"")<> "" then
If Nz(Me.sch_filterDate, "") <> "" Then
If IsDate(Me.sch_filterDate) Then
If Len(sFilter) > 0 Then sFilter = sFilter & " AND "
sFilter = sFilter & "Int([sch_Date]) = " & Int(CSng(CDate(Me.sch_filterDate))) & " AND "
len(iFilter0 > 0 then ifilter =iFilter
End If
End If
Me.Filter = sFilter AND iFilter
Me.FilterOn = (Len(sFilter) > 0) and (Len(iFilter) > 0)


yea im not sure how to do it.

 

[VilaRestal]

    Dim sFilter As String
    If Nz(Me.sch_filterText,"") <> "" Then sFilter = "sch_StoreName = """ & Me.sch_filterText & """"
    If Nz(Me.sch_filterDate,"") <> "" Then
        If IsDate(Me.sch_filterDate) Then
            If Len(sFilter) > 0 Then sFilter = sFilter & " AND "
            sFilter = sFilter & "Int(sch_Date) = " & Int(CSng(CDate(Me.sch_filterDate)))
        End If
    End If
    If Nz(Me.sch_filterNumber,"") <> "" Then
        If IsNumeric(Me.sch_filterNumber) Then
            If Len(sFilter) > 0 Then sFilter = sFilter & " AND "
            sFilter = sFilter & "sch_StoreNumber = " & Me.sch_filterNumber
        End If
    End If
    Me.Filter = sFilter
    Me.FilterOn = (Len(sFilter) > 0)

 

[SYG]

Thank you, That worked just perfect. I only had to put Int( ) around sch_filterNumber during the debug.
Thanks for all the help.