query filter

[Guest]

Hi,
I've got a query without WHERE part. Is there any way I can add WHERE part
from code. I see there is filter property for form but what if I deal with
list box and it's corresponding query (record source). Can I use filter for
list box and how?
thanx
alek_mil

 

[Wayne Morgan]

You can modify a query by modifing the SQL property of the query.

Example:
Dim strSQL As String
strSQL = "SELECT .... FROM .... WHERE .....;"
CurrentDb.QueryDefs("qryMyQuery").SQL = strSQL

The SQL property is read/write, so you can get the current SQL of the query
this way also. Any changes you make will stay after the database has been
closed. It doesn't automatically revert back to its previously saved value.
You can also use a SQL statement directly in the Row Source of the listbox,
although there is a length limit. If the SQL statement is a long one, you'll
need to use a query. Another option would be to have more than one query and
change the Row Source of the listbox to the name of the desired query.