Most efficient way to exclude records?

[nicheplayer]

I have a form I'm using to input criteria for a query that outputs to a report. The database is a job log that tracks a number of criteria on jobs done by the people in my department. I have a query set up that feeds my report, but what I'd like to do is include a way to *exclude* all records that have a certain value in their "task type" field. My input form will have a checkbox for, say, "list request" that -- when checked -- will modify the query in such a way that all records *except* those that include "list request" as the task type will be returned.

I don't know if I should create a temporary table that excludes the "list request" records and use that as a source in my existing query? What's the best way to handle something like this? Thanks very much!

 

[Golom]

Usually just a Where clause of the form

Where IIF(CheckBox = vbChecked, [Task Type] <> 'list request', 1 = 1)

The "1 = 1" just evaluates to TRUE and includes all records.

 

[nicheplayer]

Thanks very much for your reply. I'm unsure where to implement this code. Should it be the header in a new column in my query, or should it appear in the criteria of an existing column?

 

[Golom]

Coding stuff like this is unpleasent in the query grid. I recommend that you switch to SQL view and add this code to the SQL statement. If there's already a WHERE clause then add it as

AND IIF(CheckBox = vbChecked, [Task Type] <> 'list request', 1 = 1)

following other WHERE clause conditions.

 

[nicheplayer]

Here's what the end of my query looks like. It produces a syntax error.

HAVING (((Joblog.[Date In]) Between [forms]![rreports]![beginningdate] And [forms]![rreports]![endingdate]))
WHERE IIf([forms]![rreports]![Check98]=vbChecked, [Task Type] <> 'list request', 1=1);

Maybe I'm missing a paren somewhere? Thanks so much for your help.

 

[PHV]

SELECT ...
FROM ...
WHERE (Joblog.[Date In] Between [forms]![rreports]![beginningdate] And [forms]![rreports]![endingdate])
AND ([forms]![rreports]![Check98]=False Or [Task Type] <> 'list request');

As you don't use any aggregate function as criteria, get rid of the HAVING clause.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[nicheplayer]

Sorry. Actually, that was only the end of my query. The whole thing looks like this:

[code}
SELECT qryResearchStaff.ID, Joblog.[Job Number], Joblog.[Project Description], Joblog.[Task Type], Joblog.[Constituent Code], Joblog.[Requested By], Joblog.[Date In], Joblog.[Date Out], Joblog.[Time Spent], Joblog.Billed,
IIf([rush]=Yes,Count([rush])) AS RushTotal, IIf([rush]=Yes,[time spent]) AS RushBilled, IIf([billed]=Yes,[time spent]) AS TimeBilled, IIf([billed]=Yes,Sum([billed]+2)) AS TotalBilled, Joblog.Rush, qryResearchStaff.Fullname
FROM Joblog INNER JOIN qryResearchStaff ON Joblog.ID = qryResearchStaff.ID
GROUP BY qryResearchStaff.ID, Joblog.[Job Number], Joblog.[Project Description], Joblog.[Task Type], Joblog.[Constituent Code], Joblog.[Requested By], Joblog.[Date In], Joblog.[Date Out], Joblog.[Time Spent], Joblog.Billed, IIf([rush]=Yes,[time spent]), IIf([billed]=Yes,[time spent]), Joblog.Rush, qryResearchStaff.Fullname
HAVING (((Joblog.[Date In]) Between [forms]![rreports]![beginningdate] And [forms]![rreports]![endingdate]));
[/code]

So I'm looking for the best place (and way) to insert:

WHERE IIf([forms]![rreports]![Check98]=vbChecked, [Task Type] <> 'list request', 1=1)
[/code}

Again, I can't emphasize enough how much I appreciate your help.

 

[PHV]

SELECT ...
FROM ...
WHERE (Joblog.[Date In] Between [forms]![rreports]![beginningdate] And [forms]![rreports]![endingdate])
AND ([forms]![rreports]![Check98]=False Or [Task Type] <> 'list request')
GROUP BY ...;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[nicheplayer]

The SQL validates now, but this appears to be excluding records of type "list request" regardless of whether Check98 = Yes. I ran the query with Yes and No values for the checkbox, and it returned the same records. When I removed

AND ([forms]![rreports]![Check98]=False Or [Task Type] <> 'list request')

from the query, I got all the records again, including "list request" records.

 

[nicheplayer]

So...I guess I'm just SOL, huh? It seems like I'm tantalizingly close, but then Access always manages to confound my notions of what "simple" and "complicated" are. Thanks, all, for your time. I really do appreciate your help.