how to use * in where statment to retreive all record

[ABDULLAH1398]

I am tried to make dynamic sql statement.... But I still face problem I can not retrieve all record thru use if i put me.cb_location.text= *


SELECT * FROM New_Table where Loc_Code Like '" & me.cb_location.Text& " ' ;

[bergy]

I am tried to make dynamic sql statement.... But I still face problem I can not retrieve all record thru use if i put me.cb_location.text= *

SELECT * FROM New_Table where Loc_Code Like '" & me.cb_location.Text& " ' ;

There are quite a few ways to accomplish this. I think the easiest (and probably worst) way to do this would be to simply replace the * character with a % sign. What happens in your current statement is that you're getting a SQL statement that looks like this:

SELECT * FROM  New_Table where Loc_Code Like '*';

SQL does not see '*' in quotes as a wildcard character. You need to use the percent sign to specify a wildcard, so your SQL query should look like:

SELECT * FROM  New_Table where Loc_Code Like '%';

I'm assuming you're using VB because of the Me keyword so here is a couple lines of VB to put in above your query:

Dim myLocCodeSearch as String = ""
If cb_location.Text = "*" Then myLocCodeSearch = "%" Else myLocCodeSearch = cb_location.Text
Dim mySQLQuery as String = "SELECT * FROM  New_Table where Loc_Code Like '" & myLocCodeSearch & "';"

The above code would work to replace a single * character with the % sign. If you're wanting to allow someone type in say New* and return New York, New Jersey and New Hampshire you'll need to do a replace which would look like this:

Dim myLocCodeSearch as String = cb_location.Text
myLocCodeSearch = myLocCodeSearch.Replace("*", "%")
Dim mySQLQuery as String = "SELECT * FROM  New_Table  where Loc_Code Like '" & myLocCodeSearch & "';"

Hope that points you in the right direction!

Adam