Limiting Due dates to the current month (when the data is queried)

[Bob Waggoner]

In a calibration database I have a query that calculates due dates. I'd like
to limit the query to just the calibrations due in the current month. How do
I modify this formula to make only the current month's due dates show up?

Expr1: Format$(([MaxOfDateDone]+[FreqDays]),"mmm yyyy")

MaxofDateDone is a query that only looks at the last calibration done.
Frequency Days is the number of days that a piece of equipment goes before
it needs another calibration.

Thank you for your help.

 

[John W. Vinson]

In a calibration database I have a query that calculates due dates. I'd like
to limit the query to just the calibrations due in the current month. How do
I modify this formula to make only the current month's due dates show up?

Expr1: Format$(([MaxOfDateDone]+[FreqDays]),"mmm yyyy")

MaxofDateDone is a query that only looks at the last calibration done.
Frequency Days is the number of days that a piece of equipment goes before
it needs another calibration.

Thank you for your help.

Correct the criteria in your query MaxOfDaysDone, using a criterion such as

= DateSerial(Year(Date()), Month(Date()), 1) AND < DateSerial(Year(Date()), Month(Date()) + 1, 1)

to retrieve only dates during the current month. You can't do it in the
Format() function.

 

[Bob Waggoner]

John,
Thank you for your reply. When I add >= DateSerial(Year(Date()),
Month(Date()), 1) AND < DateSerial(Year(Date()), Month(Date()) + 1, 1)

to the criteria under Expr2 (BELOW) which is: MaxofDateDone + freqDays I get
a list of pms due in September. How do I get PMs that might be overdue (from
august) as well?

SELECT Format$(([MaxOfDateDone]+[FreqDays]),"mmm yyyy") AS Expr1,
(([MaxOfDateDone]+[FreqDays])) AS Expr2, tbl76PMs.DateDone
FROM (tbl76CalibEquipment INNER JOIN tbl76PMFrequency ON
tbl76CalibEquipment.Frequency = tbl76PMFrequency.Frequency) INNER JOIN
(qry76PMsLastDone INNER JOIN tbl76PMs ON (tbl76PMs.EquipID =
qry76PMsLastDone.EquipID) AND (qry76PMsLastDone.MaxOfDateDone =
tbl76PMs.DateDone)) ON tbl76CalibEquipment.EquipID = tbl76PMs.EquipID
WHERE (((tbl76CalibEquipment.MaintType)="PM"))
ORDER BY (([MaxOfDateDone]+[FreqDays]));

When I add SELECT Format$(([MaxOfDateDone]+[FreqDays]),"mmm yyyy") AS Expr1,
(([MaxOfDateDone]+[FreqDays])) AS Expr2, tbl76PMs.DateDone
FROM (tbl76CalibEquipment INNER JOIN tbl76PMFrequency ON
tbl76CalibEquipment.Frequency = tbl76PMFrequency.Frequency) INNER JOIN
(qry76PMsLastDone INNER JOIN tbl76PMs ON (tbl76PMs.EquipID =
qry76PMsLastDone.EquipID) AND (qry76PMsLastDone.MaxOfDateDone =
tbl76PMs.DateDone)) ON tbl76CalibEquipment.EquipID = tbl76PMs.EquipID
WHERE
((((([MaxOfDateDone]+[FreqDays])))>=DateSerial(Year(Date()),Month(Date()),1)
And
((([MaxOfDateDone]+[FreqDays])))<DateSerial(Year(Date()),Month(Date())+1,1))
AND ((tbl76CalibEquipment.MaintType)="PM"))
ORDER BY (([MaxOfDateDone]+[FreqDays]));

In a calibration database I have a query that calculates due dates. I'd like
to limit the query to just the calibrations due in the current month. How do
I modify this formula to make only the current month's due dates show up?

Expr1: Format$(([MaxOfDateDone]+[FreqDays]),"mmm yyyy")

MaxofDateDone is a query that only looks at the last calibration done.
Frequency Days is the number of days that a piece of equipment goes before
it needs another calibration.

Thank you for your help.

Correct the criteria in your query MaxOfDaysDone, using a criterion such as

= DateSerial(Year(Date()), Month(Date()), 1) AND < DateSerial(Year(Date()), Month(Date()) + 1, 1)

to retrieve only dates during the current month. You can't do it in the
Format() function.

 

[John W. Vinson]

John,
Thank you for your reply. When I add >= DateSerial(Year(Date()),
Month(Date()), 1) AND < DateSerial(Year(Date()), Month(Date()) + 1, 1)

to the criteria under Expr2 (BELOW) which is: MaxofDateDone + freqDays I get
a list of pms due in September.

That's what you asked for, so that's the question I answered:

How do I get PMs that might be overdue (from
august) as well?

I don't know, because I don't know the structure of your database nor how you
determine that a record is overdue. Perhaps just leave off the first date
criterion if you want all records in September *or earlier*.