1

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.

Community
  • 1
  • 1
gdekoker
  • 185
  • 8

1 Answers1

2

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.

HansUp
  • 95,961
  • 11
  • 77
  • 135
  • Thank you very much, I just realised now that I added the WHERE condition in the wrong sequence. However, I have changed it to your suggestion which makes perfect sense to me but now I am getting a missing operator error. So I am completely stumped. Any ideas? – gdekoker May 21 '20 at 14:22
  • 1
    You don't want a WHERE in there the parameter is already for WHERE. You also may want acViewPreview to open it. – Warcupine May 21 '20 at 14:27
  • When I run the DoCmd.OpenReport code I get the error. When I run the following the report opens just fine: DoCmd.OpenReport "BudgetTable", acViewReport – gdekoker May 21 '20 at 14:28
  • Thank you Warcupine, that solved my mystery. The code should look as follows: DoCmd.OpenReport "BudgetTable", acViewReport, , "IsActive = Yes" – gdekoker May 21 '20 at 14:30
  • I just fixed the answer to remove `WHERE` from the *WhereCondition* --- Thanks, @Warcupine. Is it OK, now? – HansUp May 21 '20 at 14:30
  • @HansUp, looks perfect to me – gdekoker May 21 '20 at 14:40