Group by Quarters Using Characters

D

Debbie D.

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

tina

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
 
M

Marshall Barton

Debbie said:
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)
 
D

Debbie D.

Marshall, you have my many thanks. Debbie D.

Marshall Barton said:
Debbie said:
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)
 
D

Debbie D.

Thank you Tina for your reply. Superstar!!

tina said:
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. said:
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)
 
T

tina

you're welcome :)


Debbie D. said:
Thank you Tina for your reply. Superstar!!

tina said:
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. said:
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)
 

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

Top