Filter a form between two dates

Hi,

I have a tabular form,  at the top I want to put two form fields to filter the form by.

The fields will be dtStart and dtEnd.  How can I use the filter commands with between.

I mean by using me.filter= and me.filteron=True

Thanks a lot
LVL 14
hhammashAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ala_frostyConnect With a Mentor Commented:
So ...

Me.Filter = "[DateField] BETWEEN #" & Me.dtStart & "# AND #" & Me.dtEnd & "#"
Me.FilterOn = True

HTH
0
 
Rey Obrero (Capricorn1)Commented:
On your query add the where clause

Where (TableA.Date) Between Me.dtStart And Me.dtEnd;
0
 
Rey Obrero (Capricorn1)Commented:
What is the Record Source of your form?
Is this the same form you will be using to display the result or something else?
0
Easily Design & Build Your Next Website

Squarespaceā€™s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
hhammashAuthor Commented:
Hi,

The record source of the form is a query,  and yes I'll be using the same form to display the results.

This is the filter code I am using on the form in the OnClick event of a combo box:

Me.Filter = "[InvoiceNo]='" & Me.cboInvoice & "'"
Me.FilterOn = True

When I select the cboInvoice I get the resutls on the same form.

I just want to add  two textboxes to filter the records between two dates.  Using the same colde  Me.filter= ?????
Me.filterOn=True.

I tried to use the between or => and <= but nothing worked.

Thanks
0
 
luckyrebelCommented:
try

Between [Me.dtStart] and [Me.dtEnd]

Works pretty well for me.  
0
 
Rey Obrero (Capricorn1)Commented:
In that case you need to set your filter this way;

Private Sub ApplyFilter()
Dim strFilter As String
strFilter=""

    If Me.cboInvoice<>"" And Not IsNull(Me.cboInvoice) Then
        If strFilter = "" Then
        strFilter = "[InvoiceNo]=" & Chr(34) & Me.cboInvoice & Chr(34) & " And"
        Else
        strFilter = strFilter & "[InvoiceNo]=" & Chr(34) & Me.cboInvoice & Chr(34) & " And"
        End If
    End If

    If Me.dtStart <>"" And Not IsNull(Me.dtStart) And Me.dtEnd <>"" And Not IsNull(Me.dtEnd) Then
        If strFilter = "" Then
        strFilter= " [DateField]= Between " & Me.dtStart & "  And " & Me.dtEnd & " And"
        Else
        strFilter= strFilter & " [DateField]= Between " & Me.dtStart & "  And " & Me.dtEnd & " And"
       End IF
    End If

    If strFilter <> "" Then
    strFilter = Left(strFilter, Len(strFilter) - 5)
    End If

    Me.Filter = strFilter
    Me.FilterOn = True
    Me.Refresh
   
End Sub

'Instead of placing the codes on Click event of the combo box
'Place the codes on the AfterUpdate  Event

Private Sub cboInvoice_AfterUpdate()
ApplyFilter
End Sub


0
 
ala_frostyCommented:
You wrote:
"I tried to use the between or => and <= but nothing worked."

Use these again, but you need to delimit the dates with '#' rather than the single apostrophe you used with the character data.
0
 
ala_frostyCommented:
You may run into some trouble with the time inclusion in the dates including and excluding some values that you think should be included or excluded, but this should get you on the right track.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.