Solved

Filter a form between two dates

Posted on 2004-04-19
8
918 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:hhammash
8 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
On your query add the where clause

Where (TableA.Date) Between Me.dtStart And Me.dtEnd;
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
 
LVL 14

Author Comment

by:hhammash
Comment Utility
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
 

Expert Comment

by:luckyrebel
Comment Utility
try

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

Works pretty well for me.  
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
 
LVL 7

Expert Comment

by:ala_frosty
Comment Utility
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
 
LVL 7

Accepted Solution

by:
ala_frosty earned 500 total points
Comment Utility
So ...

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

HTH
0
 
LVL 7

Expert Comment

by:ala_frosty
Comment Utility
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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now