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
10 2634
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.
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
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.....
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 5,501
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.
In that case, use the DSum function instead of the DCount function. There's no need for VBA.
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
You can just apply the same filter in the DSum that you do on the form.
zmbd 5,501
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: damjanu |
last post by:
Hi All;
I need little help.
I have a datasheet form.
I allow user to do 'filter by selection'.
My form contains a column with values.
As user changes selections, I want to
calculate totals....
|
by: richi |
last post by:
Hello
I hava an OWC Pivot Table component in an aspx file. I am using VBScript to
set properties when the page is loaded. I am setting the order of a field
using the OrderedMembers property...
|
by: Ron S |
last post by:
After days of searching I finally an example that would work with my application, the only problem is after entering all of the code it is not working. Would someone be kind enough to take a look at...
|
by: Remaniak |
last post by:
Hi All,
I use the code below in my form to filter data.
But I also need to export the data to excel. So I'd like to create a temporary query based on the strWhere and export that query to excel....
|
by: Fran |
last post by:
I recently tried to use code for "Use a multi-select list box to
filter a report" from Allen Browne in my database. I was able to add
the code and adapt it to my needs, however I am getting an...
|
by: jcf378 |
last post by:
Hi all--
Does anyone have any insight as to how I might create a search form that allows a user to select criteria based on any related table in the whole database. The search form I have now only...
|
by: jcf378 |
last post by:
Hi all--
Does anyone have any insight as to how I might create a search form
that allows a user to select criteria based on any related table in
the whole database. The search form I have now only...
|
by: JpjVB |
last post by:
I'm having difficulty filtering a form using a multiselect list box when using some Allen Browne code. I get the error "Syntax Error (missing operator)in query expression '( IN (""Sydney"London)'. -...
|
by: Marie Gardner |
last post by:
Hi,
I want to create a form that filters a report that is between a date range, and includes only the data for the item selected in the combo box.
For example, I am creating a time manager...
|
by: Ian Anderson |
last post by:
Hello there,
SO i have the followign VB code in my continuous form...
'Purpose: This module illustrates how to create a search form, _
where the user can enter as many or few...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| |