Put your code in a function.
like this and put that function in a
module.
*******************************************************
Public Function OCCUR(strSearch as string, _
                                   strLook as string ) _
                                   As Integer
  Dim ones as integer
  ones = 0
  For I = 1 to Len(strSearch)
         If Mid(strSearch,I,1)= strLook Then Ones=Ones+1
   Next I

  OCCUR = ones
End Function
*******************************************************************

After you have created and saved that function
you can now use that in your SQL statement

SELECT OCCUR(AnyString,"1") as ONES
FROM TABLENAME

HTH

Well, you can get a count by using

UBound(Split("122211221", "1"))

So, the above would return a value of 4

And,
UBound(Split("122211221", "12"))

Would return a vlaue of 2

I would suggest you build a public function, and then you can use it
anywhere in ms-access anyway.

In other words, place the following code in a standard module:

Public Function CountStr(str As String, strDelim As String) As Integer

  ' this routine returnds the number of occrancines of a string
  ' of charatres

  ' Parms are:
  '     str         - the string to search
  '     strDelim    - the string to find and count in the above

  CountStr = UBound(Split(str, strDelim))

End Function

Now, any ime you need a count, you can go

CountStr("abcabcabc","c")

So, build the funciton yourself, and you will not have to wait for Gates to
build it....

This is an old trick: replace the search text (e.g. '1') with a string
of one less character (e.g. '') and test the resulting string's length
agaisnt the length of the original string e.g.

SELECT '122211221' AS my_col, LEN(my_col) - LEN(REPLACE(my_col, '1',
'')) AS ones

Jamie.

--