Special Left function wanted...

[magmike]

I am submitting data to a source that says the [Keywords] field is limited
to 10 items. Items are devided by commas (","). Is there a way I could tell
the query to go Left 9 commas?

Mike

 

[Guest]

If someone held a water pistol up to my head, I'd try to nest a bunch of
InStr and Mid functions together to drill down that far. Maybe a good coder
could figure out a way to loop through a string.

 

[magmike]

Know where I could learn more about those functions? I'm a hack.

If someone held a water pistol up to my head, I'd try to nest a bunch of
InStr and Mid functions together to drill down that far. Maybe a good
coder
could figure out a way to loop through a string.

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I am submitting data to a source that says the [Keywords] field is
limited
to 10 items. Items are devided by commas (","). Is there a way I could
tell
the query to go Left 9 commas?

Mike

 

[Guest]

Me too! Here looks like something that might put you on the right path:

http://www.mvps.org/access/strings/str0003.htm
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Know where I could learn more about those functions? I'm a hack.

If someone held a water pistol up to my head, I'd try to nest a bunch of
InStr and Mid functions together to drill down that far. Maybe a good
coder
could figure out a way to loop through a string.

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I am submitting data to a source that says the [Keywords] field is
limited
to 10 items. Items are devided by commas (","). Is there a way I could
tell
the query to go Left 9 commas?

Mike

 

[Ken Snell \(MVP\)]

Perhaps something like this, although I am not sure if this is exactly what
you seek:

Mid([Keywords], InStr(Replace([Keywords], ",", " ", 1, 8, 1), ",") + 1)

 

[John Nurick]

Hi Mike,

I have no idea what you mean by getting a "query to go left 9 commas".
Please give a couple of examples of what you have and what you want to
get from it.

I am submitting data to a source that says the [Keywords] field is limited
to 10 items. Items are devided by commas (","). Is there a way I could tell
the query to go Left 9 commas?

Mike

 

[magmike]

I have no idea what you mean by getting a "query to go left 9 commas".

Please give a couple of examples of what you have and what you want to
get from it.

in each record is a field titled "keywords" which is used for searching the
records

each record is an article and data that defines it (like description,
author, wordcount, keywords, title, article, etc.)

searching a keyword field (which may contain 200 characters or less) is
faster than searching the memo field that the article is in, which could be
20,000 characters or even more.

each keyword or key-phrase within one record is separated by a comma (",")

the results of one record may look like this:
"cat, dog, animals, pets, pet-care, food, vetinarian, aspca, peta"

some of the records (from over-zealous authors) will contain a plethora of
keywords

however, i need to develop a query that limits the results of that field to
10 keywords/key-phrases

the keywords/key-phrases are separated by commas (",")

i'm thinking there may be a way to show Left 9 commas. Is there?

If not, could we go Left 10 words?

Mike Kline
http://www.netterweb.com
Where Articles and Ezines Come Together

 

[magmike]

I found records that ended in "," which shows that it is doing something,
but not stopping after 9 or 10 commas. Not sure what it is doing, since i'm
not real familiar with the Mid or InStr commands and the syntax that follows
in your line. Thanks for trying though.

Perhaps something like this, although I am not sure if this is exactly
what you seek:

Mid([Keywords], InStr(Replace([Keywords], ",", " ", 1, 8, 1), ",") + 1)

--

Ken Snell
<MS ACCESS MVP>

I am submitting data to a source that says the [Keywords] field is limited
to 10 items. Items are devided by commas (","). Is there a way I could
tell the query to go Left 9 commas?

Mike

 

[magmike]

magmike (a complete imbesile) scratched:

Know where I could learn more about those functions? I'm a hack.

I think that is exactly what I need! Unfortunately, it is beyond me, and I
wouldn't have the slightest on how to modify it to do what I need it to do!
:-(

Guess I have more of a project than I bargained for on my hands.

magmike

 

[Ken Snell \(MVP\)]

Based on your reply to John Nurick, what I posted is not at all what you
want. Instead, you appear to be seeking a special function that will search
a multivalue field to find up to 10 matches of values within that field?

If this is what you seek, then I don't see why you want a "Left" selection
of the field's value? You appear to be seeking a "find and concatenate" type
of functionality?

You need to show us full examples of the data in the records, what you want
to enter in order to find certain values, and what you want the programming
to return to you as a string.

--

Ken Snell
<MS ACCESS MVP>

I found records that ended in "," which shows that it is doing something,
but not stopping after 9 or 10 commas. Not sure what it is doing, since i'm
not real familiar with the Mid or InStr commands and the syntax that
follows in your line. Thanks for trying though.

Perhaps something like this, although I am not sure if this is exactly
what you seek:

Mid([Keywords], InStr(Replace([Keywords], ",", " ", 1, 8, 1), ",") + 1)

--

Ken Snell
<MS ACCESS MVP>

I am submitting data to a source that says the [Keywords] field is
limited to 10 items. Items are devided by commas (","). Is there a way I
could tell the query to go Left 9 commas?

Mike

 

[John Nurick]

If I've got this right, you want something that takes a string
containing a list like this

cat, dog, animals, pets, pet-care, food, vetinarian, aspca, peta,
pedigree, chum, rottweiler, corgi, persian, goldfish, koi carp...

and returns just the first 10 items.

This can be done by writing a function that uses successive calls to
InStr() to find successive commas, but I'm lazy and would use a regular
expression. If you get the rgxExtract function from
http://www.j.nurick.dial.pipex.com/Code/vbRegex/rgxExtract.htm , this
should do the job:

rgxExtract([keywords], "^((?:.*?(?:,|$)\s*){1,10}).*")

If you want to change the number of words extracted, change the 10 to
whatever number you need.

 

[Rick Brandt]

I have no idea what you mean by getting a "query to go left 9

in each record is a field titled "keywords" which is used for
searching the records

each record is an article and data that defines it (like description,
author, wordcount, keywords, title, article, etc.)

searching a keyword field (which may contain 200 characters or less)
is faster than searching the memo field that the article is in, which
could be 20,000 characters or even more.

each keyword or key-phrase within one record is separated by a comma
(",")
the results of one record may look like this:
"cat, dog, animals, pets, pet-care, food, vetinarian, aspca, peta"

some of the records (from over-zealous authors) will contain a
plethora of keywords

however, i need to develop a query that limits the results of that
field to 10 keywords/key-phrases

the keywords/key-phrases are separated by commas (",")

i'm thinking there may be a way to show Left 9 commas. Is there?

If not, could we go Left 10 words?

Mike Kline
http://www.netterweb.com
Where Articles and Ezines Come Together

Those keywords should be stored in a separate (related) table with one word per
record in the new table. Then this field could be indexed and performance would
be good no matter how many keywords any particular record might have.

 

[magmike]

That's beautiful! Works fantastical!
(I think I'm going to cry)

A Very Happy magmike

If I've got this right, you want something that takes a string
containing a list like this

cat, dog, animals, pets, pet-care, food, vetinarian, aspca, peta,
pedigree, chum, rottweiler, corgi, persian, goldfish, koi carp...

and returns just the first 10 items.

This can be done by writing a function that uses successive calls to
InStr() to find successive commas, but I'm lazy and would use a regular
expression. If you get the rgxExtract function from
http://www.j.nurick.dial.pipex.com/Code/vbRegex/rgxExtract.htm , this
should do the job:

rgxExtract([keywords], "^((?:.*?(?:,|$)\s*){1,10}).*")

If you want to change the number of words extracted, change the 10 to
whatever number you need.

in each record is a field titled "keywords" which is used for searching
the
records

each record is an article and data that defines it (like description,
author, wordcount, keywords, title, article, etc.)

searching a keyword field (which may contain 200 characters or less) is
faster than searching the memo field that the article is in, which could
be
20,000 characters or even more.

each keyword or key-phrase within one record is separated by a comma (",")

the results of one record may look like this:
"cat, dog, animals, pets, pet-care, food, vetinarian, aspca, peta"

some of the records (from over-zealous authors) will contain a plethora of
keywords

however, i need to develop a query that limits the results of that field
to
10 keywords/key-phrases

the keywords/key-phrases are separated by commas (",")

i'm thinking there may be a way to show Left 9 commas. Is there?

If not, could we go Left 10 words?

Mike Kline
http://www.netterweb.com
Where Articles and Ezines Come Together

 

[magmike]

Those keywords should be stored in a separate (related) table with one

word per record in the new table. Then this field could be indexed and
performance would be good no matter how many keywords any particular
record might have.

performance in the keyword field is not a problem. the problem is with
searching over 40,000 records in the article memo field (with a database
size of about 500 MB). my point was, that the use of keywords (entered by
the author of the article) was for better performance. the issue now, is to
limit the results of this field (since I already have 40,000 records with
keywords that aren't stored one word per record in a separate table) in a
query to 10 words or phrases (defined by the separation of a comma). John
Nurick in his reply, gifted me a great function for that (thanks John!).

However, i am intrigued by the idea of storing the keyords/phrases one per
record in a separate table. Would it be possible to take the current records
(again over 40,000) and separate out the keywords/phrases one at a time
(adding the ID# of the relating record along with it?

 

[John Nurick]

That's beautiful! Works fantastical!
(I think I'm going to cry)

You can download a tissue from www.getakleenex.com !

 

[John Nurick]

rgxExtract can do this too, using a different regular expression and the
optional Item argument.

(I found a bug while testing it and have just updated the version on the
web page, now dated 14/01/06:
http://www.j.nurick.dial.pipex.com/Code/vbRegex/rgxExtract.htm)

In a query, use

RgxExtract([Keywords], "[^,]+(?=,|$)", 0,, False)

to get the first keyword in the list. Replace the 0 in the Item argument
with 1 to get the second, and so on. So you run a succession of append
queries, as many as there are words in the longest list.

The last argument tells rgxExtract to return Null on any error. Without
this, if the value in the Item argument takes you past the end of the
list, you'll get a subscript out of range error - for every record the
query examines!

 

[John Nurick]

Or, and MUCH more sensibly,

Public Function SafeSplit(V As Variant, _
Delim As String, Item As Long) As Variant

On Error Resume Next 'to return Null if Item is out of range

SafeSplit = Split(V, Delim)(Item)

End Function

and in the query use

SafeSplit([Keywords], ",", 0)

and so on.

 

[magmike]

I think that is an extra comma before FALSE, as removing it stopped the
error I was getting.

rgxExtract can do this too, using a different regular expression and the
optional Item argument.

(I found a bug while testing it and have just updated the version on the
web page, now dated 14/01/06:
http://www.j.nurick.dial.pipex.com/Code/vbRegex/rgxExtract.htm)

In a query, use

RgxExtract([Keywords], "[^,]+(?=,|$)", 0,, False)

to get the first keyword in the list. Replace the 0 in the Item argument
with 1 to get the second, and so on. So you run a succession of append
queries, as many as there are words in the longest list.

The last argument tells rgxExtract to return Null on any error. Without
this, if the value in the Item argument takes you past the end of the
list, you'll get a subscript out of range error - for every record the
query examines!

performance in the keyword field is not a problem. the problem is with
searching over 40,000 records in the article memo field (with a database
size of about 500 MB). my point was, that the use of keywords (entered by
the author of the article) was for better performance. the issue now, is
to
limit the results of this field (since I already have 40,000 records with
keywords that aren't stored one word per record in a separate table) in a
query to 10 words or phrases (defined by the separation of a comma). John
Nurick in his reply, gifted me a great function for that (thanks John!).

However, i am intrigued by the idea of storing the keyords/phrases one per
record in a separate table. Would it be possible to take the current
records
(again over 40,000) and separate out the keywords/phrases one at a time
(adding the ID# of the relating record along with it?

 

[magmike]

This second one works without a hitch. The other was still giving me errors.
The only thing now is, is there an easy way to get rid of the space after
the comma and only when the next character after the comma is a space? Some
folks leave out the space after the comma (very good) while others are
including a space after the comma. Therefore, some results in the query will
be as such: " sports".

Or, and MUCH more sensibly,

Public Function SafeSplit(V As Variant, _
Delim As String, Item As Long) As Variant

On Error Resume Next 'to return Null if Item is out of range

SafeSplit = Split(V, Delim)(Item)

End Function

and in the query use

SafeSplit([Keywords], ",", 0)

and so on.

performance in the keyword field is not a problem. the problem is with
searching over 40,000 records in the article memo field (with a database
size of about 500 MB). my point was, that the use of keywords (entered by
the author of the article) was for better performance. the issue now, is
to
limit the results of this field (since I already have 40,000 records with
keywords that aren't stored one word per record in a separate table) in a
query to 10 words or phrases (defined by the separation of a comma). John
Nurick in his reply, gifted me a great function for that (thanks John!).

However, i am intrigued by the idea of storing the keyords/phrases one per
record in a separate table. Would it be possible to take the current
records
(again over 40,000) and separate out the keywords/phrases one at a time
(adding the ID# of the relating record along with it?

 

[magmike]

I just created a query with two fields (Keyword & ID). Keyword is the
separated keyword value, and ID is the ID of the article record which the
keyword needs to be connected with. I created a corresponding table to house
these new records. Then I made the qeury an append query. However when I run
the query, I get the following error:

The Microsoft Jet database engine stopped the process because you and
another user are attempting to change the same data at the same time.

However, this isn't true as far as I can tell. I've restarted too, and still
get the same error. Any insight?

Or, and MUCH more sensibly,

Public Function SafeSplit(V As Variant, _
Delim As String, Item As Long) As Variant

On Error Resume Next 'to return Null if Item is out of range

SafeSplit = Split(V, Delim)(Item)

End Function

and in the query use

SafeSplit([Keywords], ",", 0)

and so on.

performance in the keyword field is not a problem. the problem is with
searching over 40,000 records in the article memo field (with a database
size of about 500 MB). my point was, that the use of keywords (entered by
the author of the article) was for better performance. the issue now, is
to
limit the results of this field (since I already have 40,000 records with
keywords that aren't stored one word per record in a separate table) in a
query to 10 words or phrases (defined by the separation of a comma). John
Nurick in his reply, gifted me a great function for that (thanks John!).

However, i am intrigued by the idea of storing the keyords/phrases one per
record in a separate table. Would it be possible to take the current
records
(again over 40,000) and separate out the keywords/phrases one at a time
(adding the ID# of the relating record along with it?