HillTJ
To train a dog, first know more than the dog..
- Local time
- Today, 11:34
- Joined
- Apr 1, 2019
- Messages
- 731
Hi,
I have a query that calculates a future inspection date as follows;
Schedule: IIf([Qry_Inspect_Record _Test]![MaxOfCalibration_Frequency]=1,DateAdd("yyyy",[MaxofCalibration_Unit],[MaxOfAnniversary_Date]),IIf([Qry_Inspect_Record _Test]![MaxOfCalibration_Frequency]=2,DateAdd("m",[MaxofCalibration_Unit],[MaxOfAnniversary_Date]),IIf([Qry_Inspect_Record _Test]![MaxOfCalibration_Frequency]=3,DateAdd("m",[MaxofCalibration_Unit],[MaxOfAnniversary_Date]),IIf([Qry_Inspect_Record _Test]![MaxOfCalibration_Frequency]=4,DateAdd("d",[MaxofCalibration_Unit],[MaxOfAnniversary_Date]),""))))
Where MaxofCalibration_Unit & MaxOfAnniversary_Date come from another query that gets the latest record by date. So I'm really calculating the next occurrence. This works fine & when I use a sort criteria of <Date() it filters out Overdue Inspections. What I'd like to do is run a query that allows me to filter records that have an inspection date within the next "Rolling"month.
I found this ; Year([SalesDate])* 12 + DatePart("m", [SalesDate]) = Year(Date())* 12 + DatePart("m", Date()) - 1 .I guess I have to substitute each incidence of [SalesDate] for my equation above. Is their an easier way?
Appreciate the feedback.
I have a query that calculates a future inspection date as follows;
Schedule: IIf([Qry_Inspect_Record _Test]![MaxOfCalibration_Frequency]=1,DateAdd("yyyy",[MaxofCalibration_Unit],[MaxOfAnniversary_Date]),IIf([Qry_Inspect_Record _Test]![MaxOfCalibration_Frequency]=2,DateAdd("m",[MaxofCalibration_Unit],[MaxOfAnniversary_Date]),IIf([Qry_Inspect_Record _Test]![MaxOfCalibration_Frequency]=3,DateAdd("m",[MaxofCalibration_Unit],[MaxOfAnniversary_Date]),IIf([Qry_Inspect_Record _Test]![MaxOfCalibration_Frequency]=4,DateAdd("d",[MaxofCalibration_Unit],[MaxOfAnniversary_Date]),""))))
Where MaxofCalibration_Unit & MaxOfAnniversary_Date come from another query that gets the latest record by date. So I'm really calculating the next occurrence. This works fine & when I use a sort criteria of <Date() it filters out Overdue Inspections. What I'd like to do is run a query that allows me to filter records that have an inspection date within the next "Rolling"month.
I found this ; Year([SalesDate])* 12 + DatePart("m", [SalesDate]) = Year(Date())* 12 + DatePart("m", Date()) - 1 .I guess I have to substitute each incidence of [SalesDate] for my equation above. Is their an easier way?
Appreciate the feedback.