Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Access Report Filter Operator Problem

Status
Not open for further replies.

tculler

MIS
Mar 6, 2001
4
US
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...

 
Try
[Server Name] Like 'F*'

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

Chester County, PA Residents
Please Show Your Support...
 
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.)


 
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?
 
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

Chester County, PA Residents
Please Show Your Support...
 
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 [lol]

Dan
[pipe]
Daniel Vlas
Systems Consultant
danvlas@yahoo.com
 
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...
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top