Count string occurrences

[PayeDoc]

Hello All

I have a text field on a form where I want to prevent the user from entering
a vlue that has more than one 'space' character.
I thought the best way to do this would be to have a function to count the
number of spaces, and then have some validadtion to prevent this count value
frok being >1. Is this the best way, and if so how to I count the number of
spaces?

Hope someone can help.
Many thanks
Leslie Isaacs

 

[Douglas J. Steele]

Len(MyString) - Len(Replace(MyString, " ", "")) will tell you how many
spaces are in MyString.

 

[John Spencer (MVP)]

You can test if there is more than one space using
SomeString Like "* * *"
As a validation rule you might want to make that
Not Like "* * *" or is Null

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[PayeDoc]

Many thanks for that: I know the Len and Replace functions, so should have
thought of it myself!!

Thanks again
Les

 

[PayeDoc]

John

That's great - works perfectly!!

Many thanks
Les

 

[MikeB]

Just one comment. If you were going to have that code _just_to_test for an
extra space, you might as well just call REPLACE in the first place and be
done with it.

 

[Douglas J. Steele]

Except that Leslie wanted to allow one space in the string.

 

[MikeB]

I got that.

There isn't any more overhead to do replace a double space than there is to
check it each time, is there?

And if there is a double space, then there is additional overhead to do a
replace, right?

 

[Douglas J. Steele]

You're assuming that the desired change was ensuring you have aa bb (one
space), not aa bb (two spaces)
It could also be to prevent aa bb cc.

And you'd need to use Replace multiple times to ensure multiple spaces get
replaced by a single space.

Replace("aa bb", " ", " ") returns "aa bb"

(that's aa followed by three spaces then bb, and changing two spaces to one
space in the Replace function. It returns aa followed by two spaces followed
by bb)

 

[MikeB]

You're assuming that the desired change was ensuring you have aa bb (one
space), not aa bb (two spaces)
It could also be to prevent aa bb cc.

And you'd need to use Replace multiple times to ensure multiple spaces get
replaced by a single space.

Replace("aa bb", " ", " ") returns "aa bb"

(that's aa followed by three spaces then bb, and changing two spaces to
one space in the Replace function. It returns aa followed by two spaces
followed by bb)

OK.. Didn't think about the cat sitting on the space bar ;-)