Question about server filter

  • Thread starter Thread starter Jonathan Blitz
  • Start date Start date
J

Jonathan Blitz

I am use Access with SQL Server.
From what I understand when I enter a value in Server Filter it is added to
the where clause and sent to SQL Server as part of the command.

However, I have found that if I format a field (for example, a phone number)
then the Server Filter must use the formatted value rather then the base
value.
For example if the select is:

Select stuff(stuff(ce.ServiceNumber,7,0,'-'),4,0,'-') as ServiceNumber
from table

If I wish to get the row where the servicenumber field is '1234567890' then
I have to enter, in the Server Filter:

ServerNumber = '123-456-7890'

However, if I use the same command in SQL Query Analyzer then I enter:
Where ServiceNumber = '1234567890'

So....
What is happening?

--
Jonathan Blitz
AnyKey Limited
Israel

"When things seem bad
Don't worry and shout
Just count up the times
Things have worked themselves out."



--
Jonathan Blitz
AnyKey Limited
Israel

"When things seem bad
Don't worry and shout
Just count up the times
Things have worked themselves out."
 
Dear Jonathan:

I'm thinking that if you run this query:

Select stuff(stuff(ce.ServiceNumber,7,0,'-'),4,0,'-') as ServiceNumber
from table
WHERE stuff(stuff(ce.ServiceNumber,7,0,'-'),4,0,'-') = '1234567890'

the result would be empty.

Since Access is not seeing the table, but the query of the table with
ServiceNumber already formatted with the added hyphens, then the only
thing it can filter is the formatted ServiceNumber.

To avoid confusion, and perhaps see this most clearly, do not alias
the calculated column with the same name as the table column. If the
two are named the same, it looks like the system is resolving the
ambiguity in favor of the calculated column. Of course, you are much
smarter than the computer, which is actually designed to repetitively
multiply our mistakes at a very high rate of speed.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
What I was worrying about was the possibility that Access was sending
somethin like this to the server:

Select * from(
select stuff(stuff(ce.ServiceNumber,7,0,'-'),4,0,'-') as ServiceNumber
from table) as t1
WHERE SERVICENUMBER = ..........

From what I understand not I assume it it sending:

select stuff(stuff(ce.ServiceNumber,7,0,'-'),4,0,'-') as ServiceNumber
from table
Where stuff(stuff(ce.ServiceNumber,7,0,'-'),4,0,'-') = ......

I think I will change it's name as u suggest. Expicially since ServiceNumber
is my index.

--
Jonathan Blitz
AnyKey Limited
Israel

"When things seem bad
Don't worry and shout
Just count up the times
Things have worked themselves out."
 
Back
Top