a precedence problem?

[Pwyd]

All right. So i have several fields in this query. Here's my problem: if i
remove the part about "alba" "natasha" and "yohannes" it includes everything.
whether the field is null or not. If i have that field and restriction in
there, however, it also removes any of the records which have that field as
null. I want the filter to include null field, AND natasha, alba, yohannes.
i've tried both adding it further down in the criteria list, on its own line,
and making a non-showing field that has just that criteria. no luck. What am
i doing wrong?

Code for query:

SELECT DISTINCTROW MailLog.Batchnumber AS [Batch Number],
MailLog.LoggingAssigned AS [Logging Assigned to], MailLog.LoggingAssignedDate
AS [Date Logging Assigned], MailLog.ProcessingAssignedto AS [Processing
Assigned to], MailLog.ProcessingAssignedDate AS [Date Processing Assigned]
FROM MailLog
WHERE (((MailLog.ProcessingAssignedto) Not Like "yohannes" And
(MailLog.ProcessingAssignedto) Not Like "Alba" And
(MailLog.ProcessingAssignedto) Not Like "Natasha") AND
((MailLog.ProcessingCompleteDate) Is Null) AND ((MailLog.Urgentcheckbox) Like
Yes)) OR (((MailLog.ProcessingCompleteDate) Like ""));

 

[Amy Blankenship]

Have you tried

MailLog.ProcessingAssignedto NOT IN ("Alba", "Natasha", "yohannes")
OR MailLog.ProcessingAssignedto IS NULL

?

HTH;

Amy

 

[fredg]

All right. So i have several fields in this query. Here's my problem: if i
remove the part about "alba" "natasha" and "yohannes" it includes everything.
whether the field is null or not. If i have that field and restriction in
there, however, it also removes any of the records which have that field as
null. I want the filter to include null field, AND natasha, alba, yohannes.
i've tried both adding it further down in the criteria list, on its own line,
and making a non-showing field that has just that criteria. no luck. What am
i doing wrong?

Code for query:

SELECT DISTINCTROW MailLog.Batchnumber AS [Batch Number],
MailLog.LoggingAssigned AS [Logging Assigned to], MailLog.LoggingAssignedDate
AS [Date Logging Assigned], MailLog.ProcessingAssignedto AS [Processing
Assigned to], MailLog.ProcessingAssignedDate AS [Date Processing Assigned]
FROM MailLog
WHERE (((MailLog.ProcessingAssignedto) Not Like "yohannes" And
(MailLog.ProcessingAssignedto) Not Like "Alba" And
(MailLog.ProcessingAssignedto) Not Like "Natasha") AND
((MailLog.ProcessingCompleteDate) Is Null) AND ((MailLog.Urgentcheckbox) Like
Yes)) OR (((MailLog.ProcessingCompleteDate) Like ""));

Amy answered your question, except I believe she overlooked

AND ((MailLog.Urgentcheckbox) Like Yes)) OR
(((MailLog.ProcessingCompleteDate) Like ""))

The use of Like in your criteria is inappropriate unless you are also
using a wildcard.

Search Access help on
Examples of Expressions + Examples of criteria used to retrieve
records + Part of a field's value (Like)
to learn how to us Like in a query criteria.

The snippet above should read:

AND ((MailLog.Urgentcheckbox)= Yes)) OR
(((MailLog.ProcessingCompleteDate) <> ""))

However, if ProcessingCompleteDate is a Date datatype field it cannot
have a zero length string ("") as a value, but it can be Null.

OR (((MailLog.ProcessingCompleteDate) Is Null

 

[Pwyd]

So i tried making those changes. Now its returning records that don't fit
the criteria. Its ignoring the "urgentbox checked = yes" criteria for some
of the records, i cannot fathom why.

All right. So i have several fields in this query. Here's my problem: if i
remove the part about "alba" "natasha" and "yohannes" it includes everything.
whether the field is null or not. If i have that field and restriction in
there, however, it also removes any of the records which have that field as
null. I want the filter to include null field, AND natasha, alba, yohannes.
i've tried both adding it further down in the criteria list, on its own line,
and making a non-showing field that has just that criteria. no luck. What am
i doing wrong?

Code for query:

SELECT DISTINCTROW MailLog.Batchnumber AS [Batch Number],
MailLog.LoggingAssigned AS [Logging Assigned to], MailLog.LoggingAssignedDate
AS [Date Logging Assigned], MailLog.ProcessingAssignedto AS [Processing
Assigned to], MailLog.ProcessingAssignedDate AS [Date Processing Assigned]
FROM MailLog
WHERE (((MailLog.ProcessingAssignedto) Not Like "yohannes" And
(MailLog.ProcessingAssignedto) Not Like "Alba" And
(MailLog.ProcessingAssignedto) Not Like "Natasha") AND
((MailLog.ProcessingCompleteDate) Is Null) AND ((MailLog.Urgentcheckbox) Like
Yes)) OR (((MailLog.ProcessingCompleteDate) Like ""));

Amy answered your question, except I believe she overlooked

AND ((MailLog.Urgentcheckbox) Like Yes)) OR
(((MailLog.ProcessingCompleteDate) Like ""))

The use of Like in your criteria is inappropriate unless you are also
using a wildcard.

Search Access help on
Examples of Expressions + Examples of criteria used to retrieve
records + Part of a field's value (Like)
to learn how to us Like in a query criteria.

The snippet above should read:

AND ((MailLog.Urgentcheckbox)= Yes)) OR
(((MailLog.ProcessingCompleteDate) <> ""))

However, if ProcessingCompleteDate is a Date datatype field it cannot
have a zero length string ("") as a value, but it can be Null.

OR (((MailLog.ProcessingCompleteDate) Is Null

 

[Pwyd]

Incidentally *I* didn't write the sql. Access did.

All right. So i have several fields in this query. Here's my problem: if i
remove the part about "alba" "natasha" and "yohannes" it includes everything.
whether the field is null or not. If i have that field and restriction in
there, however, it also removes any of the records which have that field as
null. I want the filter to include null field, AND natasha, alba, yohannes.
i've tried both adding it further down in the criteria list, on its own line,
and making a non-showing field that has just that criteria. no luck. What am
i doing wrong?

Code for query:

SELECT DISTINCTROW MailLog.Batchnumber AS [Batch Number],
MailLog.LoggingAssigned AS [Logging Assigned to], MailLog.LoggingAssignedDate
AS [Date Logging Assigned], MailLog.ProcessingAssignedto AS [Processing
Assigned to], MailLog.ProcessingAssignedDate AS [Date Processing Assigned]
FROM MailLog
WHERE (((MailLog.ProcessingAssignedto) Not Like "yohannes" And
(MailLog.ProcessingAssignedto) Not Like "Alba" And
(MailLog.ProcessingAssignedto) Not Like "Natasha") AND
((MailLog.ProcessingCompleteDate) Is Null) AND ((MailLog.Urgentcheckbox) Like
Yes)) OR (((MailLog.ProcessingCompleteDate) Like ""));

Amy answered your question, except I believe she overlooked

AND ((MailLog.Urgentcheckbox) Like Yes)) OR
(((MailLog.ProcessingCompleteDate) Like ""))

The use of Like in your criteria is inappropriate unless you are also
using a wildcard.

Search Access help on
Examples of Expressions + Examples of criteria used to retrieve
records + Part of a field's value (Like)
to learn how to us Like in a query criteria.

The snippet above should read:

AND ((MailLog.Urgentcheckbox)= Yes)) OR
(((MailLog.ProcessingCompleteDate) <> ""))

However, if ProcessingCompleteDate is a Date datatype field it cannot
have a zero length string ("") as a value, but it can be Null.

OR (((MailLog.ProcessingCompleteDate) Is Null

 

[Chris]

Try this query, if I understand what you are trying to do, it should work.
SELECT MailLog.Batchnumber AS [Batch Number], MailLog.LoggingAssigned AS
[Logging Assigned to],
MailLog.LoggingAssignedDate AS [Date Logging Assigned],
MailLog.ProcessingAssignedTo AS [Processing Assigned to],
MailLog.ProcessingAssignedDate AS [Date Processing Assigned]
FROM MailLog
WHERE (((MailLog.ProcessingAssignedTo) Not In ('Alba','Natsha','yohannes'))
AND ((MailLog.Urgentcheckbox)=Yes)) OR (((MailLog.ProcessingAssignedTo) Is
Null) AND ((MailLog.Urgentcheckbox)=Yes)) OR (((MailLog.ProcessingAssignedTo)
Not In ('Alba','Natsha','yohannes')) AND ((MailLog.ProcessingCompleteDate) Is
Null)) OR (((MailLog.ProcessingAssignedTo) Is Null) AND
((MailLog.ProcessingCompleteDate) Is Null));

 

[Amy Blankenship]

Have you tried using =true instead of =yes?

What records are you getting that do not meet the criteria?

-Amy

So i tried making those changes. Now its returning records that don't fit
the criteria. Its ignoring the "urgentbox checked = yes" criteria for
some
of the records, i cannot fathom why.

All right. So i have several fields in this query. Here's my problem:
if i
remove the part about "alba" "natasha" and "yohannes" it includes
everything.
whether the field is null or not. If i have that field and
restriction in
there, however, it also removes any of the records which have that
field as
null. I want the filter to include null field, AND natasha, alba,
yohannes.
i've tried both adding it further down in the criteria list, on its own
line,
and making a non-showing field that has just that criteria. no luck.
What am
i doing wrong?

Code for query:

SELECT DISTINCTROW MailLog.Batchnumber AS [Batch Number],
MailLog.LoggingAssigned AS [Logging Assigned to],
MailLog.LoggingAssignedDate
AS [Date Logging Assigned], MailLog.ProcessingAssignedto AS [Processing
Assigned to], MailLog.ProcessingAssignedDate AS [Date Processing
Assigned]
FROM MailLog
WHERE (((MailLog.ProcessingAssignedto) Not Like "yohannes" And
(MailLog.ProcessingAssignedto) Not Like "Alba" And
(MailLog.ProcessingAssignedto) Not Like "Natasha") AND
((MailLog.ProcessingCompleteDate) Is Null) AND
((MailLog.Urgentcheckbox) Like
Yes)) OR (((MailLog.ProcessingCompleteDate) Like ""));

Amy answered your question, except I believe she overlooked

AND ((MailLog.Urgentcheckbox) Like Yes)) OR
(((MailLog.ProcessingCompleteDate) Like ""))

The use of Like in your criteria is inappropriate unless you are also
using a wildcard.

Search Access help on
Examples of Expressions + Examples of criteria used to retrieve
records + Part of a field's value (Like)
to learn how to us Like in a query criteria.

The snippet above should read:

AND ((MailLog.Urgentcheckbox)= Yes)) OR
(((MailLog.ProcessingCompleteDate) <> ""))

However, if ProcessingCompleteDate is a Date datatype field it cannot
have a zero length string ("") as a value, but it can be Null.

OR (((MailLog.ProcessingCompleteDate) Is Null