Trying to fillter by partial text values

[bweldon]

I have a query where I need to exclude all items which has a value of CSS in a text field. I am using <>(InStr(1,[tblStarsMaster]![strSalesOffice],"CSS",1)) as my criteria, but it is not working. What in the world am I missing? Or is there a better solution.

Thanks

 

[Isskint]

In what way does the formula not work?

Try just useing InStr([tblStarsMaster]![strSalesOffice],"CSS") <> 0
The Start and Compare arguments are optional and probably not really required in this scenario.

 

[Brianwarnock]

I'm a bit rusty and being on my iPad can't check what I'm about to say but I think that you need to put the instr formula in the field row and 0 in the criteria row to select those records where CSS is not found.

Brian

Ps see isskinit agrees

 

[bweldon]

In what way does the formula not work?

Try just useing InStr([tblStarsMaster]![strSalesOffice],"CSS") <> 0
The Start and Compare arguments are optional and probably not really required in this scenario.

Well the records that have CSS in them for example NAM CSS-Canada Eastern are still showing up in the query results.

 

[Isskint]

I'm a bit rusty and being on my iPad can't check what I'm about to say but I think that you need to put the instr formula in the field row and 0 in the criteria row to select those records where CSS is not found.

Brian

Ps see isskinit agrees

On the same wave length

Yes Brian is more accurate on this. You want a calculated field in the query using InStr([tblStarsMaster]![strSalesOffice],"CSS") and then in the criteria row below put <>0

 

[Brianwarnock]

Ops I forgot the <> but isskint is not so careless

Brian

 

[bweldon]

Ok InStr([tblStarsMaster]![strSalesOffice],"CSS") <> 0 left me with only items with CSS in the results, I changed it to InStr([tblStarsMaster]![strSalesOffice],"CSS") = 0
and elimintated them, Now can I use that expression multiple times on a single data field or is there a better way?

Thanks

 

[Isskint]

Where NOT (SalesOffice Like "*CSS*")

:banghead::banghead::banghead::banghead: dont you just hate Old Bridge Players? Neat, short and works, lol. We are ALL still learning from the pros.

 

[Brianwarnock]

LOL

It's the age old problem of correcting the solution instead of solving the problem, I've seen it and done it many times.

As to bridge players never dying and celebrating 52 I'm hoping that 71 isn't too old to restart after years of not playing owing to looking after my wife.

Brian

 

[ahaigh]

WHERE NOT (SalesOffice ALike "%CSS%") would be my preference - unlike most peeople here, I never, ever use Access as a front end, as its forms engine sucks. A web front end written in ASP requires ALike, not Like

 

[ahaigh]

Flexibility, ease of porting to SQL Server, and trying to share knowledge of one of Access' undocumented / poory documented features.

Next time I won't bother.