MS Access Date Range Filter

Question

I am using a multi-item form that is linked to a table, I am using this multi-item form to store data from forms that I create. I am trying to find a way to filter the data in my multi-item form by date. I have two txt boxs and a button in the header to do this. One txt box is named "StartDate" the other is named "EndDate" and my button is called "Command56". I have the txt boxs set up to enter a date from a calendar, but I have having trouble figuring out how to enter my 2 dates and click the button and have it filter my data to only show the data that fits in that date range. Can anyone help me on this?

Answer 1

Ok, Not the way I would do it. But the code I gave you will filter for the data range.

Answer 2

I created an order form database and store my past orders in a datasheet style form in order for me to attach an “open form” button to each. I have created a keyword search in the headed of this form and want the option to filter the keyword results by way of a range of dates to simplify my results.

Answer 3

What do you define as a multi-item form? Almost all forms are multi-item. Forms are used to input data into records. Records are comprised of multiple fields. These fields are connected to controls on your form.

Every form has a filter property. That filter property contains a comparison between a field in your form's Recordsource and values that might be in that field. So all you need it to set the filter value and activate the filter.

Me.Filter = "mydatefield Between #" & Me.StartDate & # AND #" & Me.enddate & "#"

DoCmd.RunCommand acCmdApplyFilterSort

You might want to do some checking to make sure the dates are filled in or add a Clear button to clear the filter by setting

Me.Filter=""

Answer 4

Hi Kade,

You would typically use some VBA in the On Click event of your command button. The code might look like:

Dim strFilter as String

strFilter = "1=1 "

If Not IsNull(Me.StartDate) Then

strFilter = strFilter & " AND [Your Date Field] >=#" & Me.StartDate & "# "

End If

If Not IsNull(Me.EndDate) Then

strFilter = strFilter & " AND [Your Date Field] <=#" & Me.EndDate & "# "

End If

Me.Filter = strFilter

Me.FilterOn = True

Let us know if you still need some direction.