WHERE clause returns A and B, but not A and C

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

aaearhart - 04 Jan 2006 18:39 GMT

Hello.

Here is my current query*:

SELECT [T-Jobs].JobName & "-" & [T-Jobs].JobNumber AS FullJob,
[T-Jobs].JobType
FROM [T-Jobs]
WHERE ((([T-Jobs].EndDate)>Now()-360)
AND IS NOT (([T-Jobs].JobType)="Show" And ([T-Jobs].Status)="Complete"))
ORDER BY [T-Jobs].JobType, [T-Jobs].JobName;

I realise the "AND IS NOT" is not correct. I've simply got it in there for
illustrative purposes.

What i need to do is this:

Return the JobName, JobNumber and JobType from tbl T-Jobs.
I need to return all items that have ended ([EndDate]) within the approx.
past year (>=Now()-360).
However, I want to exclude any records that are Completed
([Status]="Complete") Shows ([JobType]="Show").

What do I put in place of the "AND IS NOT?"

Thanks, in advance!
/amelia

*I'm already aware that using "-" in my naming was a poor choice, but I'm
stuck with it.

Reply to this Message

John Spencer - 04 Jan 2006 19:06 GMT

Try the following:

SELECT [T-Jobs].JobName & "-" & [T-Jobs].JobNumber AS FullJob,
[T-Jobs].JobType
FROM [T-Jobs]
WHERE [T-Jobs].EndDate>Now()-360
AND [T-Jobs].JobType<>"Show"
AND [T-Jobs].Status<>"Complete"
ORDER BY [T-Jobs].JobType, [T-Jobs].JobName;

> Hello.
>
[quoted text clipped - 25 lines]
> *I'm already aware that using "-" in my naming was a poor choice, but I'm
> stuck with it.

Reply to this Message

aaearhart - 04 Jan 2006 21:14 GMT

That seems to eliminate all Shows, which is not what i need.

> Try the following:
>
[quoted text clipped - 35 lines]
> > *I'm already aware that using "-" in my naming was a poor choice, but I'm
> > stuck with it.

Reply to this Message

John Spencer - 05 Jan 2006 12:39 GMT

Whoops! My error.

SELECT ...
FROM ...
Where EndDate>Date()-360 AND
NOT(JobType="Show" and Status="Complete")

> That seems to eliminate all Shows, which is not what i need.
>
[quoted text clipped - 41 lines]
>> > I'm
>> > stuck with it.

Reply to this Message

Tom Ellison - 05 Jan 2006 03:17 GMT

Dear Amelia:

Logic can be tough. Actually, I think you really almost had it.

SELECT JobName & "-" & JobNumber AS FullJob, JobType
FROM [T-Jobs]
WHERE EndDate > Now()-360
AND NOT (JobType = "Show" AND Status = "Complete")
ORDER BY JobType, JobName;

Pretty much just drop the word IS. I have eliminated the table references
since there's only one table.

Now, this part:

AND NOT (JobType = "Show" AND Status = "Complete")

could also be written:

AND (JobType <> "Show" OR Status <> "Complete")

This is logically equivalent. So, is that what you meant? Is you
definition of "Completed" that either Status = "Complete" or JobType =
"Show"?

Please let me know how this worked out for you.

Tom Ellison

> Hello.
>
[quoted text clipped - 25 lines]
> *I'm already aware that using "-" in my naming was a poor choice, but I'm
> stuck with it.

Reply to this Message