[AccessD] Counting multiple instances of a specific string

I agree with Drew. Those clock numbers should be in a table on their own.

As for the task of finding all the clock numbers in the existing field.

Dim vArray as Variant
Dim nClockNums as Long

vArray = Split([TheClockFieldData],",")
nClockNums = uBound(vArray)+1

Note that I am splitting at the comma and ignoring the space. Chances are the users might forget to enter a space after the comma, or even enter the space before the comma.

Lambert 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jeff Barrows
Sent: Thursday, August 20, 2009 3:55 PM
To: accessd
Subject: [AccessD] Counting multiple instances of a specific string

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