[AccessD] Counting multiple instances of a specific string

Hi Jeff, another method would be to use regular expressions.  You would
match the pattern of either 3-4 digits or ENG.  Here is a function i
created for your testing.  It runs at least under Access 2003.

1. Copy the function into your database.
2. Create watches for matches, count, objvalue, pattern, regexMatch

Have fun, Mike...

'
'
'
Function testRegexp() As Variant
   Dim pattern As String
   Dim regexMatch As Boolean
   Dim count As Integer
   Dim objvalue As Variant

   pattern = "123, 1234, 9999, 0101, 333, ENG, 456, 12345,eng,99a,"

   Set regex = CreateObject("VBScript.RegExp")
   regexMatch = True

   regex.Global = True
   regex.ignorecase = True

   ' create the pattern(s)
   ' \d{3,4} means look for exactly 3 or 4 digits
   ' the | means OR; so look for 3-4 digits OR eng (see ignore case above)
   ' another way is "\d\d\d\d?" again either 3 or 4 digits
   regex.pattern = "(\d{3,4}|eng)"
   regexMatch = regex.test(pattern)

   If (regexMatch) Then
      Set matches = regex.Execute(pattern)
      count = matches.count

      ' loop thru objects in matches
      For Each Object In matches
         objvalue = Object.Value
      Next
   End If

   ' a more robust method
   regex.pattern = "(\d+|eng)"
   regexMatch = regex.test(pattern)

   If (regexMatch) Then
      Set matches = regex.Execute(pattern)
      count = matches.count

      ' loop thru objects in matches
      For Each Object In matches
         objvalue = Object.Value

         ' test value here for compliance

      Next
   End If

   ' another way using comma-space, ", " or ",\s"
   ' the ? after \s means either "," or ", "
   regex.pattern = "(,\s?)"
   regexMatch = regex.test(pattern)

   If (regexMatch) Then
      Set matches = regex.Execute(pattern)
      count = matches.count
   End If
End Function


> I have a field, clocks, that can contain either three or four character
> employee clock numbers.
>
> The field can contain numbers (123 or 1234) OR text (ENG).
>
> The field can contain one clock number or multiple clock numbers
> (seperated
> by a comma and a space).
>
> The users claim that it HAS to be this way or they could be typing the
> same
> data multiple times with the only difference being the clock number.
>
> Now, here is the problem.
>
> They (Management) have decided that they need to be able to get a count of
> how many times the individual clock number appears in the table.
>
> How do I check for multiple entries for a clock number when it could be
> null, one clock number, or many clock numbers in one field?
>
>
>
> Here is a sample of what the data looks like in the field:
>
> *clocks***
>
> 2214, 2410, 2349, 2373, 2514, 2431
>
> 1537, 2296, 2202, 2212
>
> 2244
>
> 2411
>
> 2415, 2214, 2393
>
> 2431
>
> 2431, 1542, 2514, 1922
>
> 2431, 2214, 2516
>
> 2431, 2510, 2335
>
> 2410, 2202, 1170, 2212, 2431
>
> 2477
>
> 2410
>
> 2514
>
> 2516, 2397, 2422, 2296, ENG
>
> 2522, 2337, 1661, 2250, 1180
>
>
>
>
> --
> Jeff Barrows
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>