"Bookending" wildcards in a Where Clause

[Guest]

I've found posts that describe this exact situation, but I can't get the
solutions to work. I know I'm missing something really simple, but I've
spent more than an hour on it and need to move on.

On a lookup form, the user can type in all or part of a Client's Name into
the text box [txtClientName], hit submit, and have the results populate a
list box. The submit button uses VBA coding to build the listbox's
rowsource. Here's my WHERE Clause:

sWhere = "WHERE [ClientName] LIKE '*" & [txtClientName] & "*'"

I only get results that start with whatever is in [txtClientName], but I
want results that have it anywhere within the name. To illustrate:

If [txtClientName] = "Corp" then

I want results of:
"Corporate Tax Attorney"
"Hello Corporation"

but I'm only getting:
"Corporate Tax Attorney"

Any assistance is greatly appreciated.

 

[John Spencer (MVP)]

Don't see anything in your code fragment to cause the results you are getting.

Have you tried stopping your code at the where line and examining the variable
sWhere to make sure it is what you expect.

You might try the following, but it really should make no difference.

sWhere = "WHERE [ClientName] LIKE ""*" & [txtClientName] & "*"" "

 

[Guest]

Yup, as I said, it was something simple I was missing. Your post was
actually quite helpful, because when you didn't see a problem with that one
line, I focused more on the rest of the code. The solution came within 30
seconds.

Below is my full WHERE Clause section. The problem is that even when
[txtClientID] was left blank by the user, IsNull returned a true value. As a
result, VBA was skipping the line of code that I was trying to fix.
Hopefully seeing this story will save someone out there some frustration.
John, thanks for your time and assistance.

sSelect = "SELECT [ClientID], [ClientName], [ClientSegment] "
sFrom = "FROM [tblClientInfo] "
sOrder = "ORDER BY [ClientName]"
sWhere = ""

If IsNull(txtClientID) = False Then
sWhere = "WHERE [ClientID] LIKE """ & [txtClientID] & "*"" "
End If
If IsNull(txtClientName) = False Then
If sWhere = "" Then
sWhere = "WHERE [ClientName] LIKE ""*" & [txtClientName] & "*"" "
Else
sWhere = sWhere & "AND [ClientName] LIKE ""*" & [txtClientName]
& "*"" "
End If
End If

lstClient.RowSource = sSelect & sFrom & sWhere & sOrder

Don't see anything in your code fragment to cause the results you are getting.

Have you tried stopping your code at the where line and examining the variable
sWhere to make sure it is what you expect.

You might try the following, but it really should make no difference.

sWhere = "WHERE [ClientName] LIKE ""*" & [txtClientName] & "*"" "

I've found posts that describe this exact situation, but I can't get the
solutions to work. I know I'm missing something really simple, but I've
spent more than an hour on it and need to move on.

On a lookup form, the user can type in all or part of a Client's Name into
the text box [txtClientName], hit submit, and have the results populate a
list box. The submit button uses VBA coding to build the listbox's
rowsource. Here's my WHERE Clause:

sWhere = "WHERE [ClientName] LIKE '*" & [txtClientName] & "*'"

I only get results that start with whatever is in [txtClientName], but I
want results that have it anywhere within the name. To illustrate:

If [txtClientName] = "Corp" then

I want results of:
"Corporate Tax Attorney"
"Hello Corporation"

but I'm only getting:
"Corporate Tax Attorney"

Any assistance is greatly appreciated.

 

[Guest]

sorry, I mean IsNull returned a "False" value

Yup, as I said, it was something simple I was missing. Your post was
actually quite helpful, because when you didn't see a problem with that one
line, I focused more on the rest of the code. The solution came within 30
seconds.

Below is my full WHERE Clause section. The problem is that even when
[txtClientID] was left blank by the user, IsNull returned a true value. As a
result, VBA was skipping the line of code that I was trying to fix.
Hopefully seeing this story will save someone out there some frustration.
John, thanks for your time and assistance.

sSelect = "SELECT [ClientID], [ClientName], [ClientSegment] "
sFrom = "FROM [tblClientInfo] "
sOrder = "ORDER BY [ClientName]"
sWhere = ""

If IsNull(txtClientID) = False Then
sWhere = "WHERE [ClientID] LIKE """ & [txtClientID] & "*"" "
End If
If IsNull(txtClientName) = False Then
If sWhere = "" Then
sWhere = "WHERE [ClientName] LIKE ""*" & [txtClientName] & "*"" "
Else
sWhere = sWhere & "AND [ClientName] LIKE ""*" & [txtClientName]
& "*"" "
End If
End If

lstClient.RowSource = sSelect & sFrom & sWhere & sOrder

Don't see anything in your code fragment to cause the results you are getting.

Have you tried stopping your code at the where line and examining the variable
sWhere to make sure it is what you expect.

You might try the following, but it really should make no difference.

sWhere = "WHERE [ClientName] LIKE ""*" & [txtClientName] & "*"" "

I've found posts that describe this exact situation, but I can't get the
solutions to work. I know I'm missing something really simple, but I've
spent more than an hour on it and need to move on.

On a lookup form, the user can type in all or part of a Client's Name into
the text box [txtClientName], hit submit, and have the results populate a
list box. The submit button uses VBA coding to build the listbox's
rowsource. Here's my WHERE Clause:

sWhere = "WHERE [ClientName] LIKE '*" & [txtClientName] & "*'"

I only get results that start with whatever is in [txtClientName], but I
want results that have it anywhere within the name. To illustrate:

If [txtClientName] = "Corp" then

I want results of:
"Corporate Tax Attorney"
"Hello Corporation"

but I'm only getting:
"Corporate Tax Attorney"

Any assistance is greatly appreciated.

 

[james.igoe]

just a minor suggestion, but I find it helpful to use ascii charaters
for quotation marks:

sWhere = "WHERE [ClientName] LIKE " & chr(34) & "*" & [txtClientName] &
"*" & chr(34)

.....easier to read and modify

 

[Tim Ferguson]

sWhere = "WHERE [ClientName] LIKE " & chr(34) & "*" & [txtClientName] &
"*" & chr(34)

....easier to read and modify

FWIW, it's even easier to read and modify with a function:

"... WHERE ClientName LIKE " & SQLQuote("*" & txtClientName & "*")

because you can then "lose" all the stuff about embedded quote marks and
NULLs at the same time.

Even better, create a Class to handle the whole putting a SQL string
together, creating a connection and returning the recordset:

Set sqlTemp = New cSQLCommand
With sqlTemp
.Add "INSERT INTO Referrals (SerialNum, ReferDt)"
.Add "VALUES (" & txtSerialNum & ", "
.Add " " & .QuoteDate(CDate(strNewDate))
.Add " )"

' MsgBox .SQL

If .Execute <> 0 Then
MsgBox "Update not completed"
End If

End With


Hope that helps


Tim F