[RESOLVED] wildcard in where part of Select

[Muddy]

I am building a Select Statement based on some listbox selections. When * is selected in the listbox I want it to be a wildcard. When I build it like this it doesn't work:

"Select a from TableA where b = " & Listbox1.Text

It doesn't work when * is the selection nor when '*' is the selection.

Obviously I don't know how to set up the wildcard in the where part of the select statement.

The english eqivalent of the query I want is "return all records from TableA where b is equal to anything.

It would be tedious to just truncate the whole where part for a wildcard I think, because the real problem is more complex with several listboxes for some anded wheres. The problem presented here was watered down substantially for clarity and isolation of the specific issue on hand.

How can I accomplish this?

[rasana]

Select a from TableA where b = " & Listbox1.Text

store selected text of listbox in variable
i.e.

seltext=Listbox1.text

now write query like

"select * from TableA where b='" & seltext & "'"

[Muddy]

Select a from TableA where b = " & Listbox1.Text

store selected text of listbox in variable
i.e.

seltext=Listbox1.text

now write query like

"select * from TableA where b='" & seltext & "'"

Thanks rasana, but that doesnt seem to work with a wildcard ( * ) ... though Im not even sure a wildcard is possible in the where part of a select ... is it?

[rasana]

yes we can use wilcard with select statement....will you please tell me what error it's giving?

[Muddy]

Sorry Im being so unclear rasana ... here is the statement I want:

Select a from TableA where b = *

this doesnt throw an error, but returns no records as there is no field b with a * in it.

However I want * to be a wildcard and not a search character.

I want the above statement to return field a from all records of TableA.
I dont want to write it like "Select a from TableA" for reasons already stated in previous posts.

Thanks for the replies (and patience) !!

[Hack]

What kind of database is this?

* is something you would use with Access, but % is what you would use with SQL Server.

[Muddy]

What kind of database is this?

* is something you would use with Access, but % is what you would use with SQL Server.

its an Access database ... Ive tried both % and * but neither work in the context in which I am trying to use them.

[Muddy]

Here is the actual code (simplified a bit for clarity) ... you can see why I am trying to avoid truncating the where for "don't care" fields?

strSelect = "select distinct   recMain.Reg,  from  recMain inner join tfilter on tfilter.Reg = recMain.Reg " & _
                " where tfilter.f1 = '" & List1.Text & _
                "' and tfilter.f2 = '" & List2.Text & _
                "' and tfilter.f3 = '" & List3.Text & _
                "' and tfilter.f4 = '" & List4.Text & "'"

[Hack]

To set up a wildcard, use like

SELECT * FROM tableA WHERE b LIKE '*" & List1.Text & "' "

[Muddy]

Thats works (using % for the wildcard)!!! Thanks!

[Hack]

Really?

That is good to know. I wasn't aware that Access supported the % sign.

[si_the_geek]

It depends on the options, and the connection you are using.

* is SQL-89 (what Access usually uses), and % is SQL-92 (most DBMS's, including Access if the option is set).

I could be wrong here, but I think if you are connecting via ADO it always uses SQL-92 (as you are using Jet, rather than Access).

[Muddy]

I was connecting to an mdb using ADO 2.8 ...