[AccessD] A97 - Apply filter to listbox via code

Why don't you just refer to the form's input txtbox and forget about 'Like'?
(There's no Like in your other sql)
You do need to use the single qoutes and then it should work.

where X = '" & form.txtbox & "'"

Or could it be case sensitive since the T is capitalized?


BN,
Eric

-----Original Message-----
From: accessd-admin at databaseadvisors.com
[mailto:accessd-admin at databaseadvisors.com]On Behalf Of Reische, Brenda
L.
Sent: vrijdag 14 maart 2003 22:16
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