[AccessD] A97 - Apply filter to listbox via code

In this snip, I notice that a closing parenthesis is missing in the Where
clause.  Try removing the opening one or adding a closing one.

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


Private Sub lblSearch_Click()
    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];"
    lstNew.Requery
End Sub

This has really got to be something I'm doing, because I do this elsewhere
in the same form and it	 works:
	lstNew.RowSource = "SELECT [qrySearchTitle].[Hyperlink],
[qrySearchTitle].[Subject], [qrySearchTitle].[Number], " & _
    	"[qrySearchTitle].[Type], [qrySearchTitle].[DeptName],
[qrySearchTitle].[DateEff] " & _
    	"FROM qrySearchTitle WHERE Left([qrySearchTitle].[Subject],1)=""T""
ORDER BY [qrySearchTitle].[Subject];"

It's just when I introduce the form input box as the WHERE expression that
it blows chunks.

I guess I'm trying to work too hard on Friday afternoon and it's fighting
me!


-----Original Message-----
From: Mcgillivray, Donald [LTD]
[mailto:donald.a.Mcgillivray at mail.sprint.com] 
Sent: Friday, March 14, 2003 3:07 PM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] A97 - Apply filter to listbox via code

hmmm. Worked for me in a quick demo I threw together.  Assume you're
requerying the listbox after changing the RowSource.

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


Nope, that didn't do it....



-----Original Message-----
From: Mcgillivray, Donald [LTD]
[mailto:donald.a.Mcgillivray at mail.sprint.com] 
Sent: Friday, March 14, 2003 2:28 PM
To: accessd at databaseadvisors.com
Subject: RE: [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

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com