Access 97 Query problem

[Maggic]

I am trying to run a query to select records which satisfy the following
criteria:

TODAYD equal to today's date & SUBJECT not equal to "wasps" & TECHNICIAN not
equal to "carl"

Following is the sql code generated from my query:

SELECT Contacts.*, [LastName] & ", " & [firstName] AS LastFirst INTO Qtdate
FROM Contacts
WHERE (((Contacts.Todayd)=Date()) AND ((Contacts.Subject)<>"wasps") and
((Contacts.Technician)<>"carl"));

There are two records which satisfy the above criteria, but I get nothing.

If I eliminate the check on TECHNICIAN I get all the non wasp records, but I
of course also get the records with TECHNICIAN equal to "carl"

Obviously I am doing something wrong .. some help would be much appreciated.

Maggic

 

[Guest]

I'm no expert, but you could try using "NOT" instead of "<>". I'm not sure the "<>" operator is standard SQL syntax.

SELECT Contacts.*, [LastName] & ", " & [firstName] AS LastFirst INTO Qtdate
FROM Contacts
WHERE (((Contacts.Todayd)=Date()) AND NOT((Contacts.Subject)="wasps") and
NOT((Contacts.Technician)="carl"));

The above might or might not help, I'm not sure.

You could also try using the "LIKE" operator instead of "=".

SELECT Contacts.*, [LastName] & ", " & [firstName] AS LastFirst INTO Qtdate
FROM Contacts
WHERE (((Contacts.Todayd)=Date()) AND ((Contacts.Subject) NOT LIKE "wasps") and ((Contacts.Technician) NOT LIKE "carl"));

 

[Brendan Reynolds]

"<>" should not be a problem. I agree that it might be worth trying Like,
though, perhaps in the form LIKE "*carl*" - possibly there might be
something not very obvious in the data, like a leading or trailing space.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

I'm no expert, but you could try using "NOT" instead of "<>". I'm not sure

SELECT Contacts.*, [LastName] & ", " & [firstName] AS LastFirst INTO Qtdate
FROM Contacts
WHERE (((Contacts.Todayd)=Date()) AND NOT((Contacts.Subject)="wasps") and
NOT((Contacts.Technician)="carl"));

The above might or might not help, I'm not sure.

You could also try using the "LIKE" operator instead of "=".

SELECT Contacts.*, [LastName] & ", " & [firstName] AS LastFirst INTO Qtdate
FROM Contacts
WHERE (((Contacts.Todayd)=Date()) AND ((Contacts.Subject) NOT LIKE

"wasps") and ((Contacts.Technician) NOT LIKE "carl"));

 

[Maggic]

Tried that .. doesn't work for me. I am now using two queries, the first
one to extract all transactions for to-day and the second extracting the
"and not" criteria from the extracted to-days transactions.

Thanks for your help .. strange that it doesn't work

Maggic

I'm no expert, but you could try using "NOT" instead of "<>". I'm not sure

SELECT Contacts.*, [LastName] & ", " & [firstName] AS LastFirst INTO Qtdate
FROM Contacts
WHERE (((Contacts.Todayd)=Date()) AND NOT((Contacts.Subject)="wasps") and
NOT((Contacts.Technician)="carl"));

The above might or might not help, I'm not sure.

You could also try using the "LIKE" operator instead of "=".

SELECT Contacts.*, [LastName] & ", " & [firstName] AS LastFirst INTO Qtdate
FROM Contacts
WHERE (((Contacts.Todayd)=Date()) AND ((Contacts.Subject) NOT LIKE

"wasps") and ((Contacts.Technician) NOT LIKE "carl"));

 

[Brendan Reynolds]

In that case, it *might* be something to do with the placement of the
parentheses in the SQL statement, and the effect they have on the order of
evaluation. For example, "(a = b and c = d) or e = f" is a very different
query than "a = b and (c = d or e = f)".

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Tried that .. doesn't work for me. I am now using two queries, the first
one to extract all transactions for to-day and the second extracting the
"and not" criteria from the extracted to-days transactions.

Thanks for your help .. strange that it doesn't work

Maggic

I'm no expert, but you could try using "NOT" instead of "<>". I'm not

sure

SELECT Contacts.*, [LastName] & ", " & [firstName] AS LastFirst INTO Qtdate
FROM Contacts
WHERE (((Contacts.Todayd)=Date()) AND NOT((Contacts.Subject)="wasps") and
NOT((Contacts.Technician)="carl"));

The above might or might not help, I'm not sure.

You could also try using the "LIKE" operator instead of "=".

SELECT Contacts.*, [LastName] & ", " & [firstName] AS LastFirst INTO Qtdate
FROM Contacts
WHERE (((Contacts.Todayd)=Date()) AND ((Contacts.Subject) NOT LIKE

"wasps") and ((Contacts.Technician) NOT LIKE "carl"));