Count numeric values in field

[Gregorys05]

Hi All,
I have been using the following code in a excel spreadsheet and it is working fine, now i would like to transfer this to an access database.
To be able to count the amount of numeric values in a field.
E.G
152639 = 6
1A2A3A = 3
11ZZZZ=2


[/code]
Public Function CountNumeric(rCell As Range) As Long
Dim i As Long
For i = 1 To Len(rCell)
If IsNumeric(Mid(rCell.Value, i, 1)) Then
CountNumeric = CountNumeric + 1
End If
Next i
End Function
[/code]

Any ideas how i could do this either as a function or in a query?

Thanks

 

[Phildaburn]

Add your table to a query. First column = your field that contains "152639" etc. The second column can be the same field. In the ribbon bar to the far right in the "Show/Hide" section, click on the Sigma button that is labeled "Totals". Just below your field name you'll now see a series of "Group By". Under the second instance of your field, change that "Group By" to "Count". Run the query.

Hope this helps...

Phil...

 

[james_lankford]

start access
click Modules
click New

paste this into the module

Option Compare Database
Option Explicit

'*******************************
Public Function CountNumeric(rCell As Variant) As Long
    Dim i As Long
    Dim s As String
    s = Nz(rCell, "")
    For i = 1 To Len(s)
    If IsNumeric(Mid(s, i, 1)) Then
        CountNumeric = CountNumeric + 1
    End If
    Next i
End Function
'*******************************

close the module

click Queries
click New
click Simple Query Wizard
select your table and click Add then Close

type this
CountNumeric( [your_field_name] )
into the the Field Row, the first row of the query designer
run the query