Link to home
Start Free TrialLog in
Avatar of kay soo
kay soo

asked on

Access VBA to Change a Saved Query Filter Criteria

Hi Experts,

I am looking for a VBA codes of how to change a saved query criteria by button click from a form, button click is to open a report eventually.

Report's data source = Query1 with Field1, Field2.....etc is joined by Table1 and Table2.

Field2 in Query1 is a CheckBox from Table1.

I like to hv VBA codes on button click to replace Field2 query criteria from NULL(blank criteria) to -1 OR -1 to NULL(blank criteria) depends on user's discretion.

Can anyone shed some lights plz.

Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Save the query without the criteria.

Then run code:

Dim qd As DAO.QueryDef
Dim OldSql As String

Set qd = CurrentDb.QueryDefs("YourQueryName")
OldSql = qd.SQL 
' To filter for Null:
qd.SQL = OldSql & " Where Field2 Is Null"
' or, to filter for True:
qd.SQL = OldSql & " Where Field2 = True"

' Insert current code to open report

' Reset SQL.
qd.SQL = OldSql

Open in new window

Avatar of kay soo
kay soo


Thx for the reply, I ran the code with saved non criteria Query1, however there's this error message from access..

Run-Time error '3142'
Characters found after end of SQL Statement

thxUser generated imageUser generated image
Remove the trailing semicolon in the query's SQL.
Avatar of kay soo


Seems it remained with the same compile error..

Here's my actual Query's SQL

SELECT [Applicant Details Q].ClientID, [Applicant Details Q].ClientName, [Applicant Details Q].ICNo, [Applicant Details Q].LoanCat, [Applicant Details Q].LoanDisburse, [Applicant Micro Credit Details].StartBusiness, [Applicant Micro Credit Details].BusinessExpansion, [Applicant Micro Credit Details].WorkingCapital
FROM [Applicant Details Q] INNER JOIN [Applicant Micro Credit Details] ON [Applicant Details Q].ICNo = [Applicant Micro Credit Details].ICNo

the [Applicant Details Q].LoanDisburse is the one I tried to set filter criteria with either True/-1(Show Loan Disburse) or blank(Show All - with or without Disburse) in Report.
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of kay soo


Thx for pointing out the typo, works fine now.
Great! Thanks for the feedback.