Fiscal Year Calculation

Posted on 2009-12-17
Last Modified: 2012-06-22
Hi experts,

I copied DatabaseMX's code as following from EE, used it in my database, it's so beautiful.
=DatePart("q",DateAdd("m",-3,[starttime])) & "-" & Year([starttime])-IIf(Month([starttime])>=3,0,1) & "/" & Year([starttime])-IIf(Month([starttime])>=3,0,1)+1
Now I'd like to have the year part only like 2009/2010, I'm assuming I took out the first part it would work, the code like the following:
=DatePart(Year([starttime])-IIf(Month([starttime])>=3,0,1) & "/" & Year([starttime])-IIf(Month([starttime])>=3,0,1)+1
But it says "The expression you entered is missing a closing parenthesis, bracket (]), or vertical bar (1).
Please advise.

Thank you!

Question by:howruaz9
    LVL 16

    Expert Comment

    just put format(xxx, "yyyy") around the original code (insert code in place of xxx)
    LVL 119

    Accepted Solution

    you don't need the function DatePart if you just want to get 2009/2010 format

    try this

    = Year([starttime]) - IIf(Month([starttime]) >= 3, 0, 1) & "/" & Year([starttime]) - IIf(Month([starttime]) >= 3, 0, 1) + 1

    Author Closing Comment

    Thank you, cap. What can I say, you are genius.
    I wish you a very happy holiday season!

    Author Comment

    Thank you, kmslogic and capricorn1. However cap's is working with no trouble. Wishing you all the best!!!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now