Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Filter a form between two dates

Posted on 2004-04-19
8
Medium Priority
?
1,278 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 10862351
On your query add the where clause

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

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 10862410
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
ID: 10862497
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Expert Comment

by:luckyrebel
ID: 10862581
try

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

Works pretty well for me.  
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 10862643
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
ID: 10863403
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 2000 total points
ID: 10863442
So ...

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

HTH
0
 
LVL 7

Expert Comment

by:ala_frosty
ID: 10863465
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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

636 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