Month of Quarter in a Query

[euphonium01]

I am having a problem trying to generate the Month of a given Quarter, I am attempting this within a Query, I have the following:



MonthOfQuarter: (Month([Order Date]) Mod 3)

This seems to work well for the first two months of each quarter, but always returns 0 (zero) for the third month. Can anyone help as to where I'm going wrong?

Thanks in advance

[Dal Jeanis]

Mathematically, that's what mod means. You should get what you want from

MonthOfQuarter: ( (Month([Order Date]) -1) Mod 3) + 1

or from

MonthOfQuarter: IIF( (Month([Order Date]) Mod 3 = 0), 3, Month([Order Date]) Mod 3)

Both of those formulae will give you (1, 2, 3) rather than (1, 2, 0).

[euphonium01]

Thanks so much, used the first option you sent me, worked first time

Very much appreciated

[Dal Jeanis]

No prob. Please mark the thread "solved". Top of page, under "Thread tools".