Count # of Characters of a certain type in a record

[BlueWolverine]

Hello,
MS Access 2003 on XP Pro.

I have a table with a field in which I list several Codes, in a comma
separated fashion. I am sure the first thing you are going to tell me is,
"Why do you have a single field with comma separated list of values." Call
it convenience and I don't know how many fields I'd need to put one in to
each. And I don't want to fold it.

Anyway, I want to count the number of commas in a "cell" (Cell being an
entry for a record in a particular field. call it a row-column intersection.)

Is there any good way to count the number of times a comma shows up in Field
X for each record?

Thanks,

 

[John Spencer]

The following should work in Access 2000 (fully patched) or later.

Field: CountCommas: Len(YourField)- Len(Replace(NZ(YourField<""),",",""))

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Marshall Barton]

MS Access 2003 on XP Pro.

I have a table with a field in which I list several Codes, in a comma
separated fashion. I am sure the first thing you are going to tell me is,
"Why do you have a single field with comma separated list of values." Call
it convenience and I don't know how many fields I'd need to put one in to
each. And I don't want to fold it.

Anyway, I want to count the number of commas in a "cell" (Cell being an
entry for a record in a particular field. call it a row-column intersection.)

Is there any good way to count the number of times a comma shows up in Field
X for each record?

NumCommas = Len(field) - Len(Replace(field, ",", ""))