Access Report Filter Operator Problem

[tculler]

I'm trying to use a simple filter in a report. I have a table column called 'Server Name'. When I try to filter for all servers whose name starts with 'F' by using:

Left([Server Name],1) = "F"

And try to view the report, Clippy responds:

One or more operators in the filter expression is invalid.
For a valid list of operators refer to the help file.

I'm sure I'm using Left() correctly - I mean, how can you screw that up? Any ideas what it is Clippy doesn't like?

TIA...

 

[smedvid]

Try
[Server Name] Like 'F*'

? htwh Steve Medvid
"IT Consultant & Web Master"
e-Mail: Stephen_Medvid@GMACM.com

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[tculler]

Acutally, I had already tried that, but I cut and pasted your text FTHOI to try it again. Both times I ended up with an empty report. Without the filter, about half of my records have a [Server Name] field that starts with F (FCWS887, FCXAS767, FMCESQ08) but the filter seems to weed them out, too, along with the other servers that don't start with F (ESQ06, ECCAS543, ECCDB451, WINT623, etc.)

 

[tculler]

One more piece of info that will probably help - when I do the same LIKE filter ([server name] LIKE 'F*') on the server name column for servers that start with E ([server name] LIKE 'E*'), I get a correct report. The first record in the table (whether sorted by [server name] or not)has a [server name] field starts with 'E'. So I'm guessing that Access stops looking after the first record, since it doesn't make it through the filter. How can I tell Access to search the entire table?

 

[smedvid]

Review your table to insure that their are not any spaces preceeding the "F" server names.. Other than that I have no other ideas.. Steve Medvid
"IT Consultant & Web Master"
e-Mail: Stephen_Medvid@GMACM.com

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[danvlas]

Make sure all rows are filled in with data.
Left(Null, 1) will generate an error...

When do you try set the filter? Before opening (from a command button or something) or after it's open and you try to set the Filter property?

First case:

DoCmd.OpenReport "RepName", acViewPreview, , "[Server Name] Like 'F*'"

Second case:

Me.Filter = "[Server Name] Like 'F*'"
Me.FilterOn = True

It would be helpful to post the report recordsource SQL...

It has to work...unless there is something you're keeping secret

Dan

Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[tculler]

Turns out it was a grouping problem - We were trying to group by another column. when we removed that grouping, the problem went away and we could filter correctly for the servers which started with 'F'. I guess Access won't recursively traverse the table looking for filtered items that will match each group if the grouped column doesn't include the filter.

Unless someone knows a way...