reporting date in fiscal qtr's

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

Guest

How to report a date in fiscal qtr's, our fiscal year begins Nov 1 of the
year. How to covert a date to format like 2005-Q1, 2005-Q2 when using
crosstabs and reports?
I already use:
Qtr: Format(DateAdd("m",2[Table2].[Date]), "Qqyy")) Q105, Q205, etc would
like 2005-Q1, 2005-Q2, etc
 
Jim:

One approach would be to create a Quarter field in your query and then use
this field to build the format you desire.

For example:

IIF(Month(MyDate)<=3, 1, IIF(Month(MyDate)<=6, 2,
IIF(Month(MyDate)<=9,3,4))) as Quarter

Then for the Control Source you could use something like:

=Year([MyDate]) & "-Q" & [Quarter]

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


How to report a date in fiscal qtr's, our fiscal year begins Nov 1 of the
year. How to covert a date to format like 2005-Q1, 2005-Q2 when using
crosstabs and reports?
I already use:
Qtr: Format(DateAdd("m",2[Table2].[Date]), "Qqyy")) Q105, Q205, etc would
like 2005-Q1, 2005-Q2, etc
 
jim said:
How to report a date in fiscal qtr's, our fiscal year begins Nov 1 of the
year. How to covert a date to format like 2005-Q1, 2005-Q2 when using
crosstabs and reports?
I already use:
Qtr: Format(DateAdd("m",2[Table2].[Date]), "Qqyy")) Q105, Q205, etc
would
like 2005-Q1, 2005-Q2, etc
Click to expand...
 
David Lloyd said:
Jim:

One approach would be to create a Quarter field in your query and then use
this field to build the format you desire.

For example:

IIF(Month(MyDate)<=3, 1, IIF(Month(MyDate)<=6, 2,
IIF(Month(MyDate)<=9,3,4))) as Quarter

Then for the Control Source you could use something like:

=Year([MyDate]) & "-Q" & [Quarter]

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or
warranties.


How to report a date in fiscal qtr's, our fiscal year begins Nov 1 of the
year. How to covert a date to format like 2005-Q1, 2005-Q2 when using
crosstabs and reports?
I already use:
Qtr: Format(DateAdd("m",2[Table2].[Date]), "Qqyy")) Q105, Q205, etc
would
like 2005-Q1, 2005-Q2, etc
Click to expand...
 
adsl said:
jim said:
How to report a date in fiscal qtr's, our fiscal year begins Nov 1 of the
year. How to covert a date to format like 2005-Q1, 2005-Q2 when using
crosstabs and reports?
I already use:
Qtr: Format(DateAdd("m",2[Table2].[Date]), "Qqyy")) Q105, Q205, etc
would
like 2005-Q1, 2005-Q2, etc
Click to expand...
Click to expand...
 
adsl said:
David Lloyd said:
Jim:

One approach would be to create a Quarter field in your query and then
use
this field to build the format you desire.

For example:

IIF(Month(MyDate)<=3, 1, IIF(Month(MyDate)<=6, 2,
IIF(Month(MyDate)<=9,3,4))) as Quarter

Then for the Control Source you could use something like:

=Year([MyDate]) & "-Q" & [Quarter]

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or
warranties.


How to report a date in fiscal qtr's, our fiscal year begins Nov 1 of the
year. How to covert a date to format like 2005-Q1, 2005-Q2 when using
crosstabs and reports?
I already use:
Qtr: Format(DateAdd("m",2[Table2].[Date]), "Qqyy")) Q105, Q205, etc
would
like 2005-Q1, 2005-Q2, etc
Click to expand...
Click to expand...
 
David Lloyd said:
Jim:

One approach would be to create a Quarter field in your query and then use
this field to build the format you desire.

For example:

IIF(Month(MyDate)<=3, 1, IIF(Month(MyDate)<=6, 2,
IIF(Month(MyDate)<=9,3,4))) as Quarter

Then for the Control Source you could use something like:

=Year([MyDate]) & "-Q" & [Quarter]

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or
warranties.


How to report a date in fiscal qtr's, our fiscal year begins Nov 1 of the
year. How to covert a date to format like 2005-Q1, 2005-Q2 when using
crosstabs and reports?
I already use:
Qtr: Format(DateAdd("m",2[Table2].[Date]), "Qqyy")) Q105, Q205, etc
would
like 2005-Q1, 2005-Q2, etc
Click to expand...
 
jim said:
How to report a date in fiscal qtr's, our fiscal year begins Nov 1 of the
year. How to covert a date to format like 2005-Q1, 2005-Q2 when using
crosstabs and reports?
I already use:
Qtr: Format(DateAdd("m",2[Table2].[Date]), "Qqyy")) Q105, Q205, etc
would
like 2005-Q1, 2005-Q2, etc
Click to expand...
 
Back
Top