ACCESS FEED: Re: [MS_AccessPros] how to count

Rabu, 28 November 2012

Re: [MS_AccessPros] how to count

Youssef-

Decided to copy and paste my code into a real database to check it out and
found a couple of syntax errors. Try this, instead:

Public Function CountLetters(strTableName As String, strFieldName As
String, _
strSearchFor As String) As Long
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
' Make sure the search string is at least one character
If Len(strSearchFor) = 0 Then Exit Function
' 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

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: John Viescas <JohnV@msn.com>
Reply-To: <MS_Access_Professionals@yahoogroups.com>
Date: Wednesday, November 28, 2012 2:40 PM
To: <MS_Access_Professionals@yahoogroups.com>
Subject: 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

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

Yahoo! Groups Links