Counting Spaces

[crosmill]

I need to be able to count the number of spaces in each record.

Any ideas.

Cheers

 

[Mile-O]

In each record or a specific field in each record?

 

[crosmill]

a specific field in each record

 

[Mile-O]

Put this in a module:

Public Function CountSpaces(strField As String) As Integer
    On Error Goto Err_CountSpaces
    Dim intCounter As Integer
    For intCounter = 1 To Len(strField)
        If Mid(strField, intCounter, 1) = " " Then
            CountSpaces = CountSpaces + 1
        End If
    Next intCounter
Exit_CountSpaces:
    Exit Function
Err_CountSpaces:
    CountSpaces = 0
    Resume Exit_CountSpaces    
End Function

In your query:

NewField: CountSpaces([MyField])

 

[crosmill]

Genius!

I never would have thought to do it like that.

Slight mistake on the penultimate line though.
I changed it to this and it works.

I tend to wing it a bit with modules though. Is my correction right?

Public Function CountSpaces(strField As String) As Integer
On Error GoTo Err_CountSpaces
Dim intCounter As Integer
For intCounter = 1 To Len(strField)
If Mid(strField, intCounter, 1) = " " Then
CountSpaces = CountSpaces + 1
End If
Next intCounter
Exit_CountSpaces:
Exit Function
Err_CountSpaces:
CountSpaces = 0
Resume Exit_CountSpaces
End Function



Cheers Mile

 

[crosmill]

It also creates #Error on a NULL string

 

[Mile-O]

NewField: IIf(IsNull([MyField]), 0, CountSpaces([MyField]))

 

[crosmill]

Brilliant!

Works a treat.

For curiousities sake, can you deal with the NULL in the Module. I couldn't get it to work?