DSUM and functions

[fitshase]

I am creating a database for a sports club which has membership details and finance details.

The tables are:-

tblMembers (FullName, Address, Email, etc)
tblFinance (ID, TransactionCategory, TransactionDate, TransactionAmount, TransactionMember).

The tblFinance "TransactionMember" is linked to tblMembers "FullName".

I have 2 forms:-
frmTransactions
frmMembers

When I receive membership fees from a member, I open frmTransactions and input the details (e.g., Income, 22/07/2012, £20.00, John Smith).

In frmMembers, I have created a tab for "Member Finance" which I want to show a monthly breakdown of the fees paid. Therefore, I have created 12 unbound text boxes - one for each month.

In the VBA for the form, I have an update button linked to a function to carry out the DSUM function (I have only shown January's for now):-

JanuaryFeesPaid = DSum("[TransactionAmount]", "tblFinance", "[TransactionMember] = _
'" & Forms!frmMembers!FullName & "' and [TransactionDate] between_
 #01/01/2012# and #31/01/2012# and [TransactionCategory]='Income'")

Now, if I put in the following data in frmTransactions:-

Income | 22/01/2012 | £20.00 | John Smith
Income | 20/02/2012 | £15.00 | John Smith

The frmMembers finance tab will show £25.00 in the January fees box but then shows £35.00 in the February fees box. It is ignoring the date range and is keeping a running total.

Is there any way to have a total for each month?

 

[G37Sam]

Try changing: [TransactionDate] between #01/01/2012# and #31/01/2012#

To Month([TransactionDate]) = 1

Then you can have that 1 programmed later on by linking it to a combo etc..

 

[fitshase]

Sam,

That's perfect - works a treat now.

Cheers!

 

[G37Sam]

Glad I could help

Cheers,
Sam