A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old September 21st, 2009, 05:11 AM posted to microsoft.public.access.tablesdbdesign
Bob Waggoner[_2_]
external usenet poster
 
Posts: 80
Default Limiting Due dates to the current month (when the data is queried)

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.

  #2  
Old September 21st, 2009, 07:14 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Limiting Due dates to the current month (when the data is queried)

On Sun, 20 Sep 2009 21:11:01 -0700, Bob Waggoner
wrote:

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 [MVP]
  #3  
Old September 21st, 2009, 04:51 PM posted to microsoft.public.access.tablesdbdesign
Bob Waggoner[_2_]
external usenet poster
 
Posts: 80
Default Limiting Due dates to the current month (when the data is quer

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]));



"John W. Vinson" wrote:

On Sun, 20 Sep 2009 21:11:01 -0700, Bob Waggoner
wrote:

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 [MVP]

  #4  
Old September 22nd, 2009, 01:48 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Limiting Due dates to the current month (when the data is quer

On Mon, 21 Sep 2009 08:51:02 -0700, Bob Waggoner
wrote:

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:

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?


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*.
--

John W. Vinson [MVP]
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:09 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.