[V4MD] Case insensitive search

[Irv Kalb]

Hi,

I'm down to my last problem to solve.  I've got everything I need 
working except for a case insensitive search.

I have a table which consists of a records where the each record is a 
single varChar field called AuthorYearRefKeywords.  Based on 
responses to earlier questions, when I create this table, I create it 
using the flags:

    [#fIndexed, #fIndexByWords]

This is working great and my searches work fine.  For example, if a 
user looks for "Hayes", I build a query string programmatically that 
looks like this:

"SELECT AuthorYearRefKeywords FROM Biblio WHERE 
(left(AuthorYearRefKeywords,5) = 'Hayes')"

and it finds the appropriate records.  If the user enters two words 
like "Hayes" and "Daniel", I build a query string programmatically 
that looks like this:

"SELECT AuthorYearRefKeywords FROM Biblio WHERE 
(left(AuthorYearRefKeywords,5) = 'Hayes') AND 
(left(AuthorYearRefKeywords,6) = 'Daniel')"

and this correctly returns records that have both of those words in them.

But this only works if the case of the query matches the case of the 
word(s) in the record.  If the user enters "hayes" (with a lower case 
"h"), there is no match.  I need it to match no matter what case is 
used in the query.  (It need to match "HAYES", "Hayes", "hAYES", 
"hayes", etc.).  The V12 equals operator does a case insensitive 
search by default, whereas, Valentina seems to do an exact case match.

I have read through the documentation and have done searches on-line, 
but I cannot figure out how to make this work.  I can see if this was 
a standard search, I could use the "upper" function to do the match. 
But I can't figure  out out how to do this on a field that is 
index-by-words where I am using the "left" function.

(I have also looked at the IndexByWords sample program, but that 
doesn't seem to work correctly - at least the way I expect it should.)

If anyone can tell me how to build the appropriate query that would 
be case insensitive, I would be very grateful.

Thanks in advance,

Irv




-- 

Multimedia R Us