current month / year WHERE

J

JohnLute

I use this Row Source for a report chart:
SELECT (Format([DateReceived],"mmm"" '""yy")) AS DateReceived1,
Sum(qryCompsFacsMonths.TotalComps) AS SumOfTotalComps1
FROM qryCompsFacsMonths
WHERE (((qryCompsFacsMonths.DateReceived) Between
DateSerial(Year(Date()),Month(Date())-11,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);

This works well and I'm trying to get another chart to do the same however
it's not behaving. This is its Row Source:
SELECT (Year([ProdDate1])*12+Month([ProdDate1])-1) AS Expr1,
qryCompsFacsPerMonthSum100M.Per100MSum
FROM qryCompsFacsPerMonthSum100M
WHERE (((qryCompsFacsPerMonthSum100M.ProdDate1) Between
DateSerial(Year(Date()),Month(Date())-11,1) And
DateSerial(Year(Date()),Month(Date())+1,0)))
GROUP BY (Year([ProdDate1])*12+Month([ProdDate1])-1),
qryCompsFacsPerMonthSum100M.Per100MSum
ORDER BY (Year([ProdDate1])*12+Month([ProdDate1])-1);

This returns no records. I suspect that there's a clash with [ProdDate1]...?

Thanks for your help!
 
K

KARL DEWEY

If you are only looking for current month and year then try this --

WHERE Format([qryCompsFacsPerMonthSum100M].[ProdDate1], "yyyymm") =
Format(Date(),"yyyymm")
GROUP BY Format([qryCompsFacsPerMonthSum100M].[ProdDate1], "yyyymm"),
qryCompsFacsPerMonthSum100M.Per100MSum
ORDER BY Format([qryCompsFacsPerMonthSum100M].[ProdDate1], "yyyymm");
 
J

JohnLute

Thanks, Karl.

I'm looking for current month to display last (furthest to the right) on a
bar graph with the 11 previous months to its left.

I made a couple changes:
SELECT qryCompsFacsPerMonthSum100M.ProdDate1,
qryCompsFacsPerMonthSum100M.Per100MSum
FROM qryCompsFacsPerMonthSum100M
WHERE (((qryCompsFacsPerMonthSum100M.ProdDate1) Between
DateSerial(Year(Date()),Month(Date())-11,1) And
DateSerial(Year(Date()),Month(Date())+1,0)))
GROUP BY qryCompsFacsPerMonthSum100M.ProdDate1,
qryCompsFacsPerMonthSum100M.Per100MSum,
(Year([ProdDate1])*12+Month([ProdDate1])-1)
ORDER BY (Year([ProdDate1])*12+Month([ProdDate1])-1);

This still returns no records. I really suspect a problem with [ProdDate1].
In its table it's type is Date/Time with the format "mmm/yyyy". In one of the
underlying queries I re-format this to [ProdDate1]:
ProdDate1: (Format([ProdDate],"mmm"" '""yy"))

Is this format/re-formatting perhaps clashing with the WHERE and GROUP
clauses?

Thanks!

--
www.Marzetti.com


KARL DEWEY said:
If you are only looking for current month and year then try this --

WHERE Format([qryCompsFacsPerMonthSum100M].[ProdDate1], "yyyymm") =
Format(Date(),"yyyymm")
GROUP BY Format([qryCompsFacsPerMonthSum100M].[ProdDate1], "yyyymm"),
qryCompsFacsPerMonthSum100M.Per100MSum
ORDER BY Format([qryCompsFacsPerMonthSum100M].[ProdDate1], "yyyymm");

--
KARL DEWEY
Build a little - Test a little


JohnLute said:
I use this Row Source for a report chart:
SELECT (Format([DateReceived],"mmm"" '""yy")) AS DateReceived1,
Sum(qryCompsFacsMonths.TotalComps) AS SumOfTotalComps1
FROM qryCompsFacsMonths
WHERE (((qryCompsFacsMonths.DateReceived) Between
DateSerial(Year(Date()),Month(Date())-11,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);

This works well and I'm trying to get another chart to do the same however
it's not behaving. This is its Row Source:
SELECT (Year([ProdDate1])*12+Month([ProdDate1])-1) AS Expr1,
qryCompsFacsPerMonthSum100M.Per100MSum
FROM qryCompsFacsPerMonthSum100M
WHERE (((qryCompsFacsPerMonthSum100M.ProdDate1) Between
DateSerial(Year(Date()),Month(Date())-11,1) And
DateSerial(Year(Date()),Month(Date())+1,0)))
GROUP BY (Year([ProdDate1])*12+Month([ProdDate1])-1),
qryCompsFacsPerMonthSum100M.Per100MSum
ORDER BY (Year([ProdDate1])*12+Month([ProdDate1])-1);

This returns no records. I suspect that there's a clash with [ProdDate1]...?

Thanks for your help!
Click to expand...
Click to expand...
 
K

KARL DEWEY

Did you try what I posted?

Tell me how does this work - Year([ProdDate1])*12+Month([ProdDate1])-1

If I use the current date in it --- Year(Date())*12+Month(Date())-1 ---
I get 12/16/1965.

--
KARL DEWEY
Build a little - Test a little


JohnLute said:
Thanks, Karl.

I'm looking for current month to display last (furthest to the right) on a
bar graph with the 11 previous months to its left.

I made a couple changes:
SELECT qryCompsFacsPerMonthSum100M.ProdDate1,
qryCompsFacsPerMonthSum100M.Per100MSum
FROM qryCompsFacsPerMonthSum100M
WHERE (((qryCompsFacsPerMonthSum100M.ProdDate1) Between
DateSerial(Year(Date()),Month(Date())-11,1) And
DateSerial(Year(Date()),Month(Date())+1,0)))
GROUP BY qryCompsFacsPerMonthSum100M.ProdDate1,
qryCompsFacsPerMonthSum100M.Per100MSum,
(Year([ProdDate1])*12+Month([ProdDate1])-1)
ORDER BY (Year([ProdDate1])*12+Month([ProdDate1])-1);

This still returns no records. I really suspect a problem with [ProdDate1].
In its table it's type is Date/Time with the format "mmm/yyyy". In one of the
underlying queries I re-format this to [ProdDate1]:
ProdDate1: (Format([ProdDate],"mmm"" '""yy"))

Is this format/re-formatting perhaps clashing with the WHERE and GROUP
clauses?

Thanks!

--
www.Marzetti.com


KARL DEWEY said:
If you are only looking for current month and year then try this --

WHERE Format([qryCompsFacsPerMonthSum100M].[ProdDate1], "yyyymm") =
Format(Date(),"yyyymm")
GROUP BY Format([qryCompsFacsPerMonthSum100M].[ProdDate1], "yyyymm"),
qryCompsFacsPerMonthSum100M.Per100MSum
ORDER BY Format([qryCompsFacsPerMonthSum100M].[ProdDate1], "yyyymm");

--
KARL DEWEY
Build a little - Test a little


JohnLute said:
I use this Row Source for a report chart:
SELECT (Format([DateReceived],"mmm"" '""yy")) AS DateReceived1,
Sum(qryCompsFacsMonths.TotalComps) AS SumOfTotalComps1
FROM qryCompsFacsMonths
WHERE (((qryCompsFacsMonths.DateReceived) Between
DateSerial(Year(Date()),Month(Date())-11,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);

This works well and I'm trying to get another chart to do the same however
it's not behaving. This is its Row Source:
SELECT (Year([ProdDate1])*12+Month([ProdDate1])-1) AS Expr1,
qryCompsFacsPerMonthSum100M.Per100MSum
FROM qryCompsFacsPerMonthSum100M
WHERE (((qryCompsFacsPerMonthSum100M.ProdDate1) Between
DateSerial(Year(Date()),Month(Date())-11,1) And
DateSerial(Year(Date()),Month(Date())+1,0)))
GROUP BY (Year([ProdDate1])*12+Month([ProdDate1])-1),
qryCompsFacsPerMonthSum100M.Per100MSum
ORDER BY (Year([ProdDate1])*12+Month([ProdDate1])-1);

This returns no records. I suspect that there's a clash with [ProdDate1]...?

Thanks for your help!
Click to expand...
Click to expand...
Click to expand...
 
J

JohnLute

KARL DEWEY said:
Did you try what I posted?
Click to expand...

Yes but it returned no records. That's when I made a couple revisions to the
code.
Tell me how does this work - Year([ProdDate1])*12+Month([ProdDate1])-1
Click to expand...

It works fine in the other code I listed in my first post. It returns the
months/years in chronological order from left to right with the furthest
right bar always being the current month/year and the previous 11 months to
its left.

Maybe I need to share what does "work":
SELECT qryCompsFacsPerMonthSum100M.ProdDate1,
qryCompsFacsPerMonthSum100M.Per100MSum
FROM qryCompsFacsPerMonthSum100M
GROUP BY qryCompsFacsPerMonthSum100M.ProdDate1,
qryCompsFacsPerMonthSum100M.Per100MSum;

This returns the correct data however the bar graph displays the months
alphabetically from left to right. I simply want the months/years in
chronological order from left to right with the furthest right bar always
being the current month/year and the previous 11 months to its left.

When I add the same code that works for my other chart to the above code
that "works" then no records return.

I'm reaching my wits' end!
JohnLute said:
Thanks, Karl.

I'm looking for current month to display last (furthest to the right) on a
bar graph with the 11 previous months to its left.

I made a couple changes:
SELECT qryCompsFacsPerMonthSum100M.ProdDate1,
qryCompsFacsPerMonthSum100M.Per100MSum
FROM qryCompsFacsPerMonthSum100M
WHERE (((qryCompsFacsPerMonthSum100M.ProdDate1) Between
DateSerial(Year(Date()),Month(Date())-11,1) And
DateSerial(Year(Date()),Month(Date())+1,0)))
GROUP BY qryCompsFacsPerMonthSum100M.ProdDate1,
qryCompsFacsPerMonthSum100M.Per100MSum,
(Year([ProdDate1])*12+Month([ProdDate1])-1)
ORDER BY (Year([ProdDate1])*12+Month([ProdDate1])-1);

This still returns no records. I really suspect a problem with [ProdDate1].
In its table it's type is Date/Time with the format "mmm/yyyy". In one of the
underlying queries I re-format this to [ProdDate1]:
ProdDate1: (Format([ProdDate],"mmm"" '""yy"))

Is this format/re-formatting perhaps clashing with the WHERE and GROUP
clauses?

Thanks!

--
www.Marzetti.com


KARL DEWEY said:
If you are only looking for current month and year then try this --

WHERE Format([qryCompsFacsPerMonthSum100M].[ProdDate1], "yyyymm") =
Format(Date(),"yyyymm")
GROUP BY Format([qryCompsFacsPerMonthSum100M].[ProdDate1], "yyyymm"),
qryCompsFacsPerMonthSum100M.Per100MSum
ORDER BY Format([qryCompsFacsPerMonthSum100M].[ProdDate1], "yyyymm");

--
KARL DEWEY
Build a little - Test a little


:

I use this Row Source for a report chart:
SELECT (Format([DateReceived],"mmm"" '""yy")) AS DateReceived1,
Sum(qryCompsFacsMonths.TotalComps) AS SumOfTotalComps1
FROM qryCompsFacsMonths
WHERE (((qryCompsFacsMonths.DateReceived) Between
DateSerial(Year(Date()),Month(Date())-11,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);

This works well and I'm trying to get another chart to do the same however
it's not behaving. This is its Row Source:
SELECT (Year([ProdDate1])*12+Month([ProdDate1])-1) AS Expr1,
qryCompsFacsPerMonthSum100M.Per100MSum
FROM qryCompsFacsPerMonthSum100M
WHERE (((qryCompsFacsPerMonthSum100M.ProdDate1) Between
DateSerial(Year(Date()),Month(Date())-11,1) And
DateSerial(Year(Date()),Month(Date())+1,0)))
GROUP BY (Year([ProdDate1])*12+Month([ProdDate1])-1),
qryCompsFacsPerMonthSum100M.Per100MSum
ORDER BY (Year([ProdDate1])*12+Month([ProdDate1])-1);

This returns no records. I suspect that there's a clash with [ProdDate1]...?

Thanks for your help!
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

Apparently your query qryCompsFacsPerMonthSum100M produces the months in text
format. You will need to include an output field with numerical year and
month and in this query sort it for proper display order.
--
KARL DEWEY
Build a little - Test a little


JohnLute said:
KARL DEWEY said:
Did you try what I posted?
Click to expand...

Yes but it returned no records. That's when I made a couple revisions to the
code.
Tell me how does this work - Year([ProdDate1])*12+Month([ProdDate1])-1
Click to expand...

It works fine in the other code I listed in my first post. It returns the
months/years in chronological order from left to right with the furthest
right bar always being the current month/year and the previous 11 months to
its left.

Maybe I need to share what does "work":
SELECT qryCompsFacsPerMonthSum100M.ProdDate1,
qryCompsFacsPerMonthSum100M.Per100MSum
FROM qryCompsFacsPerMonthSum100M
GROUP BY qryCompsFacsPerMonthSum100M.ProdDate1,
qryCompsFacsPerMonthSum100M.Per100MSum;

This returns the correct data however the bar graph displays the months
alphabetically from left to right. I simply want the months/years in
chronological order from left to right with the furthest right bar always
being the current month/year and the previous 11 months to its left.

When I add the same code that works for my other chart to the above code
that "works" then no records return.

I'm reaching my wits' end!
JohnLute said:
Thanks, Karl.

I'm looking for current month to display last (furthest to the right) on a
bar graph with the 11 previous months to its left.

I made a couple changes:
SELECT qryCompsFacsPerMonthSum100M.ProdDate1,
qryCompsFacsPerMonthSum100M.Per100MSum
FROM qryCompsFacsPerMonthSum100M
WHERE (((qryCompsFacsPerMonthSum100M.ProdDate1) Between
DateSerial(Year(Date()),Month(Date())-11,1) And
DateSerial(Year(Date()),Month(Date())+1,0)))
GROUP BY qryCompsFacsPerMonthSum100M.ProdDate1,
qryCompsFacsPerMonthSum100M.Per100MSum,
(Year([ProdDate1])*12+Month([ProdDate1])-1)
ORDER BY (Year([ProdDate1])*12+Month([ProdDate1])-1);

This still returns no records. I really suspect a problem with [ProdDate1].
In its table it's type is Date/Time with the format "mmm/yyyy". In one of the
underlying queries I re-format this to [ProdDate1]:
ProdDate1: (Format([ProdDate],"mmm"" '""yy"))

Is this format/re-formatting perhaps clashing with the WHERE and GROUP
clauses?

Thanks!

--
www.Marzetti.com


:

If you are only looking for current month and year then try this --

WHERE Format([qryCompsFacsPerMonthSum100M].[ProdDate1], "yyyymm") =
Format(Date(),"yyyymm")
GROUP BY Format([qryCompsFacsPerMonthSum100M].[ProdDate1], "yyyymm"),
qryCompsFacsPerMonthSum100M.Per100MSum
ORDER BY Format([qryCompsFacsPerMonthSum100M].[ProdDate1], "yyyymm");

--
KARL DEWEY
Build a little - Test a little


:

I use this Row Source for a report chart:
SELECT (Format([DateReceived],"mmm"" '""yy")) AS DateReceived1,
Sum(qryCompsFacsMonths.TotalComps) AS SumOfTotalComps1
FROM qryCompsFacsMonths
WHERE (((qryCompsFacsMonths.DateReceived) Between
DateSerial(Year(Date()),Month(Date())-11,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);

This works well and I'm trying to get another chart to do the same however
it's not behaving. This is its Row Source:
SELECT (Year([ProdDate1])*12+Month([ProdDate1])-1) AS Expr1,
qryCompsFacsPerMonthSum100M.Per100MSum
FROM qryCompsFacsPerMonthSum100M
WHERE (((qryCompsFacsPerMonthSum100M.ProdDate1) Between
DateSerial(Year(Date()),Month(Date())-11,1) And
DateSerial(Year(Date()),Month(Date())+1,0)))
GROUP BY (Year([ProdDate1])*12+Month([ProdDate1])-1),
qryCompsFacsPerMonthSum100M.Per100MSum
ORDER BY (Year([ProdDate1])*12+Month([ProdDate1])-1);

This returns no records. I suspect that there's a clash with [ProdDate1]...?

Thanks for your help!
Click to expand...
Click to expand...
Click to expand...
 
J

JohnLute

Thanks, Karl.

I'm not sure I follow. What do you mean by "output field" and where do i put
it? In the underlying table?

Sorry - this is new ground for me.

--
www.Marzetti.com


KARL DEWEY said:
Apparently your query qryCompsFacsPerMonthSum100M produces the months in text
format. You will need to include an output field with numerical year and
month and in this query sort it for proper display order.
--
KARL DEWEY
Build a little - Test a little


JohnLute said:
KARL DEWEY said:
Did you try what I posted?
Click to expand...

Yes but it returned no records. That's when I made a couple revisions to the
code.
Tell me how does this work - Year([ProdDate1])*12+Month([ProdDate1])-1
Click to expand...

It works fine in the other code I listed in my first post. It returns the
months/years in chronological order from left to right with the furthest
right bar always being the current month/year and the previous 11 months to
its left.

Maybe I need to share what does "work":
SELECT qryCompsFacsPerMonthSum100M.ProdDate1,
qryCompsFacsPerMonthSum100M.Per100MSum
FROM qryCompsFacsPerMonthSum100M
GROUP BY qryCompsFacsPerMonthSum100M.ProdDate1,
qryCompsFacsPerMonthSum100M.Per100MSum;

This returns the correct data however the bar graph displays the months
alphabetically from left to right. I simply want the months/years in
chronological order from left to right with the furthest right bar always
being the current month/year and the previous 11 months to its left.

When I add the same code that works for my other chart to the above code
that "works" then no records return.

I'm reaching my wits' end!
:

Thanks, Karl.

I'm looking for current month to display last (furthest to the right) on a
bar graph with the 11 previous months to its left.

I made a couple changes:
SELECT qryCompsFacsPerMonthSum100M.ProdDate1,
qryCompsFacsPerMonthSum100M.Per100MSum
FROM qryCompsFacsPerMonthSum100M
WHERE (((qryCompsFacsPerMonthSum100M.ProdDate1) Between
DateSerial(Year(Date()),Month(Date())-11,1) And
DateSerial(Year(Date()),Month(Date())+1,0)))
GROUP BY qryCompsFacsPerMonthSum100M.ProdDate1,
qryCompsFacsPerMonthSum100M.Per100MSum,
(Year([ProdDate1])*12+Month([ProdDate1])-1)
ORDER BY (Year([ProdDate1])*12+Month([ProdDate1])-1);

This still returns no records. I really suspect a problem with [ProdDate1].
In its table it's type is Date/Time with the format "mmm/yyyy". In one of the
underlying queries I re-format this to [ProdDate1]:
ProdDate1: (Format([ProdDate],"mmm"" '""yy"))

Is this format/re-formatting perhaps clashing with the WHERE and GROUP
clauses?

Thanks!

--
www.Marzetti.com


:

If you are only looking for current month and year then try this --

WHERE Format([qryCompsFacsPerMonthSum100M].[ProdDate1], "yyyymm") =
Format(Date(),"yyyymm")
GROUP BY Format([qryCompsFacsPerMonthSum100M].[ProdDate1], "yyyymm"),
qryCompsFacsPerMonthSum100M.Per100MSum
ORDER BY Format([qryCompsFacsPerMonthSum100M].[ProdDate1], "yyyymm");

--
KARL DEWEY
Build a little - Test a little


:

I use this Row Source for a report chart:
SELECT (Format([DateReceived],"mmm"" '""yy")) AS DateReceived1,
Sum(qryCompsFacsMonths.TotalComps) AS SumOfTotalComps1
FROM qryCompsFacsMonths
WHERE (((qryCompsFacsMonths.DateReceived) Between
DateSerial(Year(Date()),Month(Date())-11,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);

This works well and I'm trying to get another chart to do the same however
it's not behaving. This is its Row Source:
SELECT (Year([ProdDate1])*12+Month([ProdDate1])-1) AS Expr1,
qryCompsFacsPerMonthSum100M.Per100MSum
FROM qryCompsFacsPerMonthSum100M
WHERE (((qryCompsFacsPerMonthSum100M.ProdDate1) Between
DateSerial(Year(Date()),Month(Date())-11,1) And
DateSerial(Year(Date()),Month(Date())+1,0)))
GROUP BY (Year([ProdDate1])*12+Month([ProdDate1])-1),
qryCompsFacsPerMonthSum100M.Per100MSum
ORDER BY (Year([ProdDate1])*12+Month([ProdDate1])-1);

This returns no records. I suspect that there's a clash with [ProdDate1]...?

Thanks for your help!
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

I am making the assumption that qryCompsFacsPerMonthSum100M is a query
and not a table.
So, based on that assumption also assume that ProdDate1 is formated as names
of the month and is the reason that it 'displays the months alphabetically
from left to right.'
You need a field that has year and month numerically to sort properly.
If all of these assumptions are correct then you need query
qryCompsFacsPerMonthSum100M to also have a field that has year and month
numerically.
Tell me where any of my assumptions are incorrect.
--
KARL DEWEY
Build a little - Test a little


JohnLute said:
Thanks, Karl.

I'm not sure I follow. What do you mean by "output field" and where do i put
it? In the underlying table?

Sorry - this is new ground for me.

--
www.Marzetti.com


KARL DEWEY said:
Apparently your query qryCompsFacsPerMonthSum100M produces the months in text
format. You will need to include an output field with numerical year and
month and in this query sort it for proper display order.
--
KARL DEWEY
Build a little - Test a little


JohnLute said:
:

Did you try what I posted?

Yes but it returned no records. That's when I made a couple revisions to the
code.

Tell me how does this work - Year([ProdDate1])*12+Month([ProdDate1])-1

It works fine in the other code I listed in my first post. It returns the
months/years in chronological order from left to right with the furthest
right bar always being the current month/year and the previous 11 months to
its left.

Maybe I need to share what does "work":
SELECT qryCompsFacsPerMonthSum100M.ProdDate1,
qryCompsFacsPerMonthSum100M.Per100MSum
FROM qryCompsFacsPerMonthSum100M
GROUP BY qryCompsFacsPerMonthSum100M.ProdDate1,
qryCompsFacsPerMonthSum100M.Per100MSum;

This returns the correct data however the bar graph displays the months
alphabetically from left to right. I simply want the months/years in
chronological order from left to right with the furthest right bar always
being the current month/year and the previous 11 months to its left.

When I add the same code that works for my other chart to the above code
that "works" then no records return.

I'm reaching my wits' end!


:

Thanks, Karl.

I'm looking for current month to display last (furthest to the right) on a
bar graph with the 11 previous months to its left.

I made a couple changes:
SELECT qryCompsFacsPerMonthSum100M.ProdDate1,
qryCompsFacsPerMonthSum100M.Per100MSum
FROM qryCompsFacsPerMonthSum100M
WHERE (((qryCompsFacsPerMonthSum100M.ProdDate1) Between
DateSerial(Year(Date()),Month(Date())-11,1) And
DateSerial(Year(Date()),Month(Date())+1,0)))
GROUP BY qryCompsFacsPerMonthSum100M.ProdDate1,
qryCompsFacsPerMonthSum100M.Per100MSum,
(Year([ProdDate1])*12+Month([ProdDate1])-1)
ORDER BY (Year([ProdDate1])*12+Month([ProdDate1])-1);

This still returns no records. I really suspect a problem with [ProdDate1].
In its table it's type is Date/Time with the format "mmm/yyyy". In one of the
underlying queries I re-format this to [ProdDate1]:
ProdDate1: (Format([ProdDate],"mmm"" '""yy"))

Is this format/re-formatting perhaps clashing with the WHERE and GROUP
clauses?

Thanks!

--
www.Marzetti.com


:

If you are only looking for current month and year then try this --

WHERE Format([qryCompsFacsPerMonthSum100M].[ProdDate1], "yyyymm") =
Format(Date(),"yyyymm")
GROUP BY Format([qryCompsFacsPerMonthSum100M].[ProdDate1], "yyyymm"),
qryCompsFacsPerMonthSum100M.Per100MSum
ORDER BY Format([qryCompsFacsPerMonthSum100M].[ProdDate1], "yyyymm");

--
KARL DEWEY
Build a little - Test a little


:

I use this Row Source for a report chart:
SELECT (Format([DateReceived],"mmm"" '""yy")) AS DateReceived1,
Sum(qryCompsFacsMonths.TotalComps) AS SumOfTotalComps1
FROM qryCompsFacsMonths
WHERE (((qryCompsFacsMonths.DateReceived) Between
DateSerial(Year(Date()),Month(Date())-11,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);

This works well and I'm trying to get another chart to do the same however
it's not behaving. This is its Row Source:
SELECT (Year([ProdDate1])*12+Month([ProdDate1])-1) AS Expr1,
qryCompsFacsPerMonthSum100M.Per100MSum
FROM qryCompsFacsPerMonthSum100M
WHERE (((qryCompsFacsPerMonthSum100M.ProdDate1) Between
DateSerial(Year(Date()),Month(Date())-11,1) And
DateSerial(Year(Date()),Month(Date())+1,0)))
GROUP BY (Year([ProdDate1])*12+Month([ProdDate1])-1),
qryCompsFacsPerMonthSum100M.Per100MSum
ORDER BY (Year([ProdDate1])*12+Month([ProdDate1])-1);

This returns no records. I suspect that there's a clash with [ProdDate1]...?

Thanks for your help!
Click to expand...
Click to expand...
Click to expand...
 
J

JohnLute

You assumptions are all correct - even though the "rule" is to never assume
you're very good at it :)

--
www.Marzetti.com


KARL DEWEY said:
I am making the assumption that qryCompsFacsPerMonthSum100M is a query
and not a table.
So, based on that assumption also assume that ProdDate1 is formated as names
of the month and is the reason that it 'displays the months alphabetically
from left to right.'
You need a field that has year and month numerically to sort properly.
If all of these assumptions are correct then you need query
qryCompsFacsPerMonthSum100M to also have a field that has year and month
numerically.
Tell me where any of my assumptions are incorrect.
--
KARL DEWEY
Build a little - Test a little


JohnLute said:
Thanks, Karl.

I'm not sure I follow. What do you mean by "output field" and where do i put
it? In the underlying table?

Sorry - this is new ground for me.

--
www.Marzetti.com


KARL DEWEY said:
Apparently your query qryCompsFacsPerMonthSum100M produces the months in text
format. You will need to include an output field with numerical year and
month and in this query sort it for proper display order.
--
KARL DEWEY
Build a little - Test a little


:

:

Did you try what I posted?

Yes but it returned no records. That's when I made a couple revisions to the
code.

Tell me how does this work - Year([ProdDate1])*12+Month([ProdDate1])-1

It works fine in the other code I listed in my first post. It returns the
months/years in chronological order from left to right with the furthest
right bar always being the current month/year and the previous 11 months to
its left.

Maybe I need to share what does "work":
SELECT qryCompsFacsPerMonthSum100M.ProdDate1,
qryCompsFacsPerMonthSum100M.Per100MSum
FROM qryCompsFacsPerMonthSum100M
GROUP BY qryCompsFacsPerMonthSum100M.ProdDate1,
qryCompsFacsPerMonthSum100M.Per100MSum;

This returns the correct data however the bar graph displays the months
alphabetically from left to right. I simply want the months/years in
chronological order from left to right with the furthest right bar always
being the current month/year and the previous 11 months to its left.

When I add the same code that works for my other chart to the above code
that "works" then no records return.

I'm reaching my wits' end!


:

Thanks, Karl.

I'm looking for current month to display last (furthest to the right) on a
bar graph with the 11 previous months to its left.

I made a couple changes:
SELECT qryCompsFacsPerMonthSum100M.ProdDate1,
qryCompsFacsPerMonthSum100M.Per100MSum
FROM qryCompsFacsPerMonthSum100M
WHERE (((qryCompsFacsPerMonthSum100M.ProdDate1) Between
DateSerial(Year(Date()),Month(Date())-11,1) And
DateSerial(Year(Date()),Month(Date())+1,0)))
GROUP BY qryCompsFacsPerMonthSum100M.ProdDate1,
qryCompsFacsPerMonthSum100M.Per100MSum,
(Year([ProdDate1])*12+Month([ProdDate1])-1)
ORDER BY (Year([ProdDate1])*12+Month([ProdDate1])-1);

This still returns no records. I really suspect a problem with [ProdDate1].
In its table it's type is Date/Time with the format "mmm/yyyy". In one of the
underlying queries I re-format this to [ProdDate1]:
ProdDate1: (Format([ProdDate],"mmm"" '""yy"))

Is this format/re-formatting perhaps clashing with the WHERE and GROUP
clauses?

Thanks!

--
www.Marzetti.com


:

If you are only looking for current month and year then try this --

WHERE Format([qryCompsFacsPerMonthSum100M].[ProdDate1], "yyyymm") =
Format(Date(),"yyyymm")
GROUP BY Format([qryCompsFacsPerMonthSum100M].[ProdDate1], "yyyymm"),
qryCompsFacsPerMonthSum100M.Per100MSum
ORDER BY Format([qryCompsFacsPerMonthSum100M].[ProdDate1], "yyyymm");

--
KARL DEWEY
Build a little - Test a little


:

I use this Row Source for a report chart:
SELECT (Format([DateReceived],"mmm"" '""yy")) AS DateReceived1,
Sum(qryCompsFacsMonths.TotalComps) AS SumOfTotalComps1
FROM qryCompsFacsMonths
WHERE (((qryCompsFacsMonths.DateReceived) Between
DateSerial(Year(Date()),Month(Date())-11,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);

This works well and I'm trying to get another chart to do the same however
it's not behaving. This is its Row Source:
SELECT (Year([ProdDate1])*12+Month([ProdDate1])-1) AS Expr1,
qryCompsFacsPerMonthSum100M.Per100MSum
FROM qryCompsFacsPerMonthSum100M
WHERE (((qryCompsFacsPerMonthSum100M.ProdDate1) Between
DateSerial(Year(Date()),Month(Date())-11,1) And
DateSerial(Year(Date()),Month(Date())+1,0)))
GROUP BY (Year([ProdDate1])*12+Month([ProdDate1])-1),
qryCompsFacsPerMonthSum100M.Per100MSum
ORDER BY (Year([ProdDate1])*12+Month([ProdDate1])-1);

This returns no records. I suspect that there's a clash with [ProdDate1]...?

Thanks for your help!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

format date 7
Current Month Last 5
date serial 8
Query By Month and Year - Access 2007 2
Query by Month and Year (Repost) 2
Error 3205 - Too Many Crosstab Column Headers (300) 2
Year to Date subquery 7
Querry by Quarter 7

Top