Anyone got any ideas?

[Rob Meade]

Hi all,

This will sound REALLY stupid, but I wrote some code a fair while ago
(beginning of this year), and I've done something a specific way, but for
the life of me I can't remember why, or whether the difference between the 2
ways that are used in the code really matters...

I think Aaron had some suggestions at the time...

Anyway...here we go...

I've got several columns in a database table, when I'm handling search
criteria from a form, I have something like this:

For intLoop = 0 To UBound(aSearchCriteria)

SQL2 = SQL2 & "' ' + WebsiteName + ' ' LIKE '% " &
aSearchCriteria(intLoop) & " %' AND "

Next

Note: This is a quick snip from a big page...

As you can see, I'm adding the ' ' at the beginning and end of the field,
and then doing the same with the search criteria, I guess this was to find
'words' (a word being defined in this scenario with a space either side -
ignoring punctuation etc for now).

I seem to have done it like this for most of the 'name' columns, even some
description ones, but anything that was deemed as 'content' has been done
like this:

For intLoop = 0 To UBound(aSearchCriteria)

SQL2 = SQL2 & "WebsiteMetaKeywords LIKE '" & aSearchCriteria(intLoop) &
" %' OR "
SQL2 = SQL2 & "WebsiteMetaKeywords LIKE '% " & aSearchCriteria(intLoop)
& " %' OR "
SQL2 = SQL2 & "WebsiteMetaKeywords LIKE '% " & aSearchCriteria(intLoop)
& "' OR "

Next


As you can see, this time I have 3 parts, a start of field, a middle of
field and an end of field...the ends (beginning and end) have the % wildcard
missing where appropriate (ie, there wouldn't be any content after this to
worry about anyway)...the middle one is similar to the first method above
for finding 'words'..

Now, I'm in the process of adding some code to support words that having
punctuation added to them in my database, but when a user searches, they
dont enter it...

For example:

keywords - "umbrella company, pensions, insurance, phi, ir35"

So, if this was the content of one of my columns in the table, a user is
unlikely to be entering comma's when they search, more likely they might
just enter "pensions", therefore in my new code I have a small array of the
most likely punctuation;

Dim aPunctuation(9)

aPunctuation(0) = "" ' added for no punctuation
aPunctuation(1) = "."
aPunctuation(2) = ","
aPunctuation(3) = ";"
aPunctuation(4) = ":"
aPunctuation(5) = "!"
aPunctuation(6) = "?"
aPunctuation(7) = ")"
aPunctuation(8) = """"

The first item was added as a quick way of having just the normal option,
ie, without any punctuation when a search is performed..

This would now test against my column for, as an example;

pensions
pensions.
pensions,
pensions;
.....and so on...perfect...

What I cant work out though, and being really tired right now isn't helping,
is whether it makes any difference having the ' ' + column_name + ' ', or
the other way with the beginning, middle, end....

Can anyone think of a reason why this would make a difference...it might
well have been at the time that the 3 parts way was better and I only added
it to the columns I thought necessary at the time (being lazy), but I truly
cant remember...

I know I'm asking a lot here, practically asking you guys to remember for me
D But if anyone can see from the code above why I might have gone this
way I'd be eager to hear from you )

Thanks in advance for any help.

Regards

Rob

 

[Rob Meade]

Jesus, it just took me about 15 minutes to type all that, then I amazing
stumbled across a previous post I made here where I answered my own
question - LOL!

I had the 2 ways of doing this because you cant seem to concatenate ' ' to a
column that has a TEXT data type! Hence the other method for those
columns....

*sigh*

Thanks anyway.... )

Rob