Using eval() to get a sum

[Lloyd247]

1) In a query I'm converting this field: [IT32]
<CatchWeights><CatchWeight>21.65</CatchWeight><CatchWeight>25.45</CatchWeight><CatchWeight>23.25</CatchWeight><CatchWeight>21.1</CatchWeight></CatchWeights>

2) Into this for displaying on invoice: [INVOICECW]
|21.65|25.45|23.25|21.10|

3) I replace the pipes with a "+" [INV-T1] and add a zero in front and in back of the string: [INV-T2]
0 +21.65 +25.45 +23.25 +21.10 +0

4) And this to calculate totals field [Inv-T3]
Eval([Inv-T2])

The issue I have is in (4) when I have more than 65 values the eval fails, how can I get it to sum no matter how many values are entered?

 

[ebs17]

In a query I'm converting this field

What terrible data basis are you accessing?

When converting to a database, you would write the values into a table, one record for each value.
Then it does the normal SUM.

 

[CJ_London]

perhaps multiple evals?

eval("a+b+c")+eval("d+e+f")....

or use a function

function returnVal(S as string) as double
dim a() as string
dim i as integer

    a=split(s,"<CachWeights><CatchWeight>")

    for i=0 to ubound(a)-1

       returnVal=returnVal+a(i)

    next i


end function

 

[Lloyd247]

This data is from a sql database, they mix regular field values (text, numbers and so on) with long text fields that hold xml code. Making it real hard for me to extract data for reports.

 

[plog]

And if I know SQL guys, they look down on you for using Access.

More likely than not you need to build a properly normalized database and an ETL process to populate it whenever you run these reports. If this is a one off thing, which it doesn't sound like it is you can just fight through this issue. But if this is a recurring task, I'd spend some time making my life easier.

Pretend you are starting from scratch and take a completed report and build the proper tables/fields you will need to generate that report. Manually type in data into those tables, build the queries you will need to populate your report and then remake that report based on that new structure. Once the old report and new report are identical, you're halfway home.

Next, build the VBA and APPEND queries you will need to move the data from the SQL tables to the proper ones. Once built you should be able to wrap them all in a macro or VBA to have them run consecutively so it becomes a simple extraction process. When extracted you can run your report from the new tables and it will work.

This makes your life a lot easier if nothing changes--just clikc the extraction macro and run your report. And when things upstream do change (unexpected data in that monstrosity of a field, breaking one field into 2, someone adds a new table to include, etc.) you won't have to fight the whole process again, you can just go into the extraction part and make the changes you need to accommodate whatever the genius SQL guys decided to change.

 

[ebs17]

the genius SQL guys

If one switch from a Fiat Uno to a Ferrari, one won't automatically be able to drive better; at best one will be able to drive faster if one just drive straight ahead.