Current Month Last

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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


Ken Snell (MVP) said:
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>


JohnLute said:
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!
Click to expand...
Click to expand...
 
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
..

Ken Snell (MVP) said:
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>


JohnLute said:
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!
Click to expand...
Click to expand...
 
NIFTY!

Thanks a bunch! The simplification works, too!

--
www.Marzetti.com


John Spencer said:
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
..

Ken Snell (MVP) said:
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>


JohnLute said:
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!
Click to expand...
Click to expand...
Click to expand...
 
John Spencer said:
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.
Click to expand...

Yep, thanks -- fumble fingers.

You could simplify that a bit amd avoid using the DateAdd function
Click to expand...

Good enhancement.
 
Back
Top