Not quite what I want

  • Thread starter Thread starter MikeR
  • Start date Start date
M

MikeR

I'm trying to return columns from a table where there is no match in another table on the
same column value, and the value in the first table does not have a space in it (multiple
words).

SELECT [Managers].[MANAGER]
FROM Managers LEFT JOIN Addr ON [Managers].[MANAGER] =( [Addr].[Mgr])
WHERE ([Managers].[Manager] not in (" ") and [Addr].[Mgr] Is Null)
ORDER BY [Managers].[Manager];

The phrase "[Managers].[Manager] not in (" ")" just seems to be ignored ignored, altho it
returns the correct data if just used as a select, ie
SELECT Manager from MANAGERS where Manager not in (" ");

TIA,
MikeR
 
I'm trying to return columns from a table where there is no match in another table on the
same column value, and the value in the first table does not have a space in it (multiple
words).

SELECT [Managers].[MANAGER]
FROM Managers LEFT JOIN Addr ON [Managers].[MANAGER] =( [Addr].[Mgr])
WHERE ([Managers].[Manager] not in (" ") and [Addr].[Mgr] Is Null)
ORDER BY [Managers].[Manager];

The phrase "[Managers].[Manager] not in (" ")" just seems to be ignored ignored, altho it
returns the correct data if just used as a select, ie
SELECT Manager from MANAGERS where Manager not in (" ");
Click to expand...

The NOT IN() clause doesn't do what you think it does: as written it
will exclude from consideration only those records where the [Manager]
field contains literally nothing but one single blank character.

Use wildcards instead:

WHERE [Managers].[Manager] NOT LIKE "* *"
AND [Addr].[Mgr] IS NULL

If the [Manager] field contains actual people's names, you're running
a risk: do you really want to exclude records for Mr. De La Cruz or
Ms. Van Syckle?


John W. Vinson[MVP]
 
Thanks John -
Works like a champ! The manager field is an id number, and the owner of the db uses the
field to store comments on some of the managers. I don't have the option to correct that
behaviour.
MikeR
 
Thanks John -
Works like a champ! The manager field is an id number, and the owner of the db uses the
field to store comments on some of the managers. I don't have the option to correct that
behaviour.
Click to expand...

Eeeuwww...

So he's using a 255 byte text field, as a primary key!? That's
STOOPID. As I'm sure you know, fields should be atomic; if he wants to
store a comment, *he should add a Comments field*. As it is he's
breaking any links to other tables, as well as making this kind of
search much less efficient.

John W. Vinson[MVP]
 
Back
Top