Setting Criteria on a Query using VB, can it be done? Say i have "Query 1" and in "Query 1" i have my Criteria for "X" is blank. i want to use a button on "Form 1" to set Criteria for "X"
could you give a little bit more information and we might be able to help. I'll give it a go anyway. If I understand you correctly, then you will need to alter the SQL string for the query. If you your Query is called Query1, you can find out what the current SQL is like this:
Code:
debug.print CurrentDB.querydefs("Query1").SQL
which will bring up something like this (a very very simple example):
"SELECT * from tbltest;". To add a criteria you need to add a WHERE clause at the end. This is relatively straghtforward. Firstly, you need to remove the trailing ';', which despite appearances is not always the lst character of the SQL string, then you add you WHERE clause,.e.g.:
Code:
Dim sSQL As String
' say CurrentDb.QueryDefs("query1").Sql = "SELECT * FROM tblTest;"
sSQL = CurrentDb.QueryDefs("query1").Sql
sSQL = Left(sSQL, InStrRev(sSQL, ";") - 1)
CurrentDb.QueryDefs("query1").Sql = sSQL & " WHERE x = 'whatever';"
(the space before the WHERE in the string is very important.)
This query when run will now show all records from tblTest when x is equal to 'whatever'
