How to count dates in a single field

[cdutcher]

First timer question here: In a customer data table, for each customer I have a field that includes dates in a single field. Example:
Name Dates
Steve 87,91,01,05,11, 12
Bob 99, 03
Ann 01,05,06

In the criteria field of a query, I would like to be able to determine who has at least three dates in the field. Any advice?

[Seth Schrock]

My advice would be to normalize your data (See Database Normalization and Table Structures) and then this will be a very simple problem using the DCount() function.

[jimatqsi]

cdutcher, when you say "dates" do you mean "years"? I don't see any dates in your example data. It is helpful if you say precisely what you mean, rather than some other thing.

If the data is consistently formatted, you might be able to select based on the length of the data in the columns. Three 2-digit numbers + 2 commas is at least 8 characters, so your query could include

where len([Dates])>=8

Jim

[cdutcher]

Hi Seth,

Unfortunately, I'm a bit of a noob regarding Access/data, and am hoping there's another way to achieve this. I'll look into normalizing it during my slow-summer months.
I had read elsewhere that I might be able to count the commas, but my syntax is erroring:
Length[dates] - Length(Replace([dates], "," , " "))

[cdutcher]

Hi Jim. My apologies, yes years (two digit years). The fields contain varying numbers of years, but there may be a way to calculate based upon the commas (eg, three or more years will include 2 or more commas).

[jimatqsi]

So the where clause I suggested should work just fine for you.

[cdutcher]

Light bulb above my head just went on. Thanks so much!

[zmbd]

As an alternative (just incase spaces mess things up)
[Dates] = "87,91,01,05,11 ,12"
Find the position of the last comma
You basically have groups of three plus one group, so find the groups of three and add one, find out where the last comma is and then return the wholenumber of groups:
((instrrev("87, 91,01,05,11,12" ,","))\3)+1
NOTE: use "\" NOT "/" where MOD() returns the remainder "\" returns the resultant.
Yes, replace "87,91,..." with Bob's [Dates] and it will return 2, etc...
I'll leave it to OP to figure out the actual formula for the calculated field.
(^_^)

-It is best practice when naming fields, tables, and files to avoid the use of anything other than alphanumeric characters and the underscore (spaces although allowed are problematic from a programing point of view and best avoided) and it is VERY importaint to avoid all reserved words and tokens:
Access 2007 reserved words and symbols
AllenBrowne- Problem names and reserved words in Access


Also, I've reset the best answer to the post with the actual text of the clause (^_^)