show begining and ending month of a quarter

[Guest]

I have a date grouping level on a Access form that groups on the quarter and
shows the date with: =Format$([DATE],"mmmm yyyy",0,0) . If there is a date
that falls within that quarter in Oct, Jan, Apr, or Jul it shows the quarter
starting in that month which is ok. If the dates in the field are from any
other month the date will show that month.

I would like a format that will show the starting and ending months of a
quarter and group any dates that fall within those months.

I tried just showing the quarter number but I am working in fiscal years and
it shows Oct through Dec as the 4th quarter instead of the 1st quarter so
that will not work for me.

Thanks for any help you can give with this issue.

 

[Allen Browne]

Presumably this is for a report?

If so, you can group by the quarter just by using the Sorting And Grouping
dialog of the report. (It's on the View menu in report design.)

Choose your date field in the dialog, and then in the lower pane, group on
Qtr.

If you need to do it for a form, you could create a query and type 2
calculated fields into fresh columns of the query design grid:
TheYear: Year([MyDate])
TheQuarter: DatePart("q", [MyDate])
replacing "MyDate" with the name of your date field.

 

[Guest]

Thank you Allen.

Yes this is on a report not a form. I have set the grouping property to
group by quarter and it groups them as I need. However when I use the
"DatePart("q", [MyDate])" format it shows the October through December
quarter as the 4th quarter. I am working in fiscal years makeing that the
1st quarter.

I am trying to find a format for the date field that will show each quarter
as "Oct-Dec 200_", "Jan-Apr 200_" and so on. Not nessarily that exact format
but showing the range of months in the quarter instead of just the number of
the quarter in question.

Thank you again for your reply to my question.

Presumably this is for a report?

If so, you can group by the quarter just by using the Sorting And Grouping
dialog of the report. (It's on the View menu in report design.)

Choose your date field in the dialog, and then in the lower pane, group on
Qtr.

If you need to do it for a form, you could create a query and type 2
calculated fields into fresh columns of the query design grid:
TheYear: Year([MyDate])
TheQuarter: DatePart("q", [MyDate])
replacing "MyDate" with the name of your date field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a date grouping level on a Access form that groups on the quarter
and
shows the date with: =Format$([DATE],"mmmm yyyy",0,0) . If there is a
date
that falls within that quarter in Oct, Jan, Apr, or Jul it shows the
quarter
starting in that month which is ok. If the dates in the field are from
any
other month the date will show that month.

I would like a format that will show the starting and ending months of a
quarter and group any dates that fall within those months.

I tried just showing the quarter number but I am working in fiscal years
and
it shows Oct through Dec as the 4th quarter instead of the 1st quarter so
that will not work for me.

Thanks for any help you can give with this issue.

 

[Allen Browne]

Okay so you have the report grouping correctly, and you just want to show
that information in a header on the report.

You could get the first month of the quarter with a text box bound to:
=DateSerial(Year([MyDate]), 3 * (DatePart("q", [MyDate]) - 1) + 1, 1)
It that is Text1, then the last month of the quarter is:
=DateAdd("m", 2, [Text1])
so the expression you are looking for would be:
=Format([Text1], "mmm\-") & Format(DateAdd("m", 2, [Text1]), "mmm yyyy")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you Allen.

Yes this is on a report not a form. I have set the grouping property to
group by quarter and it groups them as I need. However when I use the
"DatePart("q", [MyDate])" format it shows the October through December
quarter as the 4th quarter. I am working in fiscal years makeing that the
1st quarter.

I am trying to find a format for the date field that will show each
quarter
as "Oct-Dec 200_", "Jan-Apr 200_" and so on. Not nessarily that exact
format
but showing the range of months in the quarter instead of just the number
of
the quarter in question.

Thank you again for your reply to my question.

Presumably this is for a report?

If so, you can group by the quarter just by using the Sorting And
Grouping
dialog of the report. (It's on the View menu in report design.)

Choose your date field in the dialog, and then in the lower pane, group
on
Qtr.

If you need to do it for a form, you could create a query and type 2
calculated fields into fresh columns of the query design grid:
TheYear: Year([MyDate])
TheQuarter: DatePart("q", [MyDate])
replacing "MyDate" with the name of your date field.

I have a date grouping level on a Access form that groups on the quarter
and
shows the date with: =Format$([DATE],"mmmm yyyy",0,0) . If there is a
date
that falls within that quarter in Oct, Jan, Apr, or Jul it shows the
quarter
starting in that month which is ok. If the dates in the field are from
any
other month the date will show that month.

I would like a format that will show the starting and ending months of
a
quarter and group any dates that fall within those months.

I tried just showing the quarter number but I am working in fiscal
years
and
it shows Oct through Dec as the 4th quarter instead of the 1st quarter
so
that will not work for me.

Thanks for any help you can give with this issue.

 

[Guest]

Again Allen I thank you very much.

Now here is the problem. With that expression I get the range I am looking
for. One of the dates that uses that expression is in June and that is the
first date for that quarter (Apr-Jun). With that expression the result
returned was Jun-Aug 2005.

Thank you for all the help so far and I can feel that this is getting
narrowed down.

Okay so you have the report grouping correctly, and you just want to show
that information in a header on the report.

You could get the first month of the quarter with a text box bound to:
=DateSerial(Year([MyDate]), 3 * (DatePart("q", [MyDate]) - 1) + 1, 1)
It that is Text1, then the last month of the quarter is:
=DateAdd("m", 2, [Text1])
so the expression you are looking for would be:
=Format([Text1], "mmm\-") & Format(DateAdd("m", 2, [Text1]), "mmm yyyy")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you Allen.

Yes this is on a report not a form. I have set the grouping property to
group by quarter and it groups them as I need. However when I use the
"DatePart("q", [MyDate])" format it shows the October through December
quarter as the 4th quarter. I am working in fiscal years makeing that the
1st quarter.

I am trying to find a format for the date field that will show each
quarter
as "Oct-Dec 200_", "Jan-Apr 200_" and so on. Not nessarily that exact
format
but showing the range of months in the quarter instead of just the number
of
the quarter in question.

Thank you again for your reply to my question.

Presumably this is for a report?

If so, you can group by the quarter just by using the Sorting And
Grouping
dialog of the report. (It's on the View menu in report design.)

Choose your date field in the dialog, and then in the lower pane, group
on
Qtr.

If you need to do it for a form, you could create a query and type 2
calculated fields into fresh columns of the query design grid:
TheYear: Year([MyDate])
TheQuarter: DatePart("q", [MyDate])
replacing "MyDate" with the name of your date field.

I have a date grouping level on a Access form that groups on the quarter
and
shows the date with: =Format$([DATE],"mmmm yyyy",0,0) . If there is a
date
that falls within that quarter in Oct, Jan, Apr, or Jul it shows the
quarter
starting in that month which is ok. If the dates in the field are from
any
other month the date will show that month.

I would like a format that will show the starting and ending months of
a
quarter and group any dates that fall within those months.

I tried just showing the quarter number but I am working in fiscal
years
and
it shows Oct through Dec as the 4th quarter instead of the 1st quarter
so
that will not work for me.

Thanks for any help you can give with this issue.

 

[Allen Browne]

Okay, you can probabably pull the expression apart, and use it as an example
to build up the one that gives you the answer you need.

Use the Immediate window (Ctrl+G).
Work a bit at a time until you get the month part right, e.g.:
? (3 * (DatePart("q", #6/1/2005#) - 1)) + 1

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Again Allen I thank you very much.

Now here is the problem. With that expression I get the range I am
looking
for. One of the dates that uses that expression is in June and that is
the
first date for that quarter (Apr-Jun). With that expression the result
returned was Jun-Aug 2005.

Thank you for all the help so far and I can feel that this is getting
narrowed down.

Okay so you have the report grouping correctly, and you just want to show
that information in a header on the report.

You could get the first month of the quarter with a text box bound to:
=DateSerial(Year([MyDate]), 3 * (DatePart("q", [MyDate]) - 1) + 1, 1)
It that is Text1, then the last month of the quarter is:
=DateAdd("m", 2, [Text1])
so the expression you are looking for would be:
=Format([Text1], "mmm\-") & Format(DateAdd("m", 2, [Text1]), "mmm
yyyy")

Thank you Allen.

Yes this is on a report not a form. I have set the grouping property
to
group by quarter and it groups them as I need. However when I use the
"DatePart("q", [MyDate])" format it shows the October through December
quarter as the 4th quarter. I am working in fiscal years makeing that
the
1st quarter.

I am trying to find a format for the date field that will show each
quarter
as "Oct-Dec 200_", "Jan-Apr 200_" and so on. Not nessarily that exact
format
but showing the range of months in the quarter instead of just the
number
of
the quarter in question.

Thank you again for your reply to my question.

:

Presumably this is for a report?

If so, you can group by the quarter just by using the Sorting And
Grouping
dialog of the report. (It's on the View menu in report design.)

Choose your date field in the dialog, and then in the lower pane,
group
on
Qtr.

If you need to do it for a form, you could create a query and type 2
calculated fields into fresh columns of the query design grid:
TheYear: Year([MyDate])
TheQuarter: DatePart("q", [MyDate])
replacing "MyDate" with the name of your date field.

I have a date grouping level on a Access form that groups on the
quarter
and
shows the date with: =Format$([DATE],"mmmm yyyy",0,0) . If there
is a
date
that falls within that quarter in Oct, Jan, Apr, or Jul it shows the
quarter
starting in that month which is ok. If the dates in the field are
from
any
other month the date will show that month.

I would like a format that will show the starting and ending months
of
a
quarter and group any dates that fall within those months.

I tried just showing the quarter number but I am working in fiscal
years
and
it shows Oct through Dec as the 4th quarter instead of the 1st
quarter
so
that will not work for me.

Thanks for any help you can give with this issue.