Trying to query data from a list box variable.

[cpyouinc]

Hello there. Is there anyone who can help me?

Ok, here is the scenario:

I have a database table which has a “Stores” field in it among other fields. In this field are a list of stores (codes to identify stores, more specifically) separated by a hyphen. For example: “-ALB-WAL-CVS” (i.e. Albertsons, Walgreens, and CVS)

On a Access form, I have a list box displaying a list of the store codes. Now, what I want to happen when a user selects a store code from the list box and clicks the query command button, is for a list of records to be displayed which contains the selected store code in the “Stores” field of the database.

The name of the list box where the store codes are listed is “lstSelectStore”. Since the selected store code is stored in this variable, I have been trying to use this variable in a query with a LIKE statement so I can see any records with the selected store code in the “Stores” field. The problem is, since the store codes in the “Stores” field act as a single string, it cannot identify individual store codes within the string.

My question is: Can I use the lstSelectStore (store list box) variable successfully in a LIKE statement in Access with a * wildcard which finds a particular store code in the “Stores” field and displays the record? If so, how…or do I need to do something else in VBA to accomplish this?

Any help is appreciated.

Thank you.

 

[braindead2]

should not be a problem just the syntax is essential need to include quotes but have the avariable not in quotes like this below

"select * from yourtable where stores like '*" & me.lstSelectStore.selected & "*'"

 

[cpyouinc]

I tried this query:

"SELECT *
FROM [Sample Contact Table]
WHERE [Sample Contact Table]![Stores] Like '*" & [Forms]![Print, Report, and Query Form]![lstSelectStore] & "*'"

But I get an Access error "Invalid SQL Statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT' or UPDATE'.

Is my syntax off?

 

[tlbroadbent]

Where are you using the SQL statement? Perhaps you could post the code. Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[cpyouinc]

I am using it as an Access Query which is triggered by a command button on a user form via a macro.

 

[tlbroadbent]

Can you run the query from the query window? Terry
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[braindead2]

my guess you are using the runSQL method which is only for action queries you need to use the openrecordset method.
OR I'm wrong.
Good luck

 

[cpyouinc]

When I try and run it from the query window, I get the same Access error message.

 

[cpyouinc]

Ok, I got it. It was just a simple matter of syntax. Here is the correct query:

SELECT *
FROM [Sample Contact Table]
WHERE ((([Sample Contact Table]![Stores])
LIKE '*' & [Forms]![Print, Report, and Query Form]![lstSelectStore] & '*'));

It was the single quotes around the wildcard character that isolates them from the variable.

It works great now!
Thanks for your help.