Count Number of Spaces Within String

[Larry R Harrison Jr]

I have Access 2000. I want to be able to count the number
of spaces within a string. The string is a text box on a
form. I know how to search for a space, but to be able to
find every space and count then, I don't know how.

Assume the name of the string is Me.txtSearch, what would
be the syntax?

LRH

 

[Jonathan]

-----Original Message-----
I have Access 2000. I want to be able to count the number
of spaces within a string. The string is a text box on a
form. I know how to search for a space, but to be able to
find every space and count then, I don't know how.

Assume the name of the string is Me.txtSearch, what would
be the syntax?

LRH
.

Hi Larry,

I would create a function to return the count. The
function would literaly inspect each character in the
string an increment a counter. Use the following as an
example:

' call function
intSpaces=SpaceCount(Me.txtSearch)

function SpaceCount(myData) as integer
dim intChr as integer
dim intCount as integer

for intChr=1 to len(myData)
if mid(myData,intChr,1)=space(1) then
intCount=intCount+1
end if
' allow for window events in loop
doevents
next intChr

SpaceCount=intCount

End Function

Luck
Jonathan

 

[John Vinson]

I have Access 2000. I want to be able to count the number
of spaces within a string.

There isn't any builtin function to do this; here's some "air code",
untested but it should work:

Public Function CountChar(strIn As String, strF As String) As Integer
CountChar = 0
If Len(strF) <> 1 Or Len(strIn) = 0 Then Exit Function
' or be nice and flash an error message
Dim iPos As Integer
For iPos = 1 to Len(strIn)
If Mid(strIn, iPos, 1) = strF Then
CountChar = CountChar + 1
End If
Next iPos
End Function

Then in your Query you could use

HowManyBlanks: CountChar([fieldname], " ")

 

[Bruce M. Thompson]

I have Access 2000. I want to be able to count the number

of spaces within a string. The string is a text box on a
form. I know how to search for a space, but to be able to
find every space and count then, I don't know how.

Assume the name of the string is Me.txtSearch, what would
be the syntax?

I think that the following would work:

Ubound(Split(Me.txtSearch.Value, " ", , vbBinaryCompare)

 

[MikeB]

Just picked this up today. Works with any String/StringToCount combo. Credit goes To Mike D.
Sutton in the Microsoft.public.VB.General.Discussion NG.

Private Function NumInstance(ByVal inString _
As String, ByVal inFind _
As String) As Long
'number of occurances of string in a string
NumInstance = (Len(inString) - _
Len(Replace$(inString, inFind, ""))) _
\ Len(inFind)
End Function

 

[Jonathan]

Hi Bruce, nice solution

Cheers
Jonathan

I think that the following would work:

Ubound(Split(Me.txtSearch.Value, " ", , vbBinaryCompare)

 

[Bruce M. Thompson]

Hi Bruce, nice solution

Well, it works okay if you know to add the last ")", which apparently got
hijacked in the copy/paste operation. <g>

 

[Bob]

I tried this and can't get it to work. Should it work in A97?

 

[Mike Painter]

I have Access 2000. I want to be able to count the number
of spaces within a string. The string is a text box on a
form. I know how to search for a space, but to be able to
find every space and count then, I don't know how.

Assume the name of the string is Me.txtSearch, what would
be the syntax?

This should work.

Public Function countSpaces(stringIn As String)
Dim ThisArray
ThisArray = Split(stringIn, " ")
countSpaces = UBound(ThisArray, 1)

End Function