Sum of Textfield values (1 Viewer)

[saswiss]

Hi,
I have a textfield with the following data:
EU=6;CH=3;CN=2

How can I sum the values -> 6+3+2 and update a new field with the sum total?

thanks!

 

[vbaInet]

val(mid([[B][COLOR=Red]field1[/COLOR][/B]], instr(1, [[B][COLOR=Red]field1[/COLOR][/B]], "=") + 1)) + val(mid([[B][COLOR=DarkGreen]field2[/COLOR][/B]], instr(1, [[B][COLOR=DarkGreen]field2[/COLOR][/B]], "=") + 1)) + val(mid([[COLOR=Navy][B]field3[/B][/COLOR]], instr(1, [[COLOR=Navy][B]field3[/B][/COLOR]], "=") + 1))

Why are they saved in that format?

 

[vbaInet]

By the way, is that one record? Or separate records?

The code i gave is based on separate records as you may have noticed. Is that what you're after?

 

[saswiss]

Hey there,
thanks, but I guess this works if the values are stored in different fields only?? the content in my textfield appears exactly as mentioned above:

Fieldname: CountryDetails
Content: EU=6;CH=3;CN=2

complete with '=' and ';'

I have played around with deleting the country and '=' sign to give the following result:
Fieldname: CountryDetails
Content: 6;3;2

Would this be easier to get a sum total of?

This file has been passed over to me from my predecessor...for the moment I have to work with what I've got :-( and make the changes to the structure, etc. later...

thanks for your help!

 

[saswiss]

hey again,
no, the data shown is for 1 record only...

here a few records

CountryDetails
Rec#1: 6;3;2
Rec#2: 12;112;0;56;41
Rec#3: NULL
Rec#4: 1;101;5;16

 

[vbaInet]

With the original data (i.e. EU=6;CH=3;CN=2):

val(mid([Field], instr(1, [Field], "=") + 1)) + val(mid([Field],  instr(instr(1, [Field], ";"), [Field], "=") + 1)) + val(mid([Field],  instr(instrrev([Field], ";"), [Field], "=") + 1))

Without country and "=" (i.e. 6;3;2):

val(mid([Field], instr(1, [Field], ";") - 1)) + val(mid([Field],  instr(instr(1, [Field], ";"), [Field], ";") + 1)) + val(mid([Field],  instr(instrrev([Field], ";"), [Field], ";") + 1))

 

[saswiss]

GREAT! Thanks!!

this works perfect for the records with only 3 values...

With the original data (i.e. EU=6;CH=3;CN=2):

val(mid([Field], instr(1, [Field], "=") + 1)) + val(mid([Field],  instr(instr(1, [Field], ";"), [Field], "=") + 1)) + val(mid([Field],  instr(instrrev([Field], ";"), [Field], "=") + 1))

Without country and "=" (i.e. 6;3;2):

val(mid([Field], instr(1, [Field], ";") - 1)) + val(mid([Field],  instr(instr(1, [Field], ";"), [Field], ";") + 1)) + val(mid([Field],  instr(instrrev([Field], ";"), [Field], ";") + 1))

 

[vbaInet]

I didn't see your other post. So there could be more than three values, that's fine. What should it return for Rec #3? 0 or Null?

 

[saswiss]

No problem
for Rec# 3 it should return 0

 

[vbaInet]

Ok, so you need a function for this. Place this in a module:

Public Function AddValues(strField As Variant) As Long
    Dim splitVals As Variant, i As Integer
    
    If Not IsNull(strField) Then
        splitVals = Split(strField, ";")
        
        For i = LBound(splitVals) To UBound(splitVals)
            AddValues = AddValues + Val(splitVals(i))
        Next
    End If
End Function

And call it in the query like this:

AddValues([Field])

NB: The code is for the "6;3;2" example.

 

[saswiss]

You are an absolute genius!!!
thank you, works like a charm
take care

 

[vbaInet]

You're welcome