[AccessD] A97 - Apply filter to listbox via code

Brenda,
 
You need a single quote before your leading * and after your trailing one.
 
HTH
 
Don McGillivray

-----Original Message-----
From: Reische, Brenda L. [mailto:reische at mdh.org]
Sent: Friday, March 14, 2003 12:08 PM
To: 'accessd at databaseadvisors.com'
Subject: [AccessD] A97 - Apply filter to listbox via code



In my Access 97 database I have the following:

 

Text box control (txtTitle) 

Search "button"  (lblSearch)

List box control (lstNew)

 

 

The row source for lstNew is dynamically modified based on various controls
that the user clicks on.  For example, I limit the listing to only those
items that start with "A" when the user clicks the A button.


I also need to let the user enter a word and click Search, then apply a
"filter" or WHERE to the listbox.rowsource

 

I guess I'm just having problems with the syntax, because I always get empty
results on the form, but I can type in the expected string into a query and
get actual results.

 

I've tried every syntactical string I can think of, but cannot get the
results I need.  

 

 

Here is the SQL statement I have for the listbox version:

 

lstNew.RowSource = "SELECT [qrySearchTitle].[Hyperlink],
[qrySearchTitle].[Subject]," & _

            "[qrySearchTitle].[Number], [qrySearchTitle].[Type],
[qrySearchTitle].[DeptName]," & _

"[qrySearchTitle].[DateEff] " & _

"FROM qrySearchTitle " & _

"WHERE [qrySearchTitle].[Subject] Like *" &
[Forms]![frmSearchTitle]![txtTitle] & _

"* ORDER BY [qrySearchTitle].[Subject];"

 

The following SQL statement in a query window DOES work:

 

SELECT qrySearchTitle.Hyperlink, qrySearchTitle.Subject,
qrySearchTitle.Number, qrySearchTitle.Type, qrySearchTitle.DeptName,
qrySearchTitle.DateEff

FROM qrySearchTitle

WHERE (((qrySearchTitle.Subject) Like "*computer*"))

ORDER BY qrySearchTitle.Subject;

 

I have tried modifying the syntax of the first statement fourteen ways to
Sunday, but I cannot get the results to open !!!

 

Does anyone have any suggestions for making this work????????

 

TIA

Brenda Reische

Application Support Analyst

McDonough District Hospital