Count sentences in a memo field

[yippie_ky_yay]

Hello all,

I simply need to know how many sentences there are in this particular memo field (many records) - it would be safe to say that whenever there is a period "." that that is the end of the sentence - so I would just like to count all the periods but I'm having trouble (too early I think!).

Can I do this in a query?

Thanks in advance,
-Sean

 

[Mile-O]

Your best bet would be to make a function that counts the full-stop character in your memo field and base a new field in a query on the result of the function.

As memos can be huge, your query may run very very slow.

 

[yippie_ky_yay]

Hey Mile-O-Phile - thanks for answering, that was fast!

The fields are very rarely over the 255 mark, and it doesn't matter if the query takes a long time or not.

My problem is that I can't seem to write that function - it's like I totally forgot SQL on the weekend!

Basicly, I'd like my result to look like the following:

ID_____SentencesFld_____NumOfSentences
1______sentence.________1
2______________________0
3______sent. sent2.______2
...

My problem is counting instances within a certain field, so I'm not even sure how to start this one.
ie Count(".") IN (Select SentencesFld FROM TableName) AS NumOfSentences

Any help would be greatly appreciated!

-Sean

 

[CJBIRKIN]

Hi

I think this may be what you need.


Basically it finds the postion of the first full stop. Counts that it has found 1 and then cuts the string from behind that full stop so and then analyses the next part of the string.


Public Function GetNumStops(MemoStr As String) As Integer


' do until the str is completed or there are no more full stops
Do Until Len(MemoStr) = 0 or InStr(MemoStr, ".") = 0

' if there is a full stop
If InStr(MemoStr, ".") > 0 Then

' count it
GetNumStops = GetNumStops + 1

' cut the string leaving only the uncounted part
MemoStr = Mid(MemoStr, InStr(MemoStr, ".") + 1)
End If

' continue to count
Loop

End Function


Then in the query in the field row type

CountNumStops: GetNumStops([Replace this with your field name])



Chris

 

[yippie_ky_yay]

Thank you Chris, that worked beautifully!

-Sean

 

[CJBIRKIN]

No worries