Access VBA Get Quarter From Month Number

Question

I am stuck in trying to find the quarter of year number from the month number i.e. i have month number 2 which should be quarter number 1, how do i do this in Access VBA or an access query? Thanks in advance.

Heartburn

You could use Choose(MonthNum,1,1,1,2,2,2,3,3,3,4,4,4) – Gouda

@Gouda you should throw that up as an answer. The only thing it lacks is error/out of bounds handling. – Benz

Answer 1

You can use this function:

Public Function Quarter(ByVal MonthNumber As Long) As Long

        If (MonthNumber < 1) Or (MonthNumber > 12) Then
                Call Err.Raise(6) ' Value out of Bounds '
        End If

        Let Quarter = (MonthNumber - 1) \ 3 + 1

End Function

Hellion

Quarter = (Month(date()) + 2) \ 3 Slightly shorter. :) – Ardoin

Nice one. I should go back to fractions basics. :-) – Hellion

There was a MOD version that would roll whatever number you threw at it to be between 1 and 12. I can't find it and can't figure it out. :( – Ardoin

Proper syntax now is Dim Quarter = (Month(Date.Now) + 2) \ 3 – Idola

Answer 2

In Excel VBA I like using Choose as sometimes I need to get the financial quarter rather than the calendar quarter.

calendar_quarter = Choose(Month(Date), 1, 1, 1, 2, 2, 2, 3, 3, 3, 4, 4, 4)

' if financial year starts in April - this will get the financial quarter from the current date
financial_quarter = Choose(Month(Date), 4, 4, 4, 1, 1, 1, 2, 2, 2, 3, 3, 3)

Inae

Answer 3

One liner for Excel:

=INT((MonthNo-1)/3 +1)

for SQL

floor(([MonthNo]-1)/3) +1

Akkad

Answer 4

The format function will convert a date to the calendar quarter:

In string format: Quarter = Format((Date), "Q")

or use Val to convert to number format: Quarter = Val(Format((Date), "Q"))

Armipotent