running sum on subform

[JohnE]

I have a subform that is a checkbook style entry that acctg wants to use. In
the subform (the many to the main form one) there is a balance field in the
footer showing the what the deposit minus withdraw balance, as a whole. They
would like another field showing what the balance is from the beginning to a
selected individual record. There is on field for the amt and another field
that indicates if the amt is a deposit or a withdraw. Below is what I got
sofar but can't seem to get it right. I need to take the deposit amt and
subtract the withdraw amt.
**CheckingCategoryID = 1 is the deposit amt
**CheckingCategoryID = 2 is the withdraw amt
**both are currency
**the CheckingID is the PK

=DSum(("[Amount]","tblChecking","[CheckingCategoryID]=1")-("[Amount]","tblChecking","[CheckingCategoryID]=2"),"[CheckingID]<= " & [CheckingID])

If anyone can resolve this mystery, it is appreciated.
Thanks in advance.
.... John

 

[Allen Browne]

What results do you get?

The expression looks okay, provided:
- CheckingCategoryID is a Number field (not a Text field.)
- CheckingID is a Number field (AutoNumber is okay.)
- You are not at a new record (where CheckingID is null.)
- tblChecking contains records for only one account.

To debug it, you could open the Immediate Window (Ctrl+G), and gradually
build the expression up until you get what you want. Start with:
? DSum("Amount", "tblChecking")
Then try adding the Where condition with some number, e.g.:
? DSum("Amount", "tblChecking", "CheckingID <= 20")

Ultimately, you may finish up with something like this (one line):
=DSum("IIf([CheckingCategoryID]=1, [Amount], -[Amount])",
"tblChecking", "[CheckingID] <= " & Nz([CheckingID],0))

Or if there are multiple accounts in the table:
=DSum("IIf([CheckingCategoryID]=1, [Amount], -[Amount])",
"tblChecking", "([CheckingID] <= " & Nz([CheckingID],0) &
") AND (AccountID = " & Nz([AccountID],0) & ")")

 

[JohnE]

What I get back is actually the CheckingID (autonumber) in the field rather
then the amount. That's what's puzzling about this. I will try the
debugging as you suggested but if you see anything else, let me know.
Thanks.
.... John

What results do you get?

The expression looks okay, provided:
- CheckingCategoryID is a Number field (not a Text field.)
- CheckingID is a Number field (AutoNumber is okay.)
- You are not at a new record (where CheckingID is null.)
- tblChecking contains records for only one account.

To debug it, you could open the Immediate Window (Ctrl+G), and gradually
build the expression up until you get what you want. Start with:
? DSum("Amount", "tblChecking")
Then try adding the Where condition with some number, e.g.:
? DSum("Amount", "tblChecking", "CheckingID <= 20")

Ultimately, you may finish up with something like this (one line):
=DSum("IIf([CheckingCategoryID]=1, [Amount], -[Amount])",
"tblChecking", "[CheckingID] <= " & Nz([CheckingID],0))

Or if there are multiple accounts in the table:
=DSum("IIf([CheckingCategoryID]=1, [Amount], -[Amount])",
"tblChecking", "([CheckingID] <= " & Nz([CheckingID],0) &
") AND (AccountID = " & Nz([AccountID],0) & ")")

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a subform that is a checkbook style entry that acctg wants to use.
In
the subform (the many to the main form one) there is a balance field in
the
footer showing the what the deposit minus withdraw balance, as a whole.
They
would like another field showing what the balance is from the beginning to
a
selected individual record. There is on field for the amt and another
field
that indicates if the amt is a deposit or a withdraw. Below is what I got
sofar but can't seem to get it right. I need to take the deposit amt and
subtract the withdraw amt.
**CheckingCategoryID = 1 is the deposit amt
**CheckingCategoryID = 2 is the withdraw amt
**both are currency
**the CheckingID is the PK

=DSum(("[Amount]","tblChecking","[CheckingCategoryID]=1")-("[Amount]","tblChecking","[CheckingCategoryID]=2"),"[CheckingID]<=
" & [CheckingID])

If anyone can resolve this mystery, it is appreciated.
Thanks in advance.
... John

 

[Allen Browne]

Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
In Access 2007, it's:
Office Button | Access Options | Current Database | Name AutoCorrect

Then compact the database:
Tools | Database Utilities | Compact/Repair
or in Access 2007:
Office Button | Manage | Compact/Repair

Explanation of why:
http://allenbrowne.com/bug-03.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

What I get back is actually the CheckingID (autonumber) in the field
rather
then the amount. That's what's puzzling about this. I will try the
debugging as you suggested but if you see anything else, let me know.
Thanks.
... John

What results do you get?

The expression looks okay, provided:
- CheckingCategoryID is a Number field (not a Text field.)
- CheckingID is a Number field (AutoNumber is okay.)
- You are not at a new record (where CheckingID is null.)
- tblChecking contains records for only one account.

To debug it, you could open the Immediate Window (Ctrl+G), and gradually
build the expression up until you get what you want. Start with:
? DSum("Amount", "tblChecking")
Then try adding the Where condition with some number, e.g.:
? DSum("Amount", "tblChecking", "CheckingID <= 20")

Ultimately, you may finish up with something like this (one line):
=DSum("IIf([CheckingCategoryID]=1, [Amount], -[Amount])",
"tblChecking", "[CheckingID] <= " & Nz([CheckingID],0))

Or if there are multiple accounts in the table:
=DSum("IIf([CheckingCategoryID]=1, [Amount], -[Amount])",
"tblChecking", "([CheckingID] <= " & Nz([CheckingID],0) &
") AND (AccountID = " & Nz([AccountID],0) & ")")

I have a subform that is a checkbook style entry that acctg wants to
use.
In
the subform (the many to the main form one) there is a balance field in
the
footer showing the what the deposit minus withdraw balance, as a whole.
They
would like another field showing what the balance is from the beginning
to
a
selected individual record. There is on field for the amt and another
field
that indicates if the amt is a deposit or a withdraw. Below is what I
got
sofar but can't seem to get it right. I need to take the deposit amt
and
subtract the withdraw amt.
**CheckingCategoryID = 1 is the deposit amt
**CheckingCategoryID = 2 is the withdraw amt
**both are currency
**the CheckingID is the PK

=DSum(("[Amount]","tblChecking","[CheckingCategoryID]=1")-("[Amount]","tblChecking","[CheckingCategoryID]=2"),"[CheckingID]<=
" & [CheckingID])

If anyone can resolve this mystery, it is appreciated.
Thanks in advance.
... John