Split InStr function Count Question

[pburling]

Thanks for taking the time to help.

I have values in two text boxes, Me.CALL and txt.LastCall.
The values in both boxes are separated with a forward slash.
For example, WCCC/WDDD/WEEE is in txt.CALL and
WXXX,WCCC,WEEE is in txt.Last Call.

I want to see if any of the values in txt.CALL are in txt.LastCALL. I
have done this using the Split function combined with the InStr
function. I split txt.Call and then check each value against
txt.LastCall.
This works fine. Code Below

I want to keep track of the number of times a string from txt.CALL is
found in txt.Last Call. To clarify, if WCCC is found I want a msg box
with "found 1 station" and then if WDDD is found I want the msg box to
show "found 2 stations" etc.

I would appreciate it if someone could suggest how to go about
this.....Thanks Peter

Private Sub TxtLastCall_Click()
Dim strCall
Dim strValue

strValue = Me.CALL
StrAryWords = Split(strValue, "/")
'strAryWords is now an array
Dim i
For i = 0 To UBound(StrAryWords)
If InStr(Me.TxtLastCall, StrAryWords(i)) = 0 Then
MsgBox (StrAryWords(i))
End If
Next

 

[Graham Mandeno]

Hi Peter

You're probably not going to like this <sorry!>, but the correct advice to
you is that your design is faulty. One of the principles of relational
database design is that you should *never* store more than one value in a
single field. Instead, you should create a related table with each record
containing *one* of the multiple values, and linked to the "master" record
via a field containing the master record's primary key value. Then it is
easy to count frequencies and compare values and manipulate your data in
whatever other way you please.

It is difficult to tell, from the information you've given, exactly what is
the best way to go about this redesign. If you would like to post back some
more details of your existing table structures and the intended purpose of
your database, we could help you further with getting it right.

 

[pburling]

try this:

Dim strCall
Dim ct As Integer
Dim strValue
Dim strarywords
strValue = Me.txtCall      'I would change name from CALL to txtCall as CALL
is a statement name
strarywords = Split(strValue, "/")
'strAryWords is now an array
Dim i
For i = 0 To UBound(strarywords)
Debug.Print strarywords(i)
     If InStr(Me.txtLastCall, strarywords(i)) <> 0 Then
    ct = ct + 1
    End If
Next
MsgBox " count is " & ct, vbOKOnly

Thanks everyone.
Damon Heron - Worked beautifully.
Will change the Me.Call to txt.Call per your recommendation. Again,
Thank you.

Graham Mandeno - you are right on a basic design flaw...I have been
wrestling with what my friend wants and am thinking of redoing the
table structure so that basic tenets are kept. Thanks for the polite
reminder to keep the rules.