Quarterly query

[msmuzila]

for queries, what is the quarterly date format. I have month and year:

Month([SHIP_DATE])
Year([SHIP_DATE])

What is quarterly?

 

[Rob Parker]

There is no standard "quarter" in Access. If you want one, you'll have to
write your own custom function to provide it - and that will also allow you
to define exactly what you want it to be.

Rob

 

[Peter Danes]

The SWITCH function might do what you want. Something like (air code):

Quarter:
Switch(Month([SHIP_DATE]),1,Month([SHIP_DATE]),1,Month([SHIP_DATE]),1,
Month([SHIP_DATE]),2,Month([SHIP_DATE]),2,Month([SHIP_DATE]),2,
Month([SHIP_DATE]),3,Month([SHIP_DATE]),3,Month([SHIP_DATE]),3,
Month([SHIP_DATE]),4,Month([SHIP_DATE]),4,Month([SHIP_DATE]),4))

This should give you 1 for months Jan, Feb, Mar, 2 for Apr, May Jun, etc.

Another way might be:

Quarter: INT(Month([SHIP_DATE])/4)+1


Pete

There is no standard "quarter" in Access. If you want one, you'll have to
write your own custom function to provide it - and that will also allow you
to define exactly what you want it to be.

Rob

for queries, what is the quarterly date format. I have month and year:

Month([SHIP_DATE])
Year([SHIP_DATE])

What is quarterly?

 

[msmuzila]

Quarter: INT(Month([SHIP_DATE])/4)+1 works great, just had to put a >0
in the criteria

thanks

 

[Guest]

Here's an additional method:

DatePart("q", [SHIP_DATE])

-Michael

 

[Rob Parker]

Hi Michael,

Thanks for that post - I wasn't aware of that format within the DatePart
function.

However, it might still make sense for the OP to consider writing his own
function. That one gives 1 for Jan to Mar, 2 for Apr to Jun, etc. This may
not be appropriate; eg. here in Oz, financial year reporting considers Jul
to Sep as quarter 1, etc. And, IIRC, the UK financial year start in April.
Or, a custom function would allow you to generate a "rolling" quarter based
on the current month.

Rob

Here's an additional method:

DatePart("q", [SHIP_DATE])

-Michael

Quarter: INT(Month([SHIP_DATE])/4)+1 works great, just had to put a >0
in the criteria

thanks

 

[Peter Danes]

Glad it helped.

Pete

 

[lkk]

Quarter: INT(Month([SHIP_DATE])/4)+1 works great, just had to put a >0
in the criteria

thanks