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.
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.
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 ...
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 ...
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you. It is appreciated.
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
Regards