Exclude query not working with BE SQL table

[Guest]

Hi,

I have a split DB with an Access FE and a MS SQL Server BE. All queries
work fine except when I try to exclude records based on them containing a
certain string. Assuming "ABC" is the string, I have tried: NOT LIKE "ABC",
<> "ABC" and also NOT IN ("ABC") with the same result, I get a blank query
result. I am creating the queries from the Access FE.

I'll appreciate your suggestions,

Trauton

 

[John Spencer]

Are you looking for the exact string ABC or are you looking for ABC any
place in the field. Try one of the following depending on how you are
connecting to the SQL database.

NOT LIKE "*ABC*"

Or

NOT LIKE "%ABC%"

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Sylvain Lafontaine]

Try replacing your double quotes around string with single quotes:

<> 'ABC'

or if the field is Unicode (nchar, nvarchar and ntext):

<> N'ABC'

Does your query work in Query Analyser?

 

[Guest]

John,

Thank you for your suggestion. I was looking for the exact "ABC" match but
I tried both of your suggestions from the Access FE and got the same blank
query result.

Trauton

 

[Aaron Kempf]

if you're using SQL Server then you should be using Access Data Projects

 

[Aaron Kempf]

look fruitcake, there is only one way to connect to SQL Server

FILE, NEW, PROJECT (EXISTING DATA)

 

[Guest]

Sylvain,

Tried both suggestions, <> 'ABC' resulted in a blank query result, <> N'ABC'
selected all the records that contained the "ABC" string. The field is
nvarchar.

I tried it with Query Analyzer and couldn't get it to work either. It seems
that the query works in a way I don't understand. Since ABC is the only
string in that field, if I set the condition to Is Null, I get all the right
records. If I add another string "XYZ" to one record and then set the query
to <> "ABC" I get only one record, the one with the string "XYZ". As if the
Null records are ignored by the query statement.

Thank you,

Trauton

 

[John W. Vinson]

As if the
Null records are ignored by the query statement.

They are!

NULL is not equal to anything. It is also not UNEQUAL to anything. A NULL
value will not be returned by *any* query criterion except IS NULL.


John W. Vinson [MVP]

 

[Guest]

Thank you John,

That explains why was I getting no records when I filtered out "ABC", I had
incorrectly assumed that it would show me the records with Null in that field.

Thank you all for your help!

Trauton

 

[John W. Vinson]

Thank you John,

That explains why was I getting no records when I filtered out "ABC", I had
incorrectly assumed that it would show me the records with Null in that field.

You can search for records with NULL using a not-equal criterion with the help
of NZ:

SELECT...
....
WHERE NZ([fieldname], "") <> "ABC"


John W. Vinson [MVP]

 

[Sylvain Lafontaine]

I'm totally confused by your result. You should show us the full Select
query that you are using to make sure that the logic within it is correct.

 

[John W. Vinson]

I'm totally confused by your result. You should show us the full Select
query that you are using to make sure that the logic within it is correct.

I think the confusion is that there are NULL values in the field. A criterion
of <> "ABC" will not return the record if the field is NULL.

John W. Vinson [MVP]

 

[Guest]

The Select query used for: <> 'ABC' resulted in a blank query result, was:

SELECT tblContracts.Locations
FROM tblContracts
WHERE (((tblContracts.Locations)<>'Tampa'));

The Select query used for: <> N'ABC' selected all the records that contained
the "Tampa" string was the one below, I typed it as <> N'ABC' in the query
window but after I tabbed away from the field Access had added the extra "":

SELECT tblContracts.Locations
FROM tblContracts
WHERE (((tblContracts.Locations)<>"N'Tampa'"));

 

[Sylvain Lafontaine]

From what I see, you must keep using " instead of ' if you are using the
Query Designer of Access because something like "N'Tampa'" is obviously
wrong and explains the strange result.

 

[John W. Vinson]

The Select query used for: <> N'ABC' selected all the records that contained
the "Tampa" string was the one below, I typed it as <> N'ABC' in the query
window but after I tabbed away from the field Access had added the extra "":

I've never seen the

N'<string>

syntax, and it's certainly not accepted by Access. Is it the syntax for "not
equal" in some other DBMS?

John W. Vinson [MVP]

 

[Sylvain Lafontaine]

It's simply the syntaxe for a Unicode string on SQL-Server. It was not
clear what kind of query the OP was using against the BE (maybe a
passthrough query?), so I made a mention about it.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

 

[John W. Vinson]

It's simply the syntaxe for a Unicode string on SQL-Server. It was not
clear what kind of query the OP was using against the BE (maybe a
passthrough query?), so I made a mention about it.

Thanks, Sylvain - I was not aware of that syntax. I'm sure it would work in a
pass-through query but I don't believe it does in native Access.

John W. Vinson [MVP]

 

[Guest]

Thank you Sylvain, this was very helpful!

Trauton