format date

J

JohnLute

(Format([DateReceived],"mmm"" '""yy")) appears as "Oct '07" as it pertains to
this SQL:

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

How can I get it to display "Oct-07"? I tried "mmm-yy" which works as a
query however it doesn't work as the Row Source for a bar graph chart. The
chart displays Jan-07 on the left and increases chronologically up to Dec07.

Thanks for your help!
 
D

Duane Hookom

Why doesn't it work? What are your results? I would think that displaying Jan
to Dec from left to right is the way a chart should display.
 
J

JohnLute

Hi, Duane.

It Displays as it should: "Oct '06" with the current month last preceded by
the previous 11 months.

To be consistent with other charts on the same report I'd like it to display
"Oct-06" however when I change to this: "mmm-yy" the chart displays "Oct-07"
but this clearly affects the ORDER BY clause.

--
www.Marzetti.com


Duane Hookom said:
Why doesn't it work? What are your results? I would think that displaying Jan
to Dec from left to right is the way a chart should display.

--
Duane Hookom
Microsoft Access MVP


JohnLute said:
(Format([DateReceived],"mmm"" '""yy")) appears as "Oct '07" as it pertains to
this SQL:

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

How can I get it to display "Oct-07"? I tried "mmm-yy" which works as a
query however it doesn't work as the Row Source for a bar graph chart. The
chart displays Jan-07 on the left and increases chronologically up to Dec07.

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

Duane Hookom

Doesn't this set your order for the chart:
ORDER BY (Year([DateReceived])*12+Month([DateReceived])-1);
--
Duane Hookom
Microsoft Access MVP


JohnLute said:
Hi, Duane.

It Displays as it should: "Oct '06" with the current month last preceded by
the previous 11 months.

To be consistent with other charts on the same report I'd like it to display
"Oct-06" however when I change to this: "mmm-yy" the chart displays "Oct-07"
but this clearly affects the ORDER BY clause.

--
www.Marzetti.com


Duane Hookom said:
Why doesn't it work? What are your results? I would think that displaying Jan
to Dec from left to right is the way a chart should display.

--
Duane Hookom
Microsoft Access MVP


JohnLute said:
(Format([DateReceived],"mmm"" '""yy")) appears as "Oct '07" as it pertains to
this SQL:

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

How can I get it to display "Oct-07"? I tried "mmm-yy" which works as a
query however it doesn't work as the Row Source for a bar graph chart. The
chart displays Jan-07 on the left and increases chronologically up to Dec07.

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

JohnLute

Yes but it only works when the format "mmm"" '""yy" is used. When I try
"mmm-yy" it doesn't. I can't understand why that format makes any difference.

--
www.Marzetti.com


Duane Hookom said:
Doesn't this set your order for the chart:
ORDER BY (Year([DateReceived])*12+Month([DateReceived])-1);
--
Duane Hookom
Microsoft Access MVP


JohnLute said:
Hi, Duane.

It Displays as it should: "Oct '06" with the current month last preceded by
the previous 11 months.

To be consistent with other charts on the same report I'd like it to display
"Oct-06" however when I change to this: "mmm-yy" the chart displays "Oct-07"
but this clearly affects the ORDER BY clause.

--
www.Marzetti.com


Duane Hookom said:
Why doesn't it work? What are your results? I would think that displaying Jan
to Dec from left to right is the way a chart should display.

--
Duane Hookom
Microsoft Access MVP


:

(Format([DateReceived],"mmm"" '""yy")) appears as "Oct '07" as it pertains to
this SQL:

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

How can I get it to display "Oct-07"? I tried "mmm-yy" which works as a
query however it doesn't work as the Row Source for a bar graph chart. The
chart displays Jan-07 on the left and increases chronologically up to Dec07.

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

Duane Hookom

Change the Row Source property to:

SELECT Format([DateReceived],"mmm") & "-" &
Format(Year([DateReceived]),"00") 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") & "-" &
Format(Year([DateReceived]),"00"), (Format([DateReceived],"mmm-yy")),
Year([DateReceived])*12+Month([DateReceived])-1
ORDER BY (Year([DateReceived])*12+Month([DateReceived])-1);

--
Duane Hookom
Microsoft Access MVP


JohnLute said:
Yes but it only works when the format "mmm"" '""yy" is used. When I try
"mmm-yy" it doesn't. I can't understand why that format makes any difference.

--
www.Marzetti.com


Duane Hookom said:
Doesn't this set your order for the chart:
ORDER BY (Year([DateReceived])*12+Month([DateReceived])-1);
--
Duane Hookom
Microsoft Access MVP


JohnLute said:
Hi, Duane.

It Displays as it should: "Oct '06" with the current month last preceded by
the previous 11 months.

To be consistent with other charts on the same report I'd like it to display
"Oct-06" however when I change to this: "mmm-yy" the chart displays "Oct-07"
but this clearly affects the ORDER BY clause.

--
www.Marzetti.com


:

Why doesn't it work? What are your results? I would think that displaying Jan
to Dec from left to right is the way a chart should display.

--
Duane Hookom
Microsoft Access MVP


:

(Format([DateReceived],"mmm"" '""yy")) appears as "Oct '07" as it pertains to
this SQL:

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

How can I get it to display "Oct-07"? I tried "mmm-yy" which works as a
query however it doesn't work as the Row Source for a bar graph chart. The
chart displays Jan-07 on the left and increases chronologically up to Dec07.

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

JohnLute

BRILLIANT!

That does the trick. I never would've figured out that one. You're da man!

--
www.Marzetti.com


Duane Hookom said:
Change the Row Source property to:

SELECT Format([DateReceived],"mmm") & "-" &
Format(Year([DateReceived]),"00") 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") & "-" &
Format(Year([DateReceived]),"00"), (Format([DateReceived],"mmm-yy")),
Year([DateReceived])*12+Month([DateReceived])-1
ORDER BY (Year([DateReceived])*12+Month([DateReceived])-1);

--
Duane Hookom
Microsoft Access MVP


JohnLute said:
Yes but it only works when the format "mmm"" '""yy" is used. When I try
"mmm-yy" it doesn't. I can't understand why that format makes any difference.

--
www.Marzetti.com


Duane Hookom said:
Doesn't this set your order for the chart:
ORDER BY (Year([DateReceived])*12+Month([DateReceived])-1);
--
Duane Hookom
Microsoft Access MVP


:

Hi, Duane.

It Displays as it should: "Oct '06" with the current month last preceded by
the previous 11 months.

To be consistent with other charts on the same report I'd like it to display
"Oct-06" however when I change to this: "mmm-yy" the chart displays "Oct-07"
but this clearly affects the ORDER BY clause.

--
www.Marzetti.com


:

Why doesn't it work? What are your results? I would think that displaying Jan
to Dec from left to right is the way a chart should display.

--
Duane Hookom
Microsoft Access MVP


:

(Format([DateReceived],"mmm"" '""yy")) appears as "Oct '07" as it pertains to
this SQL:

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

How can I get it to display "Oct-07"? I tried "mmm-yy" which works as a
query however it doesn't work as the Row Source for a bar graph chart. The
chart displays Jan-07 on the left and increases chronologically up to Dec07.

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

Duane Hookom

The reason my solution works as expected is because my formatted date string
is truly a string. I did some forcing of the conversion to text of the mmm-yy.

--
Duane Hookom
Microsoft Access MVP


JohnLute said:
BRILLIANT!

That does the trick. I never would've figured out that one. You're da man!

--
www.Marzetti.com


Duane Hookom said:
Change the Row Source property to:

SELECT Format([DateReceived],"mmm") & "-" &
Format(Year([DateReceived]),"00") 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") & "-" &
Format(Year([DateReceived]),"00"), (Format([DateReceived],"mmm-yy")),
Year([DateReceived])*12+Month([DateReceived])-1
ORDER BY (Year([DateReceived])*12+Month([DateReceived])-1);

--
Duane Hookom
Microsoft Access MVP


JohnLute said:
Yes but it only works when the format "mmm"" '""yy" is used. When I try
"mmm-yy" it doesn't. I can't understand why that format makes any difference.

--
www.Marzetti.com


:

Doesn't this set your order for the chart:
ORDER BY (Year([DateReceived])*12+Month([DateReceived])-1);
--
Duane Hookom
Microsoft Access MVP


:

Hi, Duane.

It Displays as it should: "Oct '06" with the current month last preceded by
the previous 11 months.

To be consistent with other charts on the same report I'd like it to display
"Oct-06" however when I change to this: "mmm-yy" the chart displays "Oct-07"
but this clearly affects the ORDER BY clause.

--
www.Marzetti.com


:

Why doesn't it work? What are your results? I would think that displaying Jan
to Dec from left to right is the way a chart should display.

--
Duane Hookom
Microsoft Access MVP


:

(Format([DateReceived],"mmm"" '""yy")) appears as "Oct '07" as it pertains to
this SQL:

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

How can I get it to display "Oct-07"? I tried "mmm-yy" which works as a
query however it doesn't work as the Row Source for a bar graph chart. The
chart displays Jan-07 on the left and increases chronologically up to Dec07.

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

current month / year WHERE 8
Current Month Last 5
date serial 8
Sort Formatted Date 1
column headings in CrossTab Qry? 2
Week starts on Friday 3
Creating query without using Wizard 6
Graph displaying dates alphabetically on x axis, not by date 1

Top