IIF function- return specific values based on the month

Question

I'm trying to write a query that will populate the "Quarter" field with either Q1, Q2, Q3, Q4 depending on which month a certain transaction occurred in. I'm attempting it this way, which I think is rather clunky:

IIf([Original Date] like “7*” Or [original date] like “8*” or [original date] like “9*”, “Q1”, IIf ([Original Date] like “10*” or [Original Date] like “11*” or [Original Date] like “12*”, “Q2”, IIF([Original Date] like “1*” or [Original Date] like “2*” or [Original Date] like “3*”, “Q3”, IIf([Original Date] like “4*” or [Original Date] like “5*” or [Original Date] like “6*”, “Q4”, Null))))

This doesn't work, however. I was thinking maybe because the [Original Date] field is a Date/Time data type. Is there a better way to write this query?

Answer 1

Use DatePart("q"), if [Original Date] is a date.

(If not, use CDate())

SELECT "Q" & DatePart("q", [Original Date]) AS Quarter
FROM myTable

Oh wait, your Q1 starts in July. Then e.g. modify the date, adding 6 months:

SELECT "Q" & DatePart("q", DateAdd("m", 6, [Original Date])) AS Quarter
FROM myTable