How do I filter a report to show only specific date

K

Karen

Have updated a 97 database to 2003.
Report shows the history/dates of inspection of equipment. We want the
report to filter and show only when the next inspection is due.
97 query read:
SELECT tblAssetRegister.ID, tblAssetRegister.[Plant Item Number],
tblAssetRegister.Area, tblAssetRegister.Section, tblAssetRegister.Location,
tblAssetRegister.[Equipment Type], tblAssetRegister.InspFreq,
Last(tblInspections.Insp_Date) AS LastOfInsp_Date,
Last(tblInspections.Insp_Comments) AS LastOfInsp_Comments FROM
tblAssetRegister INNER JOIN tblInspections ON tblAssetRegister.ID =
tblInspections.Equip_Id WHERE (((IIf([forms]![frmMainMenu]![Area] Is
Null,[Area] Is Not Null Or [Area] Is
Null,[Area]=[forms]![frmMainMenu]![Area]))<>False) AND
((IIf([forms]![frmMainMenu]![Sect] Is Null,[Section] Is Not Null Or [Section]
Is Null,[Section]=[forms]![frmMainMenu]![Sect]))<>False) AND
((IIf([forms]![frmMainMenu]![Location] Is Null,[Location] Is Not Null Or
[Section] Is Null,[Location]=[forms]![frmMainMenu]![Location]))<>False))
GROUP BY tblAssetRegister.ID, tblAssetRegister.[Plant Item Number],
tblAssetRegister.Area, tblAssetRegister.Section, tblAssetRegister.Location,
tblAssetRegister.[Equipment Type], tblAssetRegister.InspFreq HAVING
(((tblAssetRegister.InspFreq)>0) AND
((Last(Format([Insp_date],"dd,mm,yyyy")))=Format(Date()-[InspFreq]*30,"dd,mm,yyyy"))) OR (((Last(tblInspections.Insp_Date)) Is Null));

How do I make it 2003 compatible?
Thanks
 
D

Duane Hookom

I'm surprised any of this works. "Last( )" is mostly useless in queries and
almost always should be replaced by Max( ).

Your IIf() statements seem wonky to me:
IIf(
[forms]![frmMainMenu]![Area] Is Null,
[Area] Is Not Null Or [Area] Is Null,
[Area]=[forms]![frmMainMenu]![Area]
)
IIf()s have an expression to evaluate for true or false followed by the
value to return if True and then the value to return if False.

I would also move the
(tblAssetRegister.InspFreq)>0
into the where clause since it doesn't involve an aggregate.

I don't understand why you convert the dates to text values for comparison.
I don't generally format any dates until they get to a form or report control.
 
K

KARL DEWEY

Try these queries --
Equip_Id_Last_Insp_Date --
SELECT tblAssetRegister.ID, Max(tblInspections.Insp_Date) AS MaxOfInsp_Date
FROM tblAssetRegister LEFT JOIN tblInspections ON tblAssetRegister.ID =
tblInspections.Equip_Id
WHERE (((tblAssetRegister.InspFreq)>0)) OR (((tblInspections.Equip_Id) Is
Null))
GROUP BY tblAssetRegister.ID;

SELECT tblAssetRegister.ID, tblAssetRegister.[Plant Item Number],
tblAssetRegister.Area, tblAssetRegister.Section, tblAssetRegister.Location,
tblAssetRegister.[Equipment Type], tblAssetRegister.InspFreq,
tblInspections.Insp_Comments, tblInspections.Insp_Date,
IIf([Equip_Id_Last_Insp_Date].[MaxOfInsp_Date] Is Null And
([tblAssetRegister].[InspFreq]>0),"Due
Now",[Equip_Id_Last_Insp_Date].[MaxOfInsp_Date]+([InspFreq]*30)) AS
Insp_Due_Date
FROM Equip_Id_Last_Insp_Date LEFT JOIN (tblAssetRegister LEFT JOIN
tblInspections ON tblAssetRegister.ID = tblInspections.Equip_Id) ON
Equip_Id_Last_Insp_Date.ID = tblAssetRegister.ID
WHERE (((tblInspections.Insp_Date)=[MaxOfInsp_Date] Or
(tblInspections.Insp_Date) Is Null));
 
K

KARL DEWEY

You want the forms --
SELECT tblAssetRegister.ID, tblAssetRegister.[Plant Item Number],
tblAssetRegister.Area, tblAssetRegister.Section, tblAssetRegister.Location,
tblAssetRegister.[Equipment Type], tblAssetRegister.InspFreq,
tblInspections.Insp_Comments, tblInspections.Insp_Date,
IIf([Equip_Id_Last_Insp_Date].[MaxOfInsp_Date] Is Null And
([tblAssetRegister].[InspFreq]>0),"Due
Now",[Equip_Id_Last_Insp_Date].[MaxOfInsp_Date]+([InspFreq]*30)) AS
Insp_Due_Date
FROM Equip_Id_Last_Insp_Date LEFT JOIN (tblAssetRegister LEFT JOIN
tblInspections ON tblAssetRegister.ID = tblInspections.Equip_Id) ON
Equip_Id_Last_Insp_Date.ID = tblAssetRegister.ID
WHERE (((tblAssetRegister.Area)=[forms]![frmMainMenu]![Area] Or
[forms]![frmMainMenu]![Area] Is Null) AND
((tblAssetRegister.Section)=[forms]![frmMainMenu]![Section] Or
[forms]![frmMainMenu]![Section] Is Null) AND
((tblAssetRegister.Location)=[forms]![frmMainMenu]![Location] Or
[forms]![frmMainMenu]![Location] Is Null) AND
((tblInspections.Insp_Date)=[MaxOfInsp_Date] Or (tblInspections.Insp_Date) Is
Null));


KARL DEWEY said:
Try these queries --
Equip_Id_Last_Insp_Date --
SELECT tblAssetRegister.ID, Max(tblInspections.Insp_Date) AS MaxOfInsp_Date
FROM tblAssetRegister LEFT JOIN tblInspections ON tblAssetRegister.ID =
tblInspections.Equip_Id
WHERE (((tblAssetRegister.InspFreq)>0)) OR (((tblInspections.Equip_Id) Is
Null))
GROUP BY tblAssetRegister.ID;

SELECT tblAssetRegister.ID, tblAssetRegister.[Plant Item Number],
tblAssetRegister.Area, tblAssetRegister.Section, tblAssetRegister.Location,
tblAssetRegister.[Equipment Type], tblAssetRegister.InspFreq,
tblInspections.Insp_Comments, tblInspections.Insp_Date,
IIf([Equip_Id_Last_Insp_Date].[MaxOfInsp_Date] Is Null And
([tblAssetRegister].[InspFreq]>0),"Due
Now",[Equip_Id_Last_Insp_Date].[MaxOfInsp_Date]+([InspFreq]*30)) AS
Insp_Due_Date
FROM Equip_Id_Last_Insp_Date LEFT JOIN (tblAssetRegister LEFT JOIN
tblInspections ON tblAssetRegister.ID = tblInspections.Equip_Id) ON
Equip_Id_Last_Insp_Date.ID = tblAssetRegister.ID
WHERE (((tblInspections.Insp_Date)=[MaxOfInsp_Date] Or
(tblInspections.Insp_Date) Is Null));


Karen said:
Have updated a 97 database to 2003.
Report shows the history/dates of inspection of equipment. We want the
report to filter and show only when the next inspection is due.
97 query read:
SELECT tblAssetRegister.ID, tblAssetRegister.[Plant Item Number],
tblAssetRegister.Area, tblAssetRegister.Section, tblAssetRegister.Location,
tblAssetRegister.[Equipment Type], tblAssetRegister.InspFreq,
Last(tblInspections.Insp_Date) AS LastOfInsp_Date,
Last(tblInspections.Insp_Comments) AS LastOfInsp_Comments FROM
tblAssetRegister INNER JOIN tblInspections ON tblAssetRegister.ID =
tblInspections.Equip_Id WHERE (((IIf([forms]![frmMainMenu]![Area] Is
Null,[Area] Is Not Null Or [Area] Is
Null,[Area]=[forms]![frmMainMenu]![Area]))<>False) AND
((IIf([forms]![frmMainMenu]![Sect] Is Null,[Section] Is Not Null Or [Section]
Is Null,[Section]=[forms]![frmMainMenu]![Sect]))<>False) AND
((IIf([forms]![frmMainMenu]![Location] Is Null,[Location] Is Not Null Or
[Section] Is Null,[Location]=[forms]![frmMainMenu]![Location]))<>False))
GROUP BY tblAssetRegister.ID, tblAssetRegister.[Plant Item Number],
tblAssetRegister.Area, tblAssetRegister.Section, tblAssetRegister.Location,
tblAssetRegister.[Equipment Type], tblAssetRegister.InspFreq HAVING
(((tblAssetRegister.InspFreq)>0) AND
((Last(Format([Insp_date],"dd,mm,yyyy")))=Format(Date()-[InspFreq]*30,"dd,mm,yyyy"))) OR (((Last(tblInspections.Insp_Date)) Is Null));

How do I make it 2003 compatible?
Thanks
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I count the nulls in a field on a table not related to my f 11
How do I get Taskpad to show only task due on a selected date. 1

Top