how to selectively filter the report based on criteria

[vandy]

Hi all,

I am attempting to filter out specific records from a tool inventory that
matches a criteria and run a report.


SELECT Toolno, ToolDesc, ModelNo, SerialNo, IIf(NameID Is Null, "Available:
" & Location,
"Out: " & DateTaken & " - " &
Name & "(" & Department &")") AS Status, NameID
FROM tbltoolmaster LEFT JOIN (tbltooltransaction LEFT JOIN tblname ON
tbltooltransaction.NameID =tblname.ID) ON tbltooltransaction.TrantoolID =
tbltoolmaster.ToolID And DateReturned Is Null;


This query gives me the status of every tool.

Toolno ToolDesc ModelNo SerialNo Status NameID
ET-2 Heat Gun 10008 Out: 6/11/2007 - Fred Flintstone(QA)
ET-3 Dielectric Tester AVDv 25VA 4368 Available:


how to display the records only with status out or records where NameID is
not null

can i have a filter were when i choose available displays all records that
are Available and filter out that gives me a list of tools that are out.

thanks for your help.

 

[Marshall Barton]

I am attempting to filter out specific records from a tool inventory that
matches a criteria and run a report.

SELECT Toolno, ToolDesc, ModelNo, SerialNo, IIf(NameID Is Null, "Available:
" & Location,
"Out: " & DateTaken & " - " &
Name & "(" & Department &")") AS Status, NameID
FROM tbltoolmaster LEFT JOIN (tbltooltransaction LEFT JOIN tblname ON
tbltooltransaction.NameID =tblname.ID) ON tbltooltransaction.TrantoolID =
tbltoolmaster.ToolID And DateReturned Is Null;

This query gives me the status of every tool.

Toolno ToolDesc ModelNo SerialNo Status NameID
ET-2 Heat Gun 10008 Out: 6/11/2007 - Fred Flintstone(QA)
ET-3 Dielectric Tester AVDv 25VA 4368 Available:

how to display the records only with status out or records where NameID is
not null

can i have a filter were when i choose available displays all records that
are Available and filter out that gives me a list of tools that are out.

Just add a criteria for the NameID field. Is Null will
return the available tools and Is Not Null will return the
tools that are out.

If you use an unbound option group (named grpInOut) on a
form with radio buttons: 1 - All, 2 - Available and 3 - Out,
then you can use that in the criteria:
WHERE (grpInOut=1)
OR (grpInOut=2 And NameID Is Null)
OR (grpInOut=3 And NameID Is Not Null)