Select Max and Next to Max Dates

  • Thread starter Thread starter PJFry
  • Start date Start date
P

PJFry

I am writing a query that pulls the most recent month start date and the
month before the most recent (eg. #10/1/2008# and #11/1/2008#)

Currently I write to subqueries in the criteria line:
CDate(Month((SELECT MAX(dtmMonthStart) FROM tInventory))-1 & "/1/" &
Year((SELECT MAX(dtmMonthStart) FROM tInventory)))
Or
(SELECT MAX(dtmMonthStart) FROM tInventory)

This works, but I have to believe there is a better way to pull the data.
When I get to 1/1/2009 the query will break because the prior month query
will return #12/1/2009#.

Thoughts?

PJ
 
Not completely sure I'm understanding what you want, but if
dtmMonthStart always contains the first of the month and you want
to show both the current month and the previous month as fields
in the same query, then this should work;

SELECT Max([dtmMonthStart]) AS CurrentMonth,
DateAdd("m",-1,Max([dtmMonthStart])) AS PreviousMonth
FROM tInventory;
 
That is just wanted I needed.

Thanks!

Beetle said:
Not completely sure I'm understanding what you want, but if
dtmMonthStart always contains the first of the month and you want
to show both the current month and the previous month as fields
in the same query, then this should work;

SELECT Max([dtmMonthStart]) AS CurrentMonth,
DateAdd("m",-1,Max([dtmMonthStart])) AS PreviousMonth
FROM tInventory;

--
_________

Sean Bailey


PJFry said:
I am writing a query that pulls the most recent month start date and the
month before the most recent (eg. #10/1/2008# and #11/1/2008#)

Currently I write to subqueries in the criteria line:
CDate(Month((SELECT MAX(dtmMonthStart) FROM tInventory))-1 & "/1/" &
Year((SELECT MAX(dtmMonthStart) FROM tInventory)))
Or
(SELECT MAX(dtmMonthStart) FROM tInventory)

This works, but I have to believe there is a better way to pull the data.
When I get to 1/1/2009 the query will break because the prior month query
will return #12/1/2009#.

Thoughts?

PJ
Click to expand...
Click to expand...
 
Back
Top