kennykee said:
How to fix total number of words in a field?
eg.
30 X 30 ==> 3 words
50 X 89 ==> 3 words
80X 21 ==>2 words==>error message & delete
Any solutions?
Here's a function that returns an array of the blank-delimited words in
the string you pass it:
'----- start of code -----
Function SplitWords( _
ByVal pstrSource As String, _
Optional pstrDelim As String = " ", _
Optional plngLimit As Long = -1, _
Optional pCompare As Long = vbBinaryCompare) _
As String()
' Split the argument pstrSource into an array of "words"
' based on the delimiter passed as pstrDelim, treating
' consecutive occurrences of the delimiter as a single
' occurrence. So, for example, SplitWords("This Function")
' returns the same output array as SplitWords("This Function").
Dim strTwoDelim As String
Dim lngLen As Long
strTwoDelim = pstrDelim & pstrDelim
Do
lngLen = Len(pstrSource)
pstrSource = _
Replace(pstrSource, strTwoDelim, pstrDelim, 1, -1, pCompare)
Loop Until Len(pstrSource) = lngLen
SplitWords = Split(pstrSource, pstrDelim, plngLimit, pCompare)
End Function
'----- end of code -----
With that function saved in a standard module, you can count the number
of words in a field like this:
intWords = UBound(SplitWords([YourField])) + 1
In a BeforeUpdate event procedure, you might say something like:
If UBound(SplitWords([YourField])) + 1 <> 3 Then
MsgBox "That's not a valid measurement."
Cancel = True
End If
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
