Count elements on query

[danvzla]

Hi all,

I'm trying to find the way of doing a query that counts how many ELEMNTS are in a field (Always 4 DIGIT ELEMENTS separeate by a blank space).

Table:

Field1 Field2

A XXXX BBBB YYYY EEEE DDDD
B JJJJ PPPP
C IIII QQQQ PPPP
D LLLL

Query that I need:

Field1 Field2 Filed3

A XXXX BBBB YYYY EEEE DDDD 4
B JJJJ PPPP 2
C IIII QQQQ PPPP 3
D LLLL 1

Do anybody got an idea.

Thank a lot for your time

 

[LittleSmudge]

SELECT Field1, Field2, (Len([Field2]) + 1) \ 5 As Field3 FROM .. etc


I take it that 'Filed3' was a typo!



'ope-that-'elps.

G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[danvzla]

Thanks is a good idea... but sometimes there can be blank spaces at the end, like

"XXXX XXXX XXXX XXXX "

so the result is not going to be correct. Maybe if you know a way of deleting these blank spaces it would work.

Thanks again

Dan

 

[PHV]

You may consider creating a function in a Module:
Public Function myCountOfWords(myString) As Integer
myCountOfWords = 1 + UBound(Split(myString))
End Function
And then:
SELECT Field1, Field2, myCountOfWords(Field2) As Field3 ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[LittleSmudge]

Then try the Trim function

LTrim trims of left ( leading ) white space
RTrim trims off right ( trailing ) white space


SELECT Field1, Field2, (Len(RTrim([Field2])) + 1) \ 5 As Field3 FROM .. etc




'ope-that-'elps

G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[MichaelRed]

Public Function basNumWds(strIn As String) As Integer

    Dim Wds() As String
    Dim Idx As Integer
    Dim Jdx As Integer

    Wds = Split(strIn)

    While Idx <= UBound(Wds)
        If (Trim(Wds(Idx)) <> "") Then
            Jdx = Jdx + 1
        End If
        Idx = Idx + 1
    Wend

    basNumWds = Jdx

End Function

MichaelRed
mlred@verizon.net