Count the number of words (commas) in a cell 2

[student4life]

Hi,

How can I write an SQL statement to count commas in a cell
in Excel i can do this with

=IF(LEN(TRIM(A1))=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(A1,",","")))

any ideas on how i can do this in Access,
I was trying to create a select query, without luck.

thanks in advance for the help

 

[Golom]

Probably write a public function

Public Function CommaCount(TheString As String) As Long
   Dim a() As String
   a = Split(TheString, ",")
   CommaCount = UBound(a)
End Function

and in your SQL

Select [fld1], CommaCount([fld1]) As [Number of Commas]
From myTable

 

[traingamer]

Or (tested):

Select [fld1], (len([fld1]) - len(replace([fld1],",", "")) + 1) As [Number of words]
From myTable

to list the number of words where that is equal to the number of commas plus 1.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[student4life]

thanks Golom. it works great.