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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kevin1478Author Commented:
Thank you. It is appreciated.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.