Search Criteria Problem

[Don]

I have an unbound form with a listbox called "lstCUSTOMER", a textbox called
"txtLIKE", and a Frame with two search criteria. The problem is with my code
for search criteria 1. If I type more than just a few letters in the text
box, my ListCount recognizes the number of records, but no records are
displayed. Search criteria 2 works every time.

My code is below. Could someone give me some insight?

If Me.Frame4 = 1 Then
Me.lstCUSTOMER.RowSource = "SELECT [qryLIKE].[CUST_NO],
[qryLIKE].[CUSTOMER], [qryLIKE].[CITY], [qryLIKE].[ST], [qryLIKE].[RT] FROM
qryLIKE WHERE " & _
"[qryLIKE].[CUSTOMER] LIKE '" & Me.txtLIKE & "*'"
Else
If Me.Frame4 = 2 Then
Me.lstCUSTOMER.RowSource = "SELECT [qryLIKE].[CUST_NO],
[qryLIKE].[CUSTOMER], [qryLIKE].[CITY], [qryLIKE].[ST], [qryLIKE].[RT] FROM
qryLIKE WHERE " & _
"[qryLIKE].[CUST_NO] LIKE '" & Me.txtLIKE & "*'"
End If
End If

If Me.txtCOUNT = 0 Then
Me.Caption = "NO CUSTOMERS FOUND MATCHING THE SEARCH CRITERIA."
Else
If Me.txtCOUNT = 1 Then
Me.Caption = "1 CUSTOMER FOUND MATCHING THE SEARCH CRITERIA."
Else
If Me.txtCOUNT > 1 Then
Me.Caption = Me.txtCOUNT & " CUSTOMERS FOUND MATCHING THE
SEARCH CRITERIA."
End If
End If
End If

Me.txtLIKE.SetFocus

 

[Don]

I think I've discovered the problem . . . now I need a solution. This search
does not like any search word that uses an apostrophe. How can I get around
this?

 

[Marshall Barton]

I have an unbound form with a listbox called "lstCUSTOMER", a textbox called
"txtLIKE", and a Frame with two search criteria. The problem is with my code
for search criteria 1. If I type more than just a few letters in the text
box, my ListCount recognizes the number of records, but no records are
displayed. Search criteria 2 works every time.

My code is below. Could someone give me some insight?

If Me.Frame4 = 1 Then
Me.lstCUSTOMER.RowSource = "SELECT [qryLIKE].[CUST_NO],
[qryLIKE].[CUSTOMER], [qryLIKE].[CITY], [qryLIKE].[ST], [qryLIKE].[RT] FROM
qryLIKE WHERE " & _
"[qryLIKE].[CUSTOMER] LIKE '" & Me.txtLIKE & "*'"
Else

[snip]


If txtLike might contain an apostrophe, then try using:

& "WHERE CUSTOMER LIKE """ & Me.txtLIKE & "*"" "

Or if it could contain either ' and/or "

& "WHERE CUSTOMER LIKE '" & Replace(Me.txtLIKE, "'". "''"
& "*' "

 

[Michael Gramelspacher]

I think I've discovered the problem . . . now I need a solution. This search
does not like any search word that uses an apostrophe. How can I get around
this?

use the Replace Function to replace a single apostrophe with two apostrophes in
every field that can have apostrophes.

 

[Don]

Thank you for your feedback - - this fixed my issue.
--
Don Rountree

I have an unbound form with a listbox called "lstCUSTOMER", a textbox called
"txtLIKE", and a Frame with two search criteria. The problem is with my code
for search criteria 1. If I type more than just a few letters in the text
box, my ListCount recognizes the number of records, but no records are
displayed. Search criteria 2 works every time.

My code is below. Could someone give me some insight?

If Me.Frame4 = 1 Then
Me.lstCUSTOMER.RowSource = "SELECT [qryLIKE].[CUST_NO],
[qryLIKE].[CUSTOMER], [qryLIKE].[CITY], [qryLIKE].[ST], [qryLIKE].[RT] FROM
qryLIKE WHERE " & _
"[qryLIKE].[CUSTOMER] LIKE '" & Me.txtLIKE & "*'"
Else

[snip]


If txtLike might contain an apostrophe, then try using:

& "WHERE CUSTOMER LIKE """ & Me.txtLIKE & "*"" "

Or if it could contain either ' and/or "

& "WHERE CUSTOMER LIKE '" & Replace(Me.txtLIKE, "'". "''"
& "*' "

 

[Don]

Thank you for your feedback.
--
Don Rountree

use the Replace Function to replace a single apostrophe with two apostrophes in
every field that can have apostrophes.