How do I filter a report to show only specific date

  • Thread starter Thread starter Karen
  • Start date Start 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
 
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.
 
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));
 
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...
 
Back
Top