Count Number of Spaces Within String

  • Thread starter Thread starter Larry R Harrison Jr
  • Start date Start date
L

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
 
-----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
.
Click to expand...
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
 
I have Access 2000. I want to be able to count the number
of spaces within a string.
Click to expand...

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], " ")
 
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?
Click to expand...

I think that the following would work:

Ubound(Split(Me.txtSearch.Value, " ", , vbBinaryCompare)
 
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
 
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>
 
Larry R Harrison Jr said:
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?
Click to expand...

This should work.

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

End Function
 
Back
Top