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.

Thx.
Microsoft AccessVBASQL

Avatar of undefined
Last Comment
Gustav Brock

8/22/2022 - Mon
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

ASKER
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

thxError1.jpgError2.jpg
Gustav Brock

Remove the trailing semicolon in the query's SQL.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
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.
ASKER CERTIFIED SOLUTION
Gustav Brock

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
kay soo

Thx for pointing out the typo, works fine now.
Gustav Brock

Great! Thanks for the feedback.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.