How to Sum a Number in a string 1

[cantona]

I have a field that contains data relating to active weeks in a year for a course being run. For example, the field may contain the following.. 01001011. The 1’s indicate that for that particular week the course is active, the 0’s indicate for that week the course isn’t running. I need to create a query that will search the Week_pattern field and add up how many occurrences of 1 there are, giving me the total number of active weeks. I know I probably need to use the Instr function somehow but unsure on how exactly to do this. Can anyone help?

 

[Remou]

This would be very easy indeed if you normalized. Anyway, I think you need a function:

[Code Typed, Not Tested]Function SumWeeks(fld)
If IsNull(fld) Then
SumWeeks=0
Exit Function
End If

For i=1 to Len(Trim(fld))
intTmp=intTmp+Val(Mid(fld,i,1))
Next

SumWeeks=intTmp
End Function[/code]

 

[cantona]

Hi Remou,

Thanks for the advice. Cant really do anything about the state of the tables though as they are part of a 3rd party application. Is there anyway of writing this as an expression instead? I only really need to count how many 1's appear in the Week_pattern field. So far ive been working along the lines of..

SUM(IIF(INSTR("1",[WEEK_PATTERN]),1,0)) but cant get it right. Im not really that skilled in this sort of thing!

 

[Remou]

Ok. There may just be a way. Try:

[tt]Len(Fld)-Len(Replace(fld,"1",""))[/tt]

 

[cantona]

Thanks Remou,

Thats exactly what i was after!

Youve saved me from a major headache!