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

[aaearhart]

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.

 

[John Spencer]

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;

 

[aaearhart]

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

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.

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.

 

[Tom Ellison]

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

 

[John Spencer]

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.

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.

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.