Special Left function wanted...

[Douglas J. Steele]

Use the Trim function.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

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.

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?

 

[magmike]

nifty!

Use the Trim function.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

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.


On Sat, 14 Jan 2006 11:59:55 -0500, "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?

 

[magmike]

after doing a compact and repair, this error does not appear.

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.

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?