Get the sum of strwhere table - Filter

[msamhouri]

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?

Private Sub cmdFilter_Click()
    
    Dim strWhere As String
    Dim lngLen As Long
    
    If Not IsNull(Me.txtFilterType) Then
        strWhere = strWhere & "([AcctGroupName] Like ""%" & Me.txtFilterType & "%"") AND "
    End If
    If Not IsNull(Me.txtText16) Then
        strWhere = strWhere & "([AccTypeName] Like ""%" & Me.txtText16 & "%"") AND "
    End If
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else
        strWhere = Left$(strWhere, lngLen)
        
        Me.Filter = strWhere
        Me.FilterOn = True
    
    End If
    Me.Requery

End Sub

Private Sub cmdReset_Click()
    
    Dim ctl As Control
    
   
    For Each ctl In Me.Section(acHeader).Controls
        Select Case ctl.ControlType
        Case acTextBox, acComboBox
            ctl.Value = Null
        Case acCheckBox
            ctl.Value = False
        End Select
    Next
    
    
    Me.FilterOn = False

End Sub

[Rabbit]

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.

[msamhouri]

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

[msamhouri]

Please i need the answer

[twinnyfo]

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.....

[msamhouri]

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

[zmbd]

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.

[Rabbit]

In that case, use the DSum function instead of the DCount function. There's no need for VBA.

[msamhouri]

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

[Rabbit]

You can just apply the same filter in the DSum that you do on the form.

[zmbd]

and to add to Rabbit...
which is why I mentioned that you should read thru the help file thru access or google the function.