Current Month Last

[JohnLute]

I've searched the forum but can't find a similar challenge. I use the
following to drive a report bar chart:

SELECT (Format([DateReceived],"mmm"" '""yy")) AS Expr1,
Sum(qryCompsFacsMonths.TotalComps) AS SumOfTotalComps1
FROM qryCompsFacsMonths
GROUP BY (Format([DateReceived],"mmm"" '""yy")),
(Year([DateReceived])*12+Month([DateReceived])-1)
ORDER BY (Year([DateReceived])*12+Month([DateReceived])-1);

How can I modify this so that the current month displays last (at the right)
and that only 11 previous months display?

Thanks for your help!

 

[Ken Snell \(MVP\)]

Perhaps this:

SELECT (Format([DateReceived],"mmm"" '""yy")) AS Expr1,
Sum(qryCompsFacsMonths.TotalComps) AS SumOfTotalComps1
FROM qryCompsFacsMonths
WHERE [DateReceived] Between
DateAdd("m", -11, DateSerial(Year(Date()), Month(Date()), 1)
And DateSerial(Year(Date()), Month(Date()) + 1, 0)
GROUP BY (Format([DateReceived],"mmm"" '""yy")),
(Year([DateReceived])*12+Month([DateReceived])-1)
ORDER BY (Year([DateReceived])*12+Month([DateReceived])-1);

 

[JohnLute]

Thanks, Ken!

This returns a syntax error (missing operator) in query expression...

I don't see it. It looks "ok" to me. Is anything immediately clear to you?

--
www.Marzetti.com

Perhaps this:

SELECT (Format([DateReceived],"mmm"" '""yy")) AS Expr1,
Sum(qryCompsFacsMonths.TotalComps) AS SumOfTotalComps1
FROM qryCompsFacsMonths
WHERE [DateReceived] Between
DateAdd("m", -11, DateSerial(Year(Date()), Month(Date()), 1)
And DateSerial(Year(Date()), Month(Date()) + 1, 0)
GROUP BY (Format([DateReceived],"mmm"" '""yy")),
(Year([DateReceived])*12+Month([DateReceived])-1)
ORDER BY (Year([DateReceived])*12+Month([DateReceived])-1);


--

Ken Snell
<MS ACCESS MVP>

I've searched the forum but can't find a similar challenge. I use the
following to drive a report bar chart:

SELECT (Format([DateReceived],"mmm"" '""yy")) AS Expr1,
Sum(qryCompsFacsMonths.TotalComps) AS SumOfTotalComps1
FROM qryCompsFacsMonths
GROUP BY (Format([DateReceived],"mmm"" '""yy")),
(Year([DateReceived])*12+Month([DateReceived])-1)
ORDER BY (Year([DateReceived])*12+Month([DateReceived])-1);

How can I modify this so that the current month displays last (at the
right)
and that only 11 previous months display?

Thanks for your help!

 

[John Spencer]

Count your parentheses and you will note that you need another closing
parens at the end of the expression to calculate the beginning date of the
criteria.

SELECT (Format([DateReceived],"mmm"" '""yy")) AS Expr1,
Sum(qryCompsFacsMonths.TotalComps) AS SumOfTotalComps1
FROM qryCompsFacsMonths
WHERE [DateReceived] Between
DateAdd("m", -11, DateSerial(Year(Date()), Month(Date()), 1) )
And DateSerial(Year(Date()), Month(Date()) + 1, 0)
GROUP BY (Format([DateReceived],"mmm"" '""yy")),
(Year([DateReceived])*12+Month([DateReceived])-1)
ORDER BY (Year([DateReceived])*12+Month([DateReceived])-1);

You could simplify that a bit amd avoid using the DateAdd function

Between DateSerial(Year(Date()), Month(Date())-11,1) and
DateSerial(Year(Date()),Month(Date())+1, 0)


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Perhaps this:

SELECT (Format([DateReceived],"mmm"" '""yy")) AS Expr1,
Sum(qryCompsFacsMonths.TotalComps) AS SumOfTotalComps1
FROM qryCompsFacsMonths
WHERE [DateReceived] Between
DateAdd("m", -11, DateSerial(Year(Date()), Month(Date()), 1)
And DateSerial(Year(Date()), Month(Date()) + 1, 0)
GROUP BY (Format([DateReceived],"mmm"" '""yy")),
(Year([DateReceived])*12+Month([DateReceived])-1)
ORDER BY (Year([DateReceived])*12+Month([DateReceived])-1);


--

Ken Snell
<MS ACCESS MVP>

I've searched the forum but can't find a similar challenge. I use the
following to drive a report bar chart:

SELECT (Format([DateReceived],"mmm"" '""yy")) AS Expr1,
Sum(qryCompsFacsMonths.TotalComps) AS SumOfTotalComps1
FROM qryCompsFacsMonths
GROUP BY (Format([DateReceived],"mmm"" '""yy")),
(Year([DateReceived])*12+Month([DateReceived])-1)
ORDER BY (Year([DateReceived])*12+Month([DateReceived])-1);

How can I modify this so that the current month displays last (at the
right)
and that only 11 previous months display?

Thanks for your help!

 

[JohnLute]

NIFTY!

Thanks a bunch! The simplification works, too!

--
www.Marzetti.com

Count your parentheses and you will note that you need another closing
parens at the end of the expression to calculate the beginning date of the
criteria.

SELECT (Format([DateReceived],"mmm"" '""yy")) AS Expr1,
Sum(qryCompsFacsMonths.TotalComps) AS SumOfTotalComps1
FROM qryCompsFacsMonths
WHERE [DateReceived] Between
DateAdd("m", -11, DateSerial(Year(Date()), Month(Date()), 1) )
And DateSerial(Year(Date()), Month(Date()) + 1, 0)
GROUP BY (Format([DateReceived],"mmm"" '""yy")),
(Year([DateReceived])*12+Month([DateReceived])-1)
ORDER BY (Year([DateReceived])*12+Month([DateReceived])-1);

You could simplify that a bit amd avoid using the DateAdd function

Between DateSerial(Year(Date()), Month(Date())-11,1) and
DateSerial(Year(Date()),Month(Date())+1, 0)


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Perhaps this:

SELECT (Format([DateReceived],"mmm"" '""yy")) AS Expr1,
Sum(qryCompsFacsMonths.TotalComps) AS SumOfTotalComps1
FROM qryCompsFacsMonths
WHERE [DateReceived] Between
DateAdd("m", -11, DateSerial(Year(Date()), Month(Date()), 1)
And DateSerial(Year(Date()), Month(Date()) + 1, 0)
GROUP BY (Format([DateReceived],"mmm"" '""yy")),
(Year([DateReceived])*12+Month([DateReceived])-1)
ORDER BY (Year([DateReceived])*12+Month([DateReceived])-1);


--

Ken Snell
<MS ACCESS MVP>

I've searched the forum but can't find a similar challenge. I use the
following to drive a report bar chart:

SELECT (Format([DateReceived],"mmm"" '""yy")) AS Expr1,
Sum(qryCompsFacsMonths.TotalComps) AS SumOfTotalComps1
FROM qryCompsFacsMonths
GROUP BY (Format([DateReceived],"mmm"" '""yy")),
(Year([DateReceived])*12+Month([DateReceived])-1)
ORDER BY (Year([DateReceived])*12+Month([DateReceived])-1);

How can I modify this so that the current month displays last (at the
right)
and that only 11 previous months display?

Thanks for your help!

 

[Ken Snell \(MVP\)]

Count your parentheses and you will note that you need another closing
parens at the end of the expression to calculate the beginning date of the
criteria.

Yep, thanks -- fumble fingers.

You could simplify that a bit amd avoid using the DateAdd function

Good enhancement.