Group by Quarters Using Characters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 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.
Click to expand...


Use a text box expression like:

=MonthName(3*(DatePart("q",Date+99)-1)+1,True) & "-" &
MonthName(3*DatePart("q",Date),True)
 
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.
Click to expand...


Use a text box expression like:

=MonthName(3*(DatePart("q",Date+99)-1)+1,True) & "-" &
MonthName(3*DatePart("q",Date),True)
Click to expand...
 
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)
Click to expand...
Click to expand...
 
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
Click to expand...
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
Click to expand...
welcome
:-)
Debbie D. (UK)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top