By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,985 Members | 2,907 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,985 IT Pros & Developers. It's quick & easy.

Not-operator not working?

P: n/a
Out of a table I'm trying to pull out every record in which the field
'Lastname' is not 'Johnson'.
In order to do this I've put the following next to the 'criteria' field in
the query-window:
Not "Johnson"
However the resulting table is empty.
Does someone know why it's not working?
Thanks,
john
Jul 12 '06 #1
Share this Question
Share on Google+
11 Replies


P: n/a
Does the query pull up records when there is nothing in the criteria
field?

Sarah Schreffler

john wrote:
Out of a table I'm trying to pull out every record in which the field
'Lastname' is not 'Johnson'.
In order to do this I've put the following next to the 'criteria' field in
the query-window:
Not "Johnson"
However the resulting table is empty.
Does someone know why it's not working?
Thanks,
john
Jul 12 '06 #2

P: n/a
The query doesn't give me any records while there are some records with
empty values for the criteria field 'Lastname'.
When I don't use the not-criteria in the query it pulls up every record in
the table.
john

"Sarah Schreffler" <sa**************@gmail.comschreef in bericht
news:11********************@s13g2000cwa.googlegrou ps.com...
Does the query pull up records when there is nothing in the criteria
field?

Sarah Schreffler

john wrote:
>Out of a table I'm trying to pull out every record in which the field
'Lastname' is not 'Johnson'.
In order to do this I've put the following next to the 'criteria' field
in
the query-window:
Not "Johnson"
However the resulting table is empty.
Does someone know why it's not working?
Thanks,
john

Jul 12 '06 #3

P: n/a
It's hard to say without more details. Can you post the sql that is
generated by this query? (click on SQL view instead of the blue design
triangle).
Possible causes may be that you have some other criterion going on at the
same time and the combination excludes all records, or that you did an inner
join to another table in which there are no matching records, or at least no
matching records that aren't called "johnson".
good luck
"john" <jo**@test.comwrote in message
news:Ls********************@casema.nl...
Out of a table I'm trying to pull out every record in which the field
'Lastname' is not 'Johnson'.
In order to do this I've put the following next to the 'criteria' field in
the query-window:
Not "Johnson"
However the resulting table is empty.
Does someone know why it's not working?
Thanks,
john

Jul 12 '06 #4

P: n/a
On Wed, 12 Jul 2006 17:27:05 +0200, john wrote:
Out of a table I'm trying to pull out every record in which the field
'Lastname' is not 'Johnson'.
In order to do this I've put the following next to the 'criteria' field in
the query-window:
Not "Johnson"
However the resulting table is empty.
Does someone know why it's not working?
Thanks,
john
That should work if that is the only criteria for the query.
Does <"Johnson" work?
Does using the single quote work, i.e. Not 'Johnson' ?
How about Not Like "Johnson*"
Does "Johnson" (without the Not) bring up just the Johnson records?
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Jul 12 '06 #5

P: n/a
For test reasons I made it very basic, a two field table. The query is:

SELECT Test.ID_applicatie, Test.Status
FROM Test
WHERE ((Not (Test.Status)="Vervallen"));

For this message i used 'Johnson' in stead of 'Vervallen'.
john

"John Welch" <john(remove)welch@cal(remove)central.comschreef in bericht
news:e9********@enews4.newsguy.com...
It's hard to say without more details. Can you post the sql that is
generated by this query? (click on SQL view instead of the blue design
triangle).
Possible causes may be that you have some other criterion going on at the
same time and the combination excludes all records, or that you did an
inner join to another table in which there are no matching records, or at
least no matching records that aren't called "johnson".
good luck
"john" <jo**@test.comwrote in message
news:Ls********************@casema.nl...
>Out of a table I'm trying to pull out every record in which the field
'Lastname' is not 'Johnson'.
In order to do this I've put the following next to the 'criteria' field
in the query-window:
Not "Johnson"
However the resulting table is empty.
Does someone know why it's not working?
Thanks,
john


Jul 12 '06 #6

P: n/a
Does <"Johnson" work?
No.
Does using the single quote work, i.e. Not 'Johnson' ?
No.
How about Not Like "Johnson*"
Not working either.
Does "Johnson" (without the Not) bring up just the Johnson records?
Yes, that does work

???
john
"fredg" <fg******@example.invalidschreef in bericht
news:zg****************************@40tude.net...
On Wed, 12 Jul 2006 17:27:05 +0200, john wrote:
>Out of a table I'm trying to pull out every record in which the field
'Lastname' is not 'Johnson'.
In order to do this I've put the following next to the 'criteria' field
in
the query-window:
Not "Johnson"
However the resulting table is empty.
Does someone know why it's not working?
Thanks,
john

That should work if that is the only criteria for the query.
Does <"Johnson" work?
Does using the single quote work, i.e. Not 'Johnson' ?
How about Not Like "Johnson*"
Does "Johnson" (without the Not) bring up just the Johnson records?
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Jul 12 '06 #7

P: n/a
I'm not sure what went wrong but now the NOT-operator works for records that
have a value for 'Lastname'. I've added 'and null' to pull up the records
with empty values.
Is this the correct way to do?
Thanks again,
john

"john" <jo**@test.comschreef in bericht
news:Ls********************@casema.nl...
Out of a table I'm trying to pull out every record in which the field
'Lastname' is not 'Johnson'.
In order to do this I've put the following next to the 'criteria' field in
the query-window:
Not "Johnson"
However the resulting table is empty.
Does someone know why it's not working?
Thanks,
john

Jul 12 '06 #8

P: n/a
* john:
I'm not sure what went wrong but now the NOT-operator works for records that
have a value for 'Lastname'. I've added 'and null' to pull up the records
with empty values.
Is this the correct way to do?
Yes, that's standard SQL.

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.
Jul 12 '06 #9

P: n/a
Thanks all.
john

"Randy Harris" <pl****@send.no.spamschreef in bericht
news:0f*******************@newssvr27.news.prodigy. net...
>* john:
>I'm not sure what went wrong but now the NOT-operator works for records
that have a value for 'Lastname'. I've added 'and null' to pull up the
records with empty values.
Is this the correct way to do?

Yes, that's standard SQL.

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

Jul 12 '06 #10

P: n/a
Hi John,

Your Not is a Not(Test.Status) and returns a Boolean, which is never
equal to "Vervallen"

Better use:
WHERE Not (Test.Status = "Vervallen")
or
WHERE (Test.Status <"Vervallen")

This under the condition that Test.Status has a string-value and is not
a Boolean.

Success, HBInc.


john wrote:
For test reasons I made it very basic, a two field table. The query is:

SELECT Test.ID_applicatie, Test.Status
FROM Test
WHERE ((Not (Test.Status)="Vervallen"));

For this message i used 'Johnson' in stead of 'Vervallen'.
john

"John Welch" <john(remove)welch@cal(remove)central.comschreef in bericht
news:e9********@enews4.newsguy.com...
It's hard to say without more details. Can you post the sql that is
generated by this query? (click on SQL view instead of the blue design
triangle).
Possible causes may be that you have some other criterion going on at the
same time and the combination excludes all records, or that you did an
inner join to another table in which there are no matching records, or at
least no matching records that aren't called "johnson".
good luck
"john" <jo**@test.comwrote in message
news:Ls********************@casema.nl...
Out of a table I'm trying to pull out every record in which the field
'Lastname' is not 'Johnson'.
In order to do this I've put the following next to the 'criteria' field
in the query-window:
Not "Johnson"
However the resulting table is empty.
Does someone know why it's not working?
Thanks,
john
Jul 13 '06 #11

P: n/a
bedankt ;-)

"hbinc" <j.********@hccnet.nlschreef in bericht
news:11**********************@i42g2000cwa.googlegr oups.com...
Hi John,

Your Not is a Not(Test.Status) and returns a Boolean, which is never
equal to "Vervallen"

Better use:
WHERE Not (Test.Status = "Vervallen")
or
WHERE (Test.Status <"Vervallen")

This under the condition that Test.Status has a string-value and is not
a Boolean.

Success, HBInc.


john wrote:
>For test reasons I made it very basic, a two field table. The query is:

SELECT Test.ID_applicatie, Test.Status
FROM Test
WHERE ((Not (Test.Status)="Vervallen"));

For this message i used 'Johnson' in stead of 'Vervallen'.
john

"John Welch" <john(remove)welch@cal(remove)central.comschreef in
bericht
news:e9********@enews4.newsguy.com...
It's hard to say without more details. Can you post the sql that is
generated by this query? (click on SQL view instead of the blue design
triangle).
Possible causes may be that you have some other criterion going on at
the
same time and the combination excludes all records, or that you did an
inner join to another table in which there are no matching records, or
at
least no matching records that aren't called "johnson".
good luck
"john" <jo**@test.comwrote in message
news:Ls********************@casema.nl...
Out of a table I'm trying to pull out every record in which the field
'Lastname' is not 'Johnson'.
In order to do this I've put the following next to the 'criteria'
field
in the query-window:
Not "Johnson"
However the resulting table is empty.
Does someone know why it's not working?
Thanks,
john

Jul 14 '06 #12

This discussion thread is closed

Replies have been disabled for this discussion.