Default Quaters

[Gmoz]

I need to change the default quarter ( 1=Jan-Mar Etc...) To (1= Oct-Dec Etc...). I would like to put it in one place for the whole DataBase because I use it for alot of reports and queries. Any help is appreciated.
Frank

 

[ritchieroo]

If you want to map the values 1, 2, 3, 4 ==> 2, 3, 4, 1 respectively, you can do this mathematically:

y = (x Mod 4) + 1

(1 Mod 4) + 1 = 2
(2 Mod 4) + 1 = 3
(3 Mod 4) + 1 = 4
(4 Mod 4) + 1 = 1

This avoids using if/case logic and produces a faster function:

Public Function FinancialQuarter(SourceDate as Date) as Integer
  FinancialQuarter = (DatePart("q", SourceDate) Mod 4) +1
End Function

Note: copy function into a module and save. You can then use this function in other VBA and queries as needed.


PS. The formula presented is a generalized form of

y = ((x + n) Mod 4) + 1... where n = 0

Specifying values of 1 and 2 for n will give you

1,2,3,4 ==> 3,4,1,2

and

1,2,3,4 ==> 4,1,2,3

respectively