how to group the date as Year, Quarter and Month 1

[jefferyzhong]

can anyone tell me the code!

thanks in advance!

 

[BobStubbs]

Suppose your date field is called MyDate. List this field in a query.

You can now create calculated fields in the query, using the Format$ function to extract parts of the date field.

Run the query to see the results, e.g.

MyDate Month Quarter Year
01/01/2005 Jan 1 2005
02/02/2005 Feb 1 2005
04/04/2004 Apr 2 2004
etc

These calculated fields can then be used to create grouping levels in a report.

Code examples:

Expr1: Format$([MyDate],"mmm") - month as Jan, Feb etc
Expr2: Format$([MyDate],"q") - quarter number 1,2,3 or 4
Expr3: Format$([MyDate],"yyyy") - year as 2004, 2005 etc

You can change the default values 'Expr1:' etc. to be more meaningful, e.g.

Month: Format$([MyDate],"mmm")

VBA help files contain a complete list of the date formats which you can use with Format$.


Bob Stubbs