Counting commas in a text field

[dixie]

I have a text field on a form which has names with a comma between them like
this: 'J. Smith, A. Jones, A. Man, J. Johns'. I am trying to find a
procedure that will count the number of people in this text box and put the
number into another text box (4 in the case above). If I could count the
number of commas + 1, this would do it. Can anyone please help me with a
way to achieve this?

dixie

[fredg]

On Thu, 16 Sep 2004 09:52:27 +1000, dixie wrote:

I have a text field on a form which has names with a comma between them like
this: 'J. Smith, A. Jones, A. Man, J. Johns'. I am trying to find a
procedure that will count the number of people in this text box and put the
number into another text box (4 in the case above). If I could count the
number of commas + 1, this would do it. Can anyone please help me with a
way to achieve this?

dixie

Air code:
In a module:
Function CountCommas(StringIn as String) as String
Dim intX as Integer
Dim intY as integer
intX = InStr(StringIn,",")
Do While intX <> 0
intY = intY + 1
intX = InStr(intX + 1,StringIn,",")
Loop
CountCommas = intY
End Function

You can call it from a query.
Words: CountCommas([FieldName]) + 1
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

[Steve Jorgensen]

Easiest way is to use the Split function:

Public Function CountStringItems( _
Items As String, _
Delim As String _
) As Long
Dim varaItems As Variant
varaItems = Split(Items, Delim)
CountStringItems = ArrayCount(varaItems)
End Function

Public Function ArrayCount(varArray As Variant)
ArrayCount = UBound(varArray) - LBound(varArray) + 1
End Function

On Thu, 16 Sep 2004 09:52:27 +1000, "dixie" <di****@dogmail.com> wrote:

I have a text field on a form which has names with a comma between them like
this: 'J. Smith, A. Jones, A. Man, J. Johns'. I am trying to find a
procedure that will count the number of people in this text box and put the
number into another text box (4 in the case above). If I could count the
number of commas + 1, this would do it. Can anyone please help me with a
way to achieve this?

dixie

[fredg]

On Thu, 16 Sep 2004 02:41:48 GMT, fredg wrote:

On Thu, 16 Sep 2004 09:52:27 +1000, dixie wrote:

I have a text field on a form which has names with a comma between them like
this: 'J. Smith, A. Jones, A. Man, J. Johns'. I am trying to find a
procedure that will count the number of people in this text box and put the
number into another text box (4 in the case above). If I could count the
number of commas + 1, this would do it. Can anyone please help me with a
way to achieve this?

dixie

Air code:
In a module:
Function CountCommas(StringIn as String) as String
Dim intX as Integer
Dim intY as integer
intX = InStr(StringIn,",")
Do While intX <> 0
intY = intY + 1
intX = InStr(intX + 1,StringIn,",")
Loop
CountCommas = intY
End Function

You can call it from a query.
Words: CountCommas([FieldName]) + 1

I gave you information on how to count commas in a field, but your
basic quest is to separate text into various fields.
You don't need to count the commas.
Look up the Split() function in VBA help.
It will parse the field without need for this counting function.

Public Function ParseText(TextIn As String, X) As Variant
On Error Resume Next
Dim var As Variant
var = Split(TextIn, ",", -1)
ParseText = var(X)
End Function
============
Call it from a query:
Name1:ParseText([Fullfield],0)
Name2:ParseText([FullField],1)
etc.

Note that the array is zero based.

You get 2 functions today for the price of one. :-)
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

[dixie]

I am a little at a loss as to how to run this on my form to count the names
in the text box txtNames which has names separated by commas. Sorry, but
I'm not good with public functions. I have put it into the main module, but
how do I use it on the form?

dixie

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:dp********************************@4ax.com...

Easiest way is to use the Split function:

Public Function CountStringItems( _
Items As String, _
Delim As String _
) As Long
Dim varaItems As Variant
varaItems = Split(Items, Delim)
CountStringItems = ArrayCount(varaItems)
End Function

Public Function ArrayCount(varArray As Variant)
ArrayCount = UBound(varArray) - LBound(varArray) + 1
End Function

On Thu, 16 Sep 2004 09:52:27 +1000, "dixie" <di****@dogmail.com> wrote:

I have a text field on a form which has names with a comma between them likethis: 'J. Smith, A. Jones, A. Man, J. Johns'. I am trying to find a
procedure that will count the number of people in this text box and put thenumber into another text box (4 in the case above). If I could count the
number of commas + 1, this would do it. Can anyone please help me with a
way to achieve this?

dixie

[Steve Jorgensen]

Well, where does the value get used, and how? Do you mean it's displayed in
another control on the form? If so, you could set the Control Source property
of that control to =CountStringItems([txtNames] & "", ",").

The [txtNames] & "" part converts the value to a blank string if it is Null.
Ichecked, and the function correctly returns 0 for a blank string.

On Fri, 17 Sep 2004 13:57:46 +1000, "dixie" <di****@dogmail.com> wrote:

I am a little at a loss as to how to run this on my form to count the names
in the text box txtNames which has names separated by commas. Sorry, but
I'm not good with public functions. I have put it into the main module, but
how do I use it on the form?

dixie

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:dp********************************@4ax.com.. .

Easiest way is to use the Split function:

Public Function CountStringItems( _
Items As String, _
Delim As String _
) As Long
Dim varaItems As Variant
varaItems = Split(Items, Delim)
CountStringItems = ArrayCount(varaItems)
End Function

Public Function ArrayCount(varArray As Variant)
ArrayCount = UBound(varArray) - LBound(varArray) + 1
End Function

On Thu, 16 Sep 2004 09:52:27 +1000, "dixie" <di****@dogmail.com> wrote:

>I have a text field on a form which has names with a comma between themlike >this: 'J. Smith, A. Jones, A. Man, J. Johns'. I am trying to find a
>procedure that will count the number of people in this text box and putthe >number into another text box (4 in the case above). If I could count the
>number of commas + 1, this would do it. Can anyone please help me with a
>way to achieve this?
>
>dixie
>

[dixie]

Steve, your a genius. That worked straight away. I can even see it
dynamically updating on the screen as I add people to the text box. Thanks
for your kind help.

dixie

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:2c********************************@4ax.com...

Well, where does the value get used, and how? Do you mean it's displayed in another control on the form? If so, you could set the Control Source property of that control to =CountStringItems([txtNames] & "", ",").

The [txtNames] & "" part converts the value to a blank string if it is Null. Ichecked, and the function correctly returns 0 for a blank string.

On Fri, 17 Sep 2004 13:57:46 +1000, "dixie" <di****@dogmail.com> wrote:

I am a little at a loss as to how to run this on my form to count the namesin the text box txtNames which has names separated by commas. Sorry, but
I'm not good with public functions. I have put it into the main module, buthow do I use it on the form?

dixie

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:dp********************************@4ax.com.. .

Easiest way is to use the Split function:

Public Function CountStringItems( _
Items As String, _
Delim As String _
) As Long
Dim varaItems As Variant
varaItems = Split(Items, Delim)
CountStringItems = ArrayCount(varaItems)
End Function

Public Function ArrayCount(varArray As Variant)
ArrayCount = UBound(varArray) - LBound(varArray) + 1
End Function

On Thu, 16 Sep 2004 09:52:27 +1000, "dixie" <di****@dogmail.com> wrote:

>I have a text field on a form which has names with a comma between them

like

>this: 'J. Smith, A. Jones, A. Man, J. Johns'. I am trying to find a
>procedure that will count the number of people in this text box and

putthe

>number into another text box (4 in the case above). If I could count

the >number of commas + 1, this would do it. Can anyone please help me with a >way to achieve this?
>
>dixie
>