[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 708
  • Last Modified:

Access 2003 - Filter a form

Hello Experts,

I have a form named frm_ customer_record  which includes the fields:

Company name (c_name)
Category (category)
Value of order (value)
Cost of order (cost)
Date (order_date)

At the moment it just lists records  no filters.

The results I would like are grouped by the company name and filtered by the category which is controlled by a list box.

The results on the form, for each company name and filter, would include a total order value and a total cost value.

Also, when launching the form I would like the user to input a date range (from date) and (to date) so the results would also relate to the date range.  If the input dates could also appear in the form (as text) that would be a bonus.

Any help would be appreciated. Thank you.
  • 2
  • 2
1 Solution
If TextFilter <> "" Then
    If FilterText <> "" Then
        FilterText = FilterText & " And "
    End If
    'Debug.Print Mid$(CmboStage, 6)
    FilterText = FilterText & " [FilterField] = '*" & TextFilter  & "'*"
End If

    Forms![frm_ customer_record].Filter = FilterText
    Forms![frm_ customer_record].FilterOn = True
    Forms![frm_ customer_record].Requery

This might get you started

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You must enclose Dates in hash marks (#), so something like this:

Function BuildFilter()
Dim sFilter As String
'/must have a Start and End date
If Nz(Me.StartDate,0)=0 or Nz(Me.EndDate,0)=0 Then Exit Function

sFilter = "[Date] BETWEEN #" & Me.StartDAte & "# AND #" & Me.EndDate & "#"
'/now turn the filter on
Me.Filter = sFilter
Me.FilterOn = True
'/to display it, add a lable to your form named lblFilter
Me.lblFilter.Caption = "Showing Records between " & Me.StartDAte & " AND " & Me.EndDate
End Function

This would be code running on the frm_customer_record Form ...
kevin1478Author Commented:
Thank you both for your help.

Im tying to get the date filter working first and I've been working on the date code kindly provided by LSM consulting, but Im having trouble understanding what is happening and where I place the code.

Is the code a complete solution to the date problem or do I need to make some changes? I ask as I cannot see any reference to the order_date field.

Also, Ive cut/pasted into the form on open event procedure but I get a compile error.

Sorry to ask again but Ive not done much VBA and this is a steep learning curve.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You will need to change the fields to match those in your table/form ...

sFilter = "[Date] BETWEEN #" & Me.StartDAte & "# AND #" & Me.EndDate & "#"

I mistakenly thought that "Date" was your datefield, but instead it is apparently order_date ... so:

sFilter = "[order_date] BETWEEN #" & Me.StartDAte & "# AND #" & Me.EndDate & "#"

Me.STartDAte and Me.EndDate refer to the FORM controls where your users enter the dates ... you'll need to change those to show the actual names of the textboxes on your form.
kevin1478Author Commented:
Thank you. It is appreciated.

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now