Group by Quarters Using Characters

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

 

[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

 

[Marshall Barton]

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)

 

[Guest]

Marshall, you have my many thanks. Debbie D.

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)

 

[Guest]

Thank you Tina for your reply. Superstar!!

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

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)

 

[tina]

you're welcome

Thank you Tina for your reply. Superstar!!

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

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)