ACCESS FEED: Re: [MS_AccessPros] how to count

Rabu, 28 November 2012

Re: [MS_AccessPros] how to count

Youssef-

You can't do that with a query. You could write a VBA function something
like:

Public Function CountLetters(strTableName As String, strFieldName As
String, strSearchFor As String) As Integer
Dim db As DAO.Database, rst As DAO.Recordset
Dim strSearch As String, intI As Integer, lngCount As Long

' Set an error trap
On Error GoTo UhOh
' Point to the current database
Set db = CurrentDB
' Open the table or query
Set rst = db.OpenRecordset("SELECT [" & strFieldName & "] FROM [" &
strTableName & "]"
' Process all records
Do Until rst.EOF
' Search for the string
strSearch = rst(strFieldName)
' Keep looping until we find them all
Do
intI = Instr(strSearch, strSearchFor)
' Not found - bail from loop
If intI = 0 Then Exit Do
' Add 1 to count
lngCount = lngCount + 1
' Move beyond the found character
strSearch = Mid(strSearch, intI + Len(strSearchFor))
' See if can find more
Loop
' Get the next record
rst.MoveNext
Loop

' Return the count
CountLetters = lngCount

' Close out
rst.Close

Done:
Set rst = Nothing
Set db = Nothing
Exit Function

UhOh:
MsgBox "Unexpected error: " & Err & ", " & Error
Resume Done
End Function

Call the function to count "E" in field "LastName" in tblMyTable like this:

CountLetters("tblMyTable", "LastName", "E")

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: Youssef <youssef2309@gmail.com>
Reply-To: <MS_Access_Professionals@yahoogroups.com>
Date: Wednesday, November 28, 2012 1:45 PM
To: <MS_Access_Professionals@yahoogroups.com>
Subject: [MS_AccessPros] how to count

how to count the number of a certain letter "for example letter E" within
a text in the table.

------------------------------------

Yahoo! Groups Links