asked on

Microsoft, Access, 2003, DatePart calculations starting on a fiscal year

I would like to use the DatePart function to indicate which quarter a date resides within a given fiscal year. As a default, the function does this calculation on a calendar year. Assuming the fiscal year begins on 11/1, and [Date] is 12/5/2005, I would like DatePart("q", [Date]) to return 1. I've looked through the help and it doesn't seem like I have the option via the optional criteria to set up a year start date of 11/1. Any help is greatly appreciated.

Kind Regards,
Dusty

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Won't the 4th argument take care of that? 1st week begins on 11-1 ?

_______________________________________________________

DatePart(interval, date[,firstdayofweek[, firstweekofyear]])

The DatePart function syntax has these named arguments:

Part Description
interval Required. String expression that is the interval of time you want to return.
date Required. Variant (Date) value that you want to evaluate.
firstdayofweek Optional. A constant that specifies the first day of the week. If not specified, Sunday is assumed.
firstweekofyear Optional. A constant that specifies the first week of the year. If not specified, the first week is assumed to be the week in which January 1 occurs.

ASKER CERTIFIED SOLUTION

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dustywork

ASKER

Thanks. The examples offer a good solution for adjusting for the fiscal year, but how about accounting for the quarter? The statement below (assume 10/1 fiscal year start) would return zero when the month is in the first calendar year.

What do you think about using the Month function and a corresponding translation table, e.g.:

With fiscal year start of 11/1:
Month FiscalQuarter
1 1
2 2
3 2
4 2
5 3
6 3
7 3
8 4
9 4
10 4

Thoughts????

Thanks,
Dusty

dustywork

ASKER

Sorry I forgot to paste the statement from the link you posted:

select iif(datepart("q", [updated]) = 4, "1-" & year([updated]) +1, datepart("q", [updated])-1 & "-" & year([updated]) as FY

Thanks!!!

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

You can do that translation using the Choose function ... here is the info:

Quarter: Choose([Month],1,2,2,2,3,3,3,4,4,4,1,1) or similiar

Choose Function

Selects and returns a value from a list of arguments.

Syntax

Choose(index, choice-1[, choice-2, ... [, choice-n]])

The Choose function syntax has these parts:

Part Description
index Required. Numeric expression or field that results in a value between 1 and the number of available choices.
choice Required. Variant expression containing one of the possible choices.

Remarks

Choose returns a value from the list of choices based on the value of index. If index is 1, Choose returns the first choice in the list; if index is 2, it returns the second choice, and so on.

You can use Choose to look up a value in a list of possibilities. For example, if index evaluates to 3 and choice-1 = "one", choice-2 = "two", and choice-3 = "three", Choose returns "three". This capability is particularly useful if index represents the value in an option group.

Choose evaluates every choice in the list, even though it returns only one. For this reason, you should watch for undesirable side effects. For example, if you use the MsgBox function as part of an expression in all the choices, a message box will be displayed for each choice as it is evaluated, even though Choose returns the value of only one of them.

The Choose function returns a Null if index is less than 1 or greater than the number of choices listed.

If index is not a whole number, it is rounded to the nearest whole number before being evaluated.

Computer101

Forced accept.

Computer101
EE Admin