How to SUM comma separated list?

[deko]

I have a form where users can select any number of transactions from a
datasheet. As they select the transactions, a coma separated list of
Transactions IDs is created, like this:

3349, 3221, 503, 30, 21893, 8320,

Each Transaction ID has a corresponding currency "Amount" in the
Transactions table. I need to calculate a total value for all selected
transactions.

I thought about keeping a running total as the selections are made, but the
list can be edited (in a text box) before the "Calculate" button is clicked,
so that doesn't work.

Can I somehow put the list into an array and sum the array? Or build a SQL
string and execute a SELECT SUM query? Do I need to use regex here? Other
ideas?

Thanks in advance.

 

[Wayne-I-M]

HI

I would keep it simple (less to go wrong then

Create a text box in the form header and use this to display the total.
Although this will not work in the datasheet on it's own.

I wouldn't use a calculate button - but thats up to you. If you have a text
box that can be changed then just use this - set the default vaule of this
box as =[your amount field] then just sum this if your check box = -1


If you're using a datesheet create a single form with the datesheet as a
subform.
Base the main form on the same table as the datasheet
Use the query just to give the total you need
Requery just the unbound box after update of the text box

In the query you could use something like this
SELECT Sum(TableName.AmountField) AS SumOfAmountField
FROM TableName
HAVING (([TableName]![CheckBox]=-1));


There are lots of ways to do what you want - this is just (a simple) one

HTH

 

[deko]

I have a form where users can select any number of transactions from a
datasheet. As they select the transactions, a coma separated list of
Transactions IDs is created, like this:

3349, 3221, 503, 30, 21893, 8320,

Each Transaction ID has a corresponding currency "Amount" in the
Transactions table. I need to calculate a total value for all selected
transactions.

I thought about keeping a running total as the selections are made, but
the list can be edited (in a text box) before the "Calculate" button is
clicked, so that doesn't work.

Can I somehow put the list into an array and sum the array? Or build a
SQL string and execute a SELECT SUM query? Do I need to use regex here?
Other ideas?

Thanks in advance.

Hey! this might work:

Function SumTx() as Long
Dim ndx As Integer
Dim strTx As String
Dim varTx As Variant
Dim lngSum As Long
strTx = "3, 4, 5"
varTx = Split(strTx, ",")
For ndx = LBound(varTx) To UBound(varTx)
lngSum = lngSum + varTx(ndx)
Next
SumTx = lngSum
End Function

 

[Wayne-I-M]

Very strange answering you own questions ? I think the simplest would be (as
you say) to do a running sum but only sum if your check box =-1

Another (betteras it keeps the form unclutered) would be to have the query I
gave at the only visible box on a popup (do this onclick of your calculted
button.

There are zillions of methods (dim strBoxName ... strBoxName running sum if
....)



good luck

 

[Ken Sheridan]

You can do it simply by applying the IN operator to the value list in the
criterion of a DSum function call. Put the following function in the form's
module:

Private Function SumTX(varTXList) As Currency

Dim strCriteria As String

varTXList = Nz(varTXList, 0)
strCriteria = "[Transaction ID] In(" & varTXList & ")"

SumTX = Nz(DSum("Amount", "Transactions", strCriteria), 0)

End Function

As the ControlSource property of the control to show the sum of the amounts
put:

=SumTX([txtTransactionList])

where txtTransactionList is the text box control containing the comma
separated list of Transaction ID numbers.

Ken Sheridan
Stafford, England

 

[deko]

Private Function SumTX(varTXList) As Currency

Dim strCriteria As String

varTXList = Nz(varTXList, 0)
strCriteria = "[Transaction ID] In(" & varTXList & ")"

SumTX = Nz(DSum("Amount", "Transactions", strCriteria), 0)

End Function

As the ControlSource property of the control to show the sum of the
amounts
put:

=SumTX([txtTransactionList])

This looks interesting - thanks! But I'll need to make sure my varTXList is
properly formatted (no trailing commas, etc.)

 

[Jamie Collins]

I have a form where users can select any number of transactions from a
datasheet. As they select the transactions, a coma separated list of
Transactions IDs is created, like this:

3349, 3221, 503, 30, 21893, 8320,

Each Transaction ID has a corresponding currency "Amount" in the
Transactions table. I need to calculate a total value for all selected
transactions.

I thought about keeping a running total as the selections are made, but the
list can be edited (in a text box) before the "Calculate" button is clicked,
so that doesn't work.

Can I somehow put the list into an array and sum the array? Or build a SQL
string and execute a SELECT SUM query? Do I need to use regex here? Other
ideas?

What about putting the values into a (scratch) table and executing a
SELECT SUM query against this table?

Jamie.

--