Open report

[eacollie]

I'm trying to add onto the open report command but having problems.

I'm using the filter on a form to populate labels

DoCmd.OpenReport "Labels_Contacts", acViewPreview, , Me.Filter & "and [Mail] = 'Yes'"

and Access doesn't like this.
I'm getting a 3075 error "Syntax error (missing operator) in query expression 'and [Mail] = Yes'.
What is the correct syntax for this?

Thanks much

 

[pbaldy]

Given the error message, it doesn't look like there's a filter. You may want to test for that and adjust (Len function). Also, if Mail is a yes/no field, you wouldn't want the single quotes around it.

 

[eacollie]

There is a filter because it opens the report will the filtered list. I'm trying to add something to the filter when I open the report and I think that's what's causing the problem. :banghead:

 

[pbaldy]

What you've done looks fundamentally correct, though I'd add a space in front of "and", plus the data type comment above. The error implies there's no existing filter though. Can you post the db here?

 

[eacollie]

I can't get the file down to the 2 mb limit for attaching.

 

[pbaldy]

Did you compact/repair and then zip?

 

[eacollie]

Yep, but it only gets it down to about 5.8

 

[pbaldy]

You can email it to

pbaldy
gmail
com

but I probably won't get to it until tomorrow.

 

[pbaldy]

In response to your email:

I placed the following in the code for the click event of the mailing labels control:

Dim currentfilter
currentfilter = (IIf(Me.Filter = "", " [Mail] = Yes", " ([Mail] = Yes) and " & "( " & Me.Filter & ")"))
DoCmd.OpenReport "Labels_Contacts", acViewPreview, , currentfilter

The only other issue I have is clearing of the filters.
The template is written with macros, so I’m not sure of the syntax.
The only way I can clear the filter is by using the ribbon. Any idea what code this “clear all filters” uses and how to put that into the macro behind the “show all records” control?

The VBA method to clear the filters would be:

Me.FilterOn = False

or for a different form:

Forms!FormName.FilterOn = False

The macro uses SetValue to do the same thing.