Financial Year quarter

G

Guest

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 :blush:)
 
J

John Spencer

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]))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top