Uppercase and * in search query

[Wind54Surfer]

Hi all,

I am using the following:

If Me.txtDescription > "" Then
varWhere = varWhere & "[Description] LIKE """ & Me.txtDescription &
"*"" AND "

to search but have two problems:

1-doesn't work with uppercase words
2-doesn't work unless using "*", if word is in middle of sentence

I adapted the code from a sample found in the web, don't know how to make
changes.

Can someone help me.

Thanks in advance,
Emilio

 

[John Spencer]

Access is NOT case-sensitive so you can't use the standard comparison
operators to search.

If you are looking for a word (or string) anywhere in the Description field

varWhere =
varWhere & "[Description] LIKE ""*" & Me.txtDescription & "*"" AND "

If you really need to do a case-sensitive search for a word anywhere in the
string you are going to have to use some VBA. Simplest would be to use the
Instr function if you are doing a contains search.

Instr(1,[Description],Me.txtDescription,0)>0

So you would probably need something lijke the following to do a case
sensitive search.

varWhere =
varWhere & " Instr(1,[Description],""" & Me.txtDescription & """,0)>0"

Warning, this will be slow

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Dale Fye]

When you say "doesn't work with uppercase words" I assume you mean that if
you are looking for a word that is in all uppercase, it is returning records
where the word is in lower or mixed case, and not just those where the word
appears in all caps. Is that correct? Unfortunately, Access searches are
not case sensitive, so you are going to get records in your search results
which don't match an all uppercase string. You could use a function to parse
all of the text passed to it and us the strComp( ) function to test for a
binary match. I've got a function that does this, but it is slow for large
data sets when searching memo fields.

Public Function fnStringSearch(SearchIn As Variant, SearchFor As String) As
Boolean

Dim aText() As String
Dim intLoop As Integer


fnStringSearch = False
If IsNull(SearchIn) = True Then
'do nothing
ElseIf InStr(SearchIn, SearchFor) = 0 Then
'do nothing
Else
aText = Split(SearchIn, " ")
For intLoop = LBound(aText) To UBound(aText)

If StrComp(aText(intLoop), SearchFor, vbBinaryCompare) = 0 Then
fnStringSearch = True
Exit For
End If
Next
End If

End Function

I'm not sure what you mean by your 2nd issue, but I'll take a stab. The way
you have your criteria written, it will only search for occurrances of the
word at the beginning of the string being searched. If you want to search
for the word anywhere in the string, you will have to use:

varWhere = varWhere & "[Description] LIKE ""*" _
& Me.txtDescription & "*"" AND "

Notice that I added another * to the beginning of the string. It will now
get interpreted like:

[Description] like "*xxxxxxx*"

 

[Dale Fye]

BTW, depending on the formatting of the string you are going to search, you
might want to consider removing all of the punctionation marks in the string
being searched and replacing them with spaces. If the word you are searching
for is wrapped by a punctuation mark, it will not pass a binary comparison.
I would add lines similar to the following for all the major punctuation.

SearchIn = replace(SearchIn, "(", " ")
SearchIn = replace(SearchIn, ")", " ")
SearchIn = Replace(SearchIn, ".", " ")

 

[Dale Fye]

Learn something new every day. Has the compare parameter been around
forever, or was it added in 2007. Turns out I've been doing it the hard way
all this time.

----
HTH
Dale

Access is NOT case-sensitive so you can't use the standard comparison
operators to search.

If you are looking for a word (or string) anywhere in the Description field

varWhere =
varWhere & "[Description] LIKE ""*" & Me.txtDescription & "*"" AND "

If you really need to do a case-sensitive search for a word anywhere in the
string you are going to have to use some VBA. Simplest would be to use the
Instr function if you are doing a contains search.

Instr(1,[Description],Me.txtDescription,0)>0

So you would probably need something lijke the following to do a case
sensitive search.

varWhere =
varWhere & " Instr(1,[Description],""" & Me.txtDescription & """,0)>0"

Warning, this will be slow

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi all,

I am using the following:

If Me.txtDescription > "" Then
varWhere = varWhere & "[Description] LIKE """ & Me.txtDescription &
"*"" AND "

to search but have two problems:

1-doesn't work with uppercase words
2-doesn't work unless using "*", if word is in middle of sentence

I adapted the code from a sample found in the web, don't know how to make
changes.

Can someone help me.

Thanks in advance,
Emilio

 

[John Spencer]

As far as I know it has been around since at least Access 97. And Probably
before that.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County