Opening a Report With a Filter

Question

I have table in Access with budget information I use to generate a report.

One of the fields in my table is called "IsActive". It is defined as a Yes/No field in my table properties.

I would like to open the report and only show the lines where the IsActive field = Yes.

I tried the following:

DoCmd.OpenReport "BudgetTable", acViewReport, "WHERE IsActive = Yes"

and

DoCmd.OpenReport "BudgetTable", acViewReport, "WHERE IsActive = 1"

In both cases the report is showing all the fields, including where the IsActive is set to No.

Answer 1

Your code supplies the WHERE string as the FilterName argument of OpenReport ...

DoCmd.OpenReport "BudgetTable", acViewReport, "WHERE IsActive = 1"
                       ^             ^                 ^
                       |             |                 |
                  ReportName       View           FilterName

Supply it as the WhereCondition instead ...

DoCmd.OpenReport "BudgetTable", acViewReport, , "IsActive = True"
                       ^            ^        ^               ^
                       |            |        |               |
                 ReportName       View   FilterName   WhereCondition

Note the documentation describes the WhereCondition as ...

"A string expression that's a valid SQL WHERE clause without the word WHERE."

So make sure to remove WHERE from your WhereCondition string.