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.

Thx.

Gustav Brock

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

kay soo

ASKER

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

thx

Gustav Brock

Remove the trailing semicolon in the query's SQL.

kay soo

ASKER

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.

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kay soo

ASKER

Thx for pointing out the typo, works fine now.

Gustav Brock

Great! Thanks for the feedback.