[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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030314/509032a1/attachment.html>