Show Year with Quarter

[Mordred]

Hi all,



I have a problem that is also posted here regarding showing the year that corresponds to the quarter being queried. I use the following statement (is that the right terminology?) to call the quarterly data:

Quarters: IIf(Month([Datev]) In (1,2,3),"Q1",IIf(Month([Datev]) In (4,5,6),"Q2",IIf(Month([Datev]) In (7,8,9),"Q3","Q4"))),

along with

Between #1/1/2007# And #12/1/2007#

(not seen). How can I make the output of the query look like: Q1-07,Q2-07, and etc..? Any help is always appreciated!

Regards:

[orange]

I'm not clear on what exactly you are doing/wanting to achieve, but

format(Date,"YYYY-Q") where Date is today's date will give this output
2011-2

[Mordred]

Hi orange, thanks for the quick reply. I am relatively new to Access and thanks to my job I will be working with it a lot more. In saying that, I don't yet know where I would put "format(Date,"YYYY-Q"). I tried it in the properties window > Format, but that's not right because it does this: (d"ate,YYYY-Q)" after I run the code.

Again, sorry to be daft about this but, I am!

[orange]

Perhaps you could try this in your query

Qtr: format(Datev,"YYYY-Q")

It may be useful, or at least something you can work with/from.

Datev is a date field that you're interested in, correct?

[Mordred]

Perhaps you could try this in your query

Qtr: format(Datev,"YYYY-Q")

Where would I put that, in the field where I already have the statement from my first post? Or in the criteria? I tried the criteria with that and format(Date,"YYYY-Q") but the query outputted blank records.

Datev is a date field that you're interested in, correct?

Yes, that is where all of my monthly data is.

[orange]

Try this

Qtr: format(Datev,"YYYY-Q")

[Mordred]

Thanks orange but that comes back with the months and the year. I am querying monthly data to make quarterly averages, that's why I have this:

Quarters: IIf(Month([Datev]) In (1,2,3),"Q1",IIf(Month([Datev]) In (4,5,6),"Q2",IIf(Month([Datev]) In (7,8,9),"Q3","Q4")))

When I get rid of that for yours it queries only the months.

[orange]

This expression
Qtr: format(Datev,"YYYY-Q")
will return the 4 digit year and the quarter for a given date Datev.

If Datev is Jun 29, 2011, then the expression will return 2011-2.

If Datev is Aug 15, 2023, then the expression will return 2023-3.

[Mordred]

Alright Orange, I see what you are saying. Thank you for that and the patience you have. Is there a way to make it display Q1-2011 instead of 2011-1?

[Mordred]

Thank you so much Orange for guiding my way! I played with the expression and finally did:
Qtr: "Q" & Format([Datev],"q-yyyy"). Now it displays exactly as I need it to. You rock!