469,071 Members | 2,003 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,071 developers. It's quick & easy.

Get the sum of strwhere table - Filter

I have the following codes which is show me a filter of data from a table, it works fine. My problem is when i run the query in the form i can't get the total based on the filtering data.

MY question is how i can put a text label shows me the total of the filtering data? i need the code for the label?

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdFilter_Click()
  2.  
  3.     Dim strWhere As String
  4.     Dim lngLen As Long
  5.  
  6.     If Not IsNull(Me.txtFilterType) Then
  7.         strWhere = strWhere & "([AcctGroupName] Like ""%" & Me.txtFilterType & "%"") AND "
  8.     End If
  9.     If Not IsNull(Me.txtText16) Then
  10.         strWhere = strWhere & "([AccTypeName] Like ""%" & Me.txtText16 & "%"") AND "
  11.     End If
  12.     lngLen = Len(strWhere) - 5
  13.     If lngLen <= 0 Then
  14.         MsgBox "No criteria", vbInformation, "Nothing to do."
  15.     Else
  16.         strWhere = Left$(strWhere, lngLen)
  17.  
  18.         Me.Filter = strWhere
  19.         Me.FilterOn = True
  20.  
  21.     End If
  22.     Me.Requery
  23.  
  24. End Sub
  25.  
  26. Private Sub cmdReset_Click()
  27.  
  28.     Dim ctl As Control
  29.  
  30.  
  31.     For Each ctl In Me.Section(acHeader).Controls
  32.         Select Case ctl.ControlType
  33.         Case acTextBox, acComboBox
  34.             ctl.Value = Null
  35.         Case acCheckBox
  36.             ctl.Value = False
  37.         End Select
  38.     Next
  39.  
  40.  
  41.     Me.FilterOn = False
  42.  
  43. End Sub
Oct 4 '12 #1
10 2517
Rabbit
12,516 Expert Mod 8TB
Please use code tags when posting code.

Total of what? the total number of filtered records? That's shown in the navigation bar at the bottom of the form.

Or do you mean total number of unfiltered records? That you can get using the DCount function.

Or do you mean some other total of which you have not defined? That you have to explain before any help can be offerred.
Oct 4 '12 #2
okay, I have a table which has the following :
No, AcctGroupName, AccTypeName, Amounts

I made the filter for this table based on AcctGroupName and AccTypeName, (it’s a continues table) it works perfect

My question is I need to add a textlabel in form footer to show me the total amount based on the filtered data.

Sorry about that but I am new in vba
Oct 4 '12 #3
Please i need the answer
Oct 4 '12 #4
twinnyfo
3,653 Expert Mod 2GB
As Rabbit mentioned, this may be solved with a DCount function in the text box to which you are referring. However, if you display the Navigation Buttons on your form, it should show the number of records produced by your filter without any additional text boxes or coding.....
Oct 4 '12 #5
Thank you for your reply.. But I don’t want to count the number of record which is I know that it shows in the navigation buttons. I need to create a text label in the footer shows the total (sum) of the amounts based on the filtered data.

For example

No, AcctGroupName, AccTypeName, Amounts
1 Car Expenses Expenses 500
2 Computer Expenses Expenses 400
3 Phone Expenses Expenses 1000
4 Rent Income Income 5000
5 Sales Income Income 7000

Now in the header of the form there are 2 textlabels, one to filter based on AcctGroupName and one based on AccTypeName, let say I want to filter based on AccTypeName = Income
So the results will be;
No, AcctGroupName, AccTypeName, Amounts
4 Rent Income Income 5000
5 Sales Income Income 7000

now I don’t have any problem for the form above.
My problem is that I want to add a textlabel in the footer of the form shows me the Amount’s total based on the filtered data, which is 12000 based on the example.
that’s it and thank you in advance for your help
Oct 4 '12 #6
zmbd
5,400 Expert Mod 4TB
In an unbound text control in the footer, set the record source to the DSUM() for the field in the details section of the form that you want the total for...
either google or [F1] in access and search for the function.
Oct 4 '12 #7
Rabbit
12,516 Expert Mod 8TB
In that case, use the DSum function instead of the DCount function. There's no need for VBA.
Oct 4 '12 #8
Thank you for all replies, but DSum formula shows the total based on the data in the table not the data in the form after the filter is applied. So in the previous example the total if I apply Dsum formula will be 3200, so this formula is reflecting the amounts in the table not in the form, because it should be 1200.
On the other hand, the sum formula works fine in the form but when you apply the filter it shows Error, so it doesn’t work either.
I am sure there is VBA code for that because both formulas (Dsum,Sum) are not working.
Thank you again for your help
Oct 4 '12 #9
Rabbit
12,516 Expert Mod 8TB
You can just apply the same filter in the DSum that you do on the form.
Oct 4 '12 #10
zmbd
5,400 Expert Mod 4TB
and to add to Rabbit...
which is why I mentioned that you should read thru the help file thru access or google the function.
Oct 5 '12 #11

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

7 posts views Thread by damjanu | last post: by
reply views Thread by richi | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.