Financial Year quarter

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sebastian stephenson - 06 Jul 2006 10:40 GMT

I would like to extract the financial year quarter from a Date.

I am using the DatePart("q",[date]) method on the field.

When I have a date = 01.04.05 the function returns = 2. But in reality the
financial quarter is = 1.

Is their a way I can adapt the datePart() method, OR is their a specific
method to produce this result.

Any help will be greatly appreciated. I have to produce a high level
financial report by lunch time today :o)

Signature

Learning SQL and Access

Reply to this Message

John Spencer - 06 Jul 2006 12:47 GMT

It could be confusion on the part of Access. Date is a reserved word that
means return the system date.

Is the field a DateTime Field? If not, then Access is probably incorrectly
interpreting the value.

Also, does 01.04.05 mean January 4 2005 or does it mean April 1, 2005? If
the second, then this is quarter 2 of the calendar year. If your fiscal
year calendar starts on a different date than January 1, you will have to
adjust the date by adding or subtracting months (or days) using the DateAdd
function, prior to calculating the quarter.

Try
DatePart("q",[yourtablename].[Date])

If you are storing the date value as a string in the format you show and
01.04.05 means January 4 2005, then try the following
DatePart("q",DateSerial(Right([Date],2),Left([Date],2),1 ))

If April is the first month of the quarter subtract 1 quarter from the
[Date] and then calculate the quarter number.

DatePart("q",DateAdd("q",-1,[Date]))

>I would like to extract the financial year quarter from a Date.
>
[quoted text clipped - 9 lines]
> Any help will be greatly appreciated. I have to produce a high level
> financial report by lunch time today :o)

Reply to this Message