Group by Quarters Using Characters

Hello all,

I have a report based on a table that contains various dates throughout a
year. I wish the report to group by the monthly quarters 1 -4. This I have
managed with the statement below:
Quarter: "Q" & CStr(DatePart("q", [DateOfBirth]))

However, the reports groups on the heading Q1, Q2 etc. Is it possble to
show the month characters as in Jan-Mar, Apr-Jun instead of the Q1, Q2.
Thanks for taking the time to read this. All help and guidence welcome :-)

Debbie D. (UK)

probably the easiest way would be to use a SQL statement as the RecordSource
for your report, as

SELECT *, DatePart("q",[DateOfBirth]) AS Qtr FROM TableName;

substitute the correct table name, of course. if your report is bound to a
query, rather than to a table, then instead of changing the report's
RecordSource, simply add the calculated field into the existing query, as

Qtr: DatePart("q",[DateOfBirth])

*group* the report on the Qtr field, so the quarters will be in numeric
order. in the group Header section, add an unbound text box with its'
ControlSource set to the following expression, as

=Choose([Qtr],"Jan - Mar","Apr - Jun","Jul - Sep","Oct - Dec")

hth


"Debbie D." wrote in message
...
Hello all,

I have a report based on a table that contains various dates throughout a
year. I wish the report to group by the monthly quarters 1 -4. This I
have
managed with the statement below:
Quarter: "Q" & CStr(DatePart("q", [DateOfBirth]))

However, the reports groups on the heading Q1, Q2 etc. Is it possble to
show the month characters as in Jan-Mar, Apr-Jun instead of the Q1, Q2.
Thanks for taking the time to read this. All help and guidence welcome
:-)

Debbie D. (UK)

Debbie D. wrote:
I have a report based on a table that contains various dates throughout a
year. I wish the report to group by the monthly quarters 1 -4. This I have
managed with the statement below:
Quarter: "Q" & CStr(DatePart("q", [DateOfBirth]))

However, the reports groups on the heading Q1, Q2 etc. Is it possble to
show the month characters as in Jan-Mar, Apr-Jun instead of the Q1, Q2.


Use a text box expression like:

=MonthName(3*(DatePart("q",Date+99)-1)+1,True) & "-" &
MonthName(3*DatePart("q",Date),True)

--
Marsh
MVP [MS Access]

Marshall, you have my many thanks. Debbie D.

"Marshall Barton" wrote:

Debbie D. wrote:
I have a report based on a table that contains various dates throughout a
year. I wish the report to group by the monthly quarters 1 -4. This I have
managed with the statement below:
Quarter: "Q" & CStr(DatePart("q", [DateOfBirth]))

However, the reports groups on the heading Q1, Q2 etc. Is it possble to
show the month characters as in Jan-Mar, Apr-Jun instead of the Q1, Q2.


Use a text box expression like:

=MonthName(3*(DatePart("q",Date+99)-1)+1,True) & "-" &
MonthName(3*DatePart("q",Date),True)

--
Marsh
MVP [MS Access]

Thank you Tina for your reply. Superstar!!

"tina" wrote:

probably the easiest way would be to use a SQL statement as the RecordSource
for your report, as

SELECT *, DatePart("q",[DateOfBirth]) AS Qtr FROM TableName;

substitute the correct table name, of course. if your report is bound to a
query, rather than to a table, then instead of changing the report's
RecordSource, simply add the calculated field into the existing query, as

Qtr: DatePart("q",[DateOfBirth])

*group* the report on the Qtr field, so the quarters will be in numeric
order. in the group Header section, add an unbound text box with its'
ControlSource set to the following expression, as

=Choose([Qtr],"Jan - Mar","Apr - Jun","Jul - Sep","Oct - Dec")

hth


"Debbie D." wrote in message
...
Hello all,

I have a report based on a table that contains various dates throughout a
year. I wish the report to group by the monthly quarters 1 -4. This I
have
managed with the statement below:
Quarter: "Q" & CStr(DatePart("q", [DateOfBirth]))

However, the reports groups on the heading Q1, Q2 etc. Is it possble to
show the month characters as in Jan-Mar, Apr-Jun instead of the Q1, Q2.
Thanks for taking the time to read this. All help and guidence welcome
:-)

Debbie D. (UK)

you're welcome


"Debbie D." wrote in message
...
Thank you Tina for your reply. Superstar!!

"tina" wrote:

probably the easiest way would be to use a SQL statement as the
RecordSource
for your report, as

SELECT *, DatePart("q",[DateOfBirth]) AS Qtr FROM TableName;

substitute the correct table name, of course. if your report is bound to
a
query, rather than to a table, then instead of changing the report's
RecordSource, simply add the calculated field into the existing query,
as

Qtr: DatePart("q",[DateOfBirth])

*group* the report on the Qtr field, so the quarters will be in numeric
order. in the group Header section, add an unbound text box with its'
ControlSource set to the following expression, as

=Choose([Qtr],"Jan - Mar","Apr - Jun","Jul - Sep","Oct - Dec")

hth


"Debbie D." wrote in message
...
Hello all,

I have a report based on a table that contains various dates
throughout a
year. I wish the report to group by the monthly quarters 1 -4. This
I
have
managed with the statement below:
Quarter: "Q" & CStr(DatePart("q", [DateOfBirth]))

However, the reports groups on the heading Q1, Q2 etc. Is it possble
to
show the month characters as in Jan-Mar, Apr-Jun instead of the Q1,
Q2.
Thanks for taking the time to read this. All help and guidence
welcome
:-)

Debbie D. (UK)