Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
RUNNING TOTAL
- Thread starter Angelique
- Start date
LonnieJohnson
Programmer
Not sure if by Monthly total you mean 1) a total for every month... or 2) a total displayed for the current month.
If you goal is to have a field that displays the current month, I was able to come up with this:
Private Sub Form_Current()
TheField = DSum("AmountField", "MyTable", "MyDate Like Left(Date(),2) & '*'"
' all of the above is one line.
End Sub
Where you would create a text field (unbound) and code the above. This will calc the sum for the current month.
2) If you were looking for the monthly total for every month all year long. Then you would have twelve unbound text fields and would code simularly:
Private Sub Form_Current()
txtMonthlyTotalJan = DSum("MyAmount", "DateAmount", "Month(MyDate) = 1"
'all of the above is one line.
End Sub
The part that says (MyDate) = 1 be for January, (MyDate) = 2 would be for February and so on. ljprodev@yahoo.com
Professional Development
MS Access Applications
If you goal is to have a field that displays the current month, I was able to come up with this:
Private Sub Form_Current()
TheField = DSum("AmountField", "MyTable", "MyDate Like Left(Date(),2) & '*'"
' all of the above is one line.
End Sub
Where you would create a text field (unbound) and code the above. This will calc the sum for the current month.
2) If you were looking for the monthly total for every month all year long. Then you would have twelve unbound text fields and would code simularly:
Private Sub Form_Current()
txtMonthlyTotalJan = DSum("MyAmount", "DateAmount", "Month(MyDate) = 1"
'all of the above is one line.
End Sub
The part that says (MyDate) = 1 be for January, (MyDate) = 2 would be for February and so on. ljprodev@yahoo.com
Professional Development
MS Access Applications
- Thread starter
- #3
- Thread starter
- #4
LonnieJohnson
Programmer
txtMonthlyTotalJan = DSum("MyAmount", "DateAmount", "Month(MyDate) = 1"
'all of the above is one line.
If you are talking about example #2, let me do a better job of explaining my varibles. Sorry for that.
"MyAmount" is the amount field in your table that we will be doing the sum on.
"DateAmount" is the name of MY table that holds the fields in question. (I should have explained that.
txtMonthlyTotalJan is one of the names of the twelve text boxes (name them as you like.)
The word Month is actually the name of a funtion.
Hope this makes it clearer. If not give me your field and table names and I will create the expression/example using your structure. B-)
MyDate is the date field in your table that determine the monthly grouping.
ljprodev@yahoo.com
Professional Development
MS Access Applications
'all of the above is one line.
If you are talking about example #2, let me do a better job of explaining my varibles. Sorry for that.
"MyAmount" is the amount field in your table that we will be doing the sum on.
"DateAmount" is the name of MY table that holds the fields in question. (I should have explained that.
txtMonthlyTotalJan is one of the names of the twelve text boxes (name them as you like.)
The word Month is actually the name of a funtion.
Hope this makes it clearer. If not give me your field and table names and I will create the expression/example using your structure. B-)
MyDate is the date field in your table that determine the monthly grouping.
ljprodev@yahoo.com
Professional Development
MS Access Applications
- Thread starter
- #6
- Status
Similar threads
- Locked
- Question
- Locked
- Question
