making the formula expression to sum ​​a series of numbers (text) numerical result

[Dankurkos]

Hi ...

I will make a query, but I have difficulty in making the formula expression to sum ​​a series of numbers (text) into the numerical results
example

NUMBER_BA	GOODS_KODE	QTY_GOODS	TOTAL
001	1	+8000+800+9000	formula sum a series of numbers (text) into the numerical results = 17.800
001	1	+8000+800+9000
001	1	+8000+800+9000
001	2	+500+1500+10000+15000+14000	41.000
001	2	+500+1500+10000+15000+14000

anybody can help me
I attached an example file database query

Thank you

[ssanfu]

It looks like "QTY_GOODS" is a MVF... I don't use multi value fields - they are not easy to deal with and are not (IMO) a normalized structure.


You could write your own function (UDF) to return a value.

- Pass the field "QTY_GOODS" value to the function
- Split the field on the *+ sign.
- Use the Val() function to convert the string to a value and add the values.
- Return the sum.



But the first thing I would do is stop using the MVF...

[June7]

I agree, multi-value fields bad.

Steve, if that is truly a multi-value field (not a single string) then don't think parsing will work.

If you must use MVF, then do a query that expands the multi-value field then do an aggregate query on the records or build a report and do aggregate calcs in footer sections

http://office.microsoft.com/en-us/ac...010149297.aspx

Should have set the field as number type, not text. Will have to deal with eliminating the + sign and converting to a number in the expanded query, Val() function works:

Val([field])

However, if those values were one long string instead of multi-value, could use Eval() function.

[ssanfu]

if that is truly a multi-value field (not a single string) then don't think parsing will work.

Yes, you are right. It was a long day.... thanks

[Dankurkos]

yeahh,,it work
I use a formula eval ([field])

thank you