Count number of characters in a field

  • Thread starter Thread starter ian salmon
  • Start date Start date
I

ian salmon

I am trying to count the number of characters in a field.
e.g the field contains 11101101.
I wish to count then number of 1's. The answer to the
above should be 6

I have a formula which does this in excel.If cell A1
contains 11101101 the formula is:
=len(substitute(A1,1,"")) but I cant find the equivilent
of 'substitute' in access.

Thanks in advance for any help.
 
Replace

is the substitute.


We Access guys should get together and create a list of
Access replacements for Excel statistical and other
functions.

For example, I've had to make conversions for RATE,
PRODUCT, and CORREL.

PRODUCT: Exp(Sum(Log(...some number...))), except is a
little more complicated when dealing with possibly
negative numbers (need to test for proper sign, and of
course trapping if there's a 0 [answer=0])

CORREL: r: (Sum([a].[Gross]*.[Gross])-Sum([a].[Gross])
*Sum(.[Gross])/Count(*))/Sqr((Sum([a].[Gross]*[a].
[Gross])-Sum([a].[Gross])*Sum([a].[Gross])/Count(*))*(Sum
(.[Gross]*.[Gross])-Sum(.[Gross])*Sum(.
[Gross])/Count(*)))

David Atkins, MCP
 
I am trying to count the number of characters in a field.
e.g the field contains 11101101.
I wish to count then number of 1's. The answer to the
above should be 6

I have a formula which does this in excel.If cell A1
contains 11101101 the formula is:
=len(substitute(A1,1,"")) but I cant find the equivilent
of 'substitute' in access.
Click to expand...

Replace() should do it (in A2002 anyway). Or you could copy and paste
this VBA into a Module and call it:

Public Function CountOnes(strIn As String) As Integer
Dim iPos As Integer
CountOnes = 0
For iPos = 1 to Len(strIn)
If Mid(strIn, iPos, 1) = "1" Then
CountOnes = CountOnes + 1
End If
Next iPos
End Function
 
Thanks for the replies.
Am I correct in thinking that the "replace" is not
available in access 2000, only in vb. If so can I include
a vb only function in an access query? If so how?

Thanks again
ian
 
Back
Top