how to generate date format "YYYY Q"

[Matt D Francis]

Hi, Any ideas how I can add an expression in a Quary to convert a date field
into a format that will give me YYYY Q for example

2004 Q4

I don't know how, and to add to the problem, my workplace defines Quarters
according to the financial rather than calender year so

01/04 - 30/06 = Q1
01/07 - 30/09 = Q1
01/10 - 31/12 = Q3
01/01 - 31/03 = Q4

Fields are in Short date/Time format, and we use UK date format dd/mm/yyyy

Hoping someone can help.

Matt

 

[Ofer]

Try this

Select format(DateField,"yyyy") & " Q" & Format(DateField, "q") as NewDate
From TableName

 

[Matt D Francis]

Quick reply, and it works! Thank-you. Looks so simple..

I also have to report using the financial year, as opposed to the calender
years so

01/04 - 30/06 = Q1
01/07 - 30/09 = Q2
01/10 - 31/12 = Q3
01/01 - 31/03 = Q4

Can that formula be adapted to cope with that?

Thanks again

Matt

 

[Ofer]

I'm not sure, but you can try and use that

Select format(DateField,"yyyy") & " Q" & iif(Format(DateField,
"q")-1=0,4,Format(DateField, "q")-1) as NewDate
From TableName
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

 

[John Spencer]

Use the format function to return the value you want

Format(SomeDate,"YYYY Q") or if you want the Q to show Format(SomeDate,"YYYY
\QQ")

Use the DateAdd function to adjust the quarters: Since I'm not sure how you
derive the FY number, you will have to adjust the following as appropriate
to get the correct year number
DateAdd("m",-9,SomeDate) or DateAdd("m",+3,SomeDate)

Putting that all together
Format(DateAdd("m",-9,SomeDate),YYYY \QQ")