Access 97 query not working.

  • Thread starter Thread starter Jeff Rush
  • Start date Start date
J

Jeff Rush

Hi all,

I have the following query that is designed to pull all listed fields from
tblSurvey but to exclude those records that contain one of the restricted
names.

SELECT tblSurvey.Name, tblSurvey.Source, tblSurvey.REFER_TO,
tblSurvey.ACTIVITY_NUM, tblSurvey.shuffle
FROM tblSurvey
WHERE ((Not (tblSurvey.Name)="Ryan" Or (tblSurvey.Name)="Brian" Or
(tblSurvey.Name)="Russel" Or (tblSurvey.Name)="Fredrcik" Or
(tblSurvey.Name)="Todd"));

When I run the query I still get records with Todd, Brian etc in the Name
Field.

Any suggestions or assistance would be appreciated.

Thanks in advance!!!

Jeff
 
Ah, but I'll bet you aren't still getting Ryan. I would
change your syntax a bit....

WHERE tblSurvey.Name <> "Ryan" Or tblSurvey.Name <> "Brian"
Or etc...

Gary Miller
Sisters, OR
 
(Just to give a hard time, Gary <g>)

.... and I bet that Jeff will now get all "Ryan", "Brian", "Russell",
"Fredrick" and "Todd".

Remember the multiplicative rule in applying NOT to a compound expression,
you need to convert OR to AND and AND to OR.

Try:
....
WHERE tblSurvey.[Name] <> "Ryan"
AND tblSurvey.[Name] <> "Brian"
AND tblSurvey.[Name] <> "Russell"
AND tblSurvey.[Name] <> "Fredrick"
AND tblSurvey.[Name] <> "Todd"

I think it is clearer and shorter to use:

..... WHERE tblSurvey.[Name]
Not In ("Ryan", "Brian", "Russell", "Fredrick", "Todd")

I assumed the spelling of "Fredrick" was incorrect in the original post.
 
Van,

If I ever had a day without someone giving me a hard time,
it would probably be the first. Good to see a little humour
here.

Gary

"Van T. Dinh" <[email protected]>
wrote in message
(Just to give a hard time, Gary <g>)

... and I bet that Jeff will now get all "Ryan", "Brian", "Russell",
"Fredrick" and "Todd".

Remember the multiplicative rule in applying NOT to a compound expression,
you need to convert OR to AND and AND to OR.

Try:
...
WHERE tblSurvey.[Name] <> "Ryan"
AND tblSurvey.[Name] <> "Brian"
AND tblSurvey.[Name] <> "Russell"
AND tblSurvey.[Name] <> "Fredrick"
AND tblSurvey.[Name] <> "Todd"

I think it is clearer and shorter to use:

.... WHERE tblSurvey.[Name]
Not In ("Ryan", "Brian", "Russell", "Fredrick", "Todd")

I assumed the spelling of "Fredrick" was incorrect in the original post.

--
HTH
Van T. Dinh
MVP (Access)



Ah, but I'll bet you aren't still getting Ryan. I would
change your syntax a bit....

WHERE tblSurvey.Name <> "Ryan" Or tblSurvey.Name <> "Brian"
Or etc...

Gary Miller
Sisters, OR
Click to expand...
Click to expand...
 
You can razz me all you want so long as I keep getting such good
information!!

Thank you all for your help on this. The last one looks like just what I
wanted!!

Best Regards,

Jeff
 
Hi Van,

Actually our tracking database has him entered as Frederick and the powers
that be are not going to change anything in the tracking system (it tends to
blow up).
I'll have to get a look at his drivers license to see if he actually spells
it that way.

Also:
I tried to modify your wonderful code to make things a little more
manageable and modified it to:

SELECT tblSurvey.Name, tblSurvey.Source, tblSurvey.REFER_TO,
tblSurvey.ACTIVITY_NUM, tblSurvey.shuffle
FROM tblSurvey
WHERE tblSurvey.[Name] Not In (HelpDeskPPL.[HlpDskName]);

So I could simply administer the list of PPL from the HelpDeskPPL table.

However when I do this I get an Enter Parameter window. Do I need an *
somewhere in the NOT IN statement??

Thank You!

Jeff

Van T. Dinh said:
(Just to give a hard time, Gary <g>)

... and I bet that Jeff will now get all "Ryan", "Brian", "Russell",
"Fredrick" and "Todd".

Remember the multiplicative rule in applying NOT to a compound expression,
you need to convert OR to AND and AND to OR.

Try:
...
WHERE tblSurvey.[Name] <> "Ryan"
AND tblSurvey.[Name] <> "Brian"
AND tblSurvey.[Name] <> "Russell"
AND tblSurvey.[Name] <> "Fredrick"
AND tblSurvey.[Name] <> "Todd"

I think it is clearer and shorter to use:

.... WHERE tblSurvey.[Name]
Not In ("Ryan", "Brian", "Russell", "Fredrick", "Todd")

I assumed the spelling of "Fredrick" was incorrect in the original post.

--
HTH
Van T. Dinh
MVP (Access)



Gary Miller said:
Ah, but I'll bet you aren't still getting Ryan. I would
change your syntax a bit....

WHERE tblSurvey.Name <> "Ryan" Or tblSurvey.Name <> "Brian"
Or etc...

Gary Miller
Sisters, OR
Click to expand...
Click to expand...
 
Glad to help.

Gary

Jeff Rush said:
You can razz me all you want so long as I keep getting such good
information!!

Thank you all for your help on this. The last one looks like just what I
wanted!!

Best Regards,

Jeff
Click to expand...
 
See comments in-line.

HTH
Van T. Dinh
MVP (Access)

-----Original Message-----
Hi Van,

Actually our tracking database has him entered as Frederick and the powers
that be are not going to change anything in the tracking system (it tends to
blow up).
I'll have to get a look at his drivers license to see if he actually spells
it that way.
Click to expand...
I only mentioned it because you had "Fredrcik" in the
original post.


Also:
I tried to modify your wonderful code to make things a little more
manageable and modified it to:

SELECT tblSurvey.Name, tblSurvey.Source, tblSurvey.REFER_TO,
tblSurvey.ACTIVITY_NUM, tblSurvey.shuffle
FROM tblSurvey
WHERE tblSurvey.[Name] Not In (HelpDeskPPL.[HlpDskName]);

So I could simply administer the list of PPL from the HelpDeskPPL table.

However when I do this I get an Enter Parameter window. Do I need an *
somewhere in the NOT IN statement??
Click to expand...
If you have a Table for this, it is much more efficient to
use the Left Outer Join. Try:

SELECT S.[Name], S.[Source], S.[REFER_TO],
S.[ACTIVITY_NUM], S.[shuffle]
FROM tblSurvey As S
LEFT JOIN HelpDeskPPL As HD
ON S.[Name] = HD.[HlpDskName]
WHERE (HD.[HlpDskName] Is Null);
 
SWEET!!
That is SO much Easier to read and use!!

Thank you again for all your help!!

Jeff

Van T. Dinh said:
See comments in-line.

HTH
Van T. Dinh
MVP (Access)

-----Original Message-----
Hi Van,

Actually our tracking database has him entered as Frederick and the powers
that be are not going to change anything in the tracking system (it tends to
blow up).
I'll have to get a look at his drivers license to see if he actually spells
it that way.
Click to expand...
I only mentioned it because you had "Fredrcik" in the
original post.


Also:
I tried to modify your wonderful code to make things a little more
manageable and modified it to:

SELECT tblSurvey.Name, tblSurvey.Source, tblSurvey.REFER_TO,
tblSurvey.ACTIVITY_NUM, tblSurvey.shuffle
FROM tblSurvey
WHERE tblSurvey.[Name] Not In (HelpDeskPPL.[HlpDskName]);

So I could simply administer the list of PPL from the HelpDeskPPL table.

However when I do this I get an Enter Parameter window. Do I need an *
somewhere in the NOT IN statement??
Click to expand...
If you have a Table for this, it is much more efficient to
use the Left Outer Join. Try:

SELECT S.[Name], S.[Source], S.[REFER_TO],
S.[ACTIVITY_NUM], S.[shuffle]
FROM tblSurvey As S
LEFT JOIN HelpDeskPPL As HD
ON S.[Name] = HD.[HlpDskName]
WHERE (HD.[HlpDskName] Is Null);
Click to expand...
 
Back
Top