simple function problem

[niki]

hey!
Up to now this function helps me to find within a field the words situated after the word "Keywords:". This is, to talk in a visual manner, the left limit of my parsing area.

function:
Public Function TheKeyWords(longText As String) As String
Dim varPos As Variant
varPos = InStr(1, longText, "Keywords:")
If varPos > 0 Then
TheKeyWords = Mid$(longText, InStr(1, longText, "Keywords:") + 9)
Else
TheKeyWords = ""
End If
End Function

But being only a machine, my computer doesn't understand when the keywords stop. Due to an importing data problem, some words manage to get in the field keywords without being keywords.

This non interesting part of the field always starts with the sentence "Partners already aquired". How could I, sort of like my left limit earlier, have this sentence be my right limit, but this limit being not included in the field?

In other words, How could I tell my function, with an IF loop, that if it encounters "Partners already aquired" when searching through the field, it should stop and not give back this sentence as a result??
thx to anyone who can help
nico

 

[NJudson]

Assuming that your keyword field is something like this:

KEYWORD Partners already acquired

you could search the string for a space " " and use everything to the left of the space.

For i = 1 to len(keyword)
If Mid(keyword, 1, " ") then
keyword = left(keyword, i)
Exit For
End If
Next i

This should trim off all the garbage data you don't want.

HTH

 

[Fizzio]

Are you using A2k or 97 as if you are using "k you can use the Split method and then parse the resulting array. If you are using 97, I'll post a splitstring function that you could adapt to your own needs.

 

[niki]

It's a nice try there but it won't work. As I say in the message I have a parsing problem in an earlier step of my project. This problem results in the fact that I can't view the data before importing it into access. Therefore, the sentence "Partners alreday aquired" appears without warning right next to a keyword, without space or anything...
That's why I want to keep my first function and to make an IF loop which states that if it encounters "Partners already aquired" when searching through the field, it should stop and not give it back, along with the following words, this part of the field as a result.

nico

 

[niki]

>>>Fizzio
I am using access 2002, but I really want to insert an IF LOOP in my theKeywords function. I want to do that because after parametering all my queries and functions, I want to automate this task. Doing the full parsing task (e.g. search "keywords:"; return the keywords if "keywords:" is read and return "null" if not; stop at "Partners already aquired" if present in the field) will facilitate and will lighten the task for my computer...
what do you think?

nico

 

[Fizzio]

Try this A2k above only.

 

[niki]

wow fizzio that was more than enough man!!!!
actually I need this as a VBA function that I will use with a query on a specific field. That's why I want to integrate the functionnality of finding the keywords between the values "keywords:" and "Partners already aquired" within a field and transfering that to a second clean database (clean as without garbage data) by using an add query...
Your solution is perfect but really too complicated for me... I usually understand bits and pieces of a VBA code but yours is like chinese to me...
How can I just add an IF loop to my function and only get the valuable data???
thx
nico

 

[Fizzio]

Here you go with simple function in that can be used in the query or in the form - simpler than the array code but if you want to split the keywords up, it will need a little more work - you could combine the 2 methods to process the string then split it up.

hope this helps.