Count Instances of a word in a string of text

W

Wolfman311

I have a form with an unbound text box. The form also has about 50 fields
that coorespond to answers from a survey. All 50 have the same 4 options to
choose from (Correct, Incorrect, Reversed and NA) The unbound text box
contains the concatination of all 50 fields separated by a space. At the
bottom of the form I need to add 4 additional unbound text boxes that each
coorespond to 1 of the 4 possible answer...ie a count of the number of times
"correct" is in the string and have that number displayed in the field...
likewise a count of the number of the other 3 options each displaying in
their own box so that at a glance as we scroll through the records we can see
the number of corrects/incorrects etc that each respondent had...I've seen
lots of posts here about different methods of counting characters but nothing
quite like what I've described.

Or if there is a better way to count the responses other than dumping them
all together I'm game. I was trying to avoid going into each control's event
and coding, that is the only reason I chose to combine the values of all
fields into one string...but I'm stuck frome there.

Can anyone help? I'm using Access 2003.

Thanks,
 
P

pietlinden

I have a form with an unbound text box. The form also has about 50 fields
that coorespond to answers from a survey. All 50 have the same 4 options to
choose from (Correct, Incorrect, Reversed and NA) The unbound text box
contains the concatination of all 50 fields separated by a space. At the
bottom of the form I need to add 4 additional unbound text boxes that each
coorespond to 1 of the 4 possible answer...ie a count of the number of times
"correct" is in the string and have that number displayed in the field...
likewise a count of the number of the other 3 options each displaying in
their own box so that at a glance as we scroll through the records we cansee
the number of corrects/incorrects etc that each respondent had...I've seen
lots of posts here about different methods of counting characters but nothing
quite like what I've described.

Or if there is a better way to count the responses other than dumping them
all together I'm game. I was trying to avoid going into each control's event
and coding, that is the only reason I chose to combine the values of all
fields into one string...but I'm stuck frome there.

Can anyone help? I'm using Access 2003.

Thanks,

If all the data is already entered, you could use Split and loop
through the resulting arrays, but if this is a long term problem, I
would download Duane Hookum's "At Your Survey". it's built already,
so all you have to do is a little data entry and you're ready to go.

I know there's a copy at Roger Carlson's website... http://www.rogersaccesslibrary.com/
 
M

Marshall Barton

Wolfman311 said:
I have a form with an unbound text box. The form also has about 50 fields
that coorespond to answers from a survey. All 50 have the same 4 options to
choose from (Correct, Incorrect, Reversed and NA) The unbound text box
contains the concatination of all 50 fields separated by a space. At the
bottom of the form I need to add 4 additional unbound text boxes that each
coorespond to 1 of the 4 possible answer...ie a count of the number of times
"correct" is in the string and have that number displayed in the field...
likewise a count of the number of the other 3 options each displaying in
their own box so that at a glance as we scroll through the records we can see
the number of corrects/incorrects etc that each respondent had...I've seen
lots of posts here about different methods of counting characters but nothing
quite like what I've described.

Or if there is a better way to count the responses other than dumping them
all together I'm game. I was trying to avoid going into each control's event
and coding, that is the only reason I chose to combine the values of all
fields into one string...but I'm stuck frome there.


=Len(textbox) - Len(Replace(textbox, "Correct", "xxxxxx")
=Len(textbox) - Len(Replace(textbox, "NA", "x")

BTW, your table design has seriously flaws that will force
you into more and more of these hokey solutions. You should
have an answers table with one answer per record. Then you
could count the correct, etc. values using standard SQL
features.

You would probably benefit from careful study of the stuff
at:
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'
 
W

Wolfman311 via AccessMonster.com

Thanks for the reply. I know it has flaws, I didn't design it but I've been
ask to help with this so I'm trying. Problem is that I'm very new to using
vba. I think I understand what you have posted except for the "xxxxxx" what
do the x's represent?

Marshall said:
I have a form with an unbound text box. The form also has about 50 fields
that coorespond to answers from a survey. All 50 have the same 4 options to
[quoted text clipped - 13 lines]
and coding, that is the only reason I chose to combine the values of all
fields into one string...but I'm stuck frome there.

=Len(textbox) - Len(Replace(textbox, "Correct", "xxxxxx")
=Len(textbox) - Len(Replace(textbox, "NA", "x")

BTW, your table design has seriously flaws that will force
you into more and more of these hokey solutions. You should
have an answers table with one answer per record. Then you
could count the correct, etc. values using standard SQL
features.

You would probably benefit from careful study of the stuff
at:
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'
 
J

John Spencer

Marshall's expression works because it replaces correct (7 letters) with
xxxxxx (6 letters) and then calculates the difference in the number of letters
in the two versions of the string. So you need to replace 1 less letter than
the value you are trying to count.



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Thanks for the reply. I know it has flaws, I didn't design it but I've been
ask to help with this so I'm trying. Problem is that I'm very new to using
vba. I think I understand what you have posted except for the "xxxxxx" what
do the x's represent?

Marshall said:
I have a form with an unbound text box. The form also has about 50 fields
that coorespond to answers from a survey. All 50 have the same 4 options to
[quoted text clipped - 13 lines]
and coding, that is the only reason I chose to combine the values of all
fields into one string...but I'm stuck frome there.
=Len(textbox) - Len(Replace(textbox, "Correct", "xxxxxx")
=Len(textbox) - Len(Replace(textbox, "NA", "x")

BTW, your table design has seriously flaws that will force
you into more and more of these hokey solutions. You should
have an answers table with one answer per record. Then you
could count the correct, etc. values using standard SQL
features.

You would probably benefit from careful study of the stuff
at:
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top