Query for Fiscal Year and Month

[Heidestrand]

Hello,

I want to build a query that calculates the fiscal year and the month from a date on-the-fly. I tried to do it in VBA, but it's more complicated than in query I guess. So this is my table:

The date is on the left, in the middle I want to have calculated Jan 14 and on the right I want 14/15.

In VBA I started with this: strMonth = MonthName(Month(A), True) & " " & Format(A, "YY") which gave me "Jan 08". But the date was hard-coded and not from my table. Then I tried to store the information by the help of a SQL statement into a string. But this also didn't work, the types were different.

Then I stumbled upon this Microsoft page but the code also wasn't really helping me. I tried to alter it to this code:

UPDATE tblSAPOD 
SET sapOD_month = Year([sapOD_OrderDate])-IIf([sapOD_OrderDate]< 
               DateSerial(Year([sapOD_OrderDate]),6,16),1,0)
WHERE sapOD_OrderDate Like "*/*/94";

.. but I guess it's wrong, it gives me a syntax error.

So it would be really great if you can help me with this


Thanks in advance,

Heide

 

[CJ_London]

when does your fiscal year start?

 

[namliam]

On top of which you are trying to store information into your table that you want to calculate???

FY stuff is easily calculated, why store it in a table?

 

[Heidestrand]

@CJ_London:

My fiscal year 14/15 starts at October 1st, 2014 and ends the following year September 30, 2015. And it should be future-oriented

@namliam:

I'm building a database for my boss and I'm rebuilding an actual order by the help of the table.. so he wants me to do it It's part of my database design that I split up different kinds of information and store them in its own tables. And the FY and month are part of my time table.
At the end I want to export by "big" table to Excel for further calculations.

 

[CJ_London]

I want to build a query that calculates the fiscal year and the month from a date on-the-fly

Something like

 SELECT orderDate, month(orderdate) as orderMonth, format(dateadd("yyyy",month(orderDate)<10,orderdate),"yy") & "/" & format(dateadd("yyyy",abs(month(orderDate)>=10),orderdate),"yy")  AS FiscalYear
 FROM sapOD

 

[plog]

You should build a custom functions in Access to determine the FY. You would pass it a date and it would return the fiscal year that date is in. Then whenever you need to calculate the fiscal year, you just pass that function a date and it does the math for you.

 

[Heidestrand]

Thanks a lot, CJ_London for the code. I could have never made this by myself, it looks complicated. The FY looks great, but I'd like to make some adjustments to the month.

I know that in VBA the function MonthName(Month(A), True) outputs Jan. So I need this and the year like 15. Is this also possible somehow?

@plog:

That's a good idea, but I have many dates in my table and in my situation it's better to have them calculated and stored rather than typing it in..

 

[CJ_London]

you can use monthname or format(orderDate,"mmmm")

not sure what you mean by 'So I need this and the year like 15'

fiscal year calc will produce '14/15' - if you just want the 15, just use the appropriate part of the calculation

 

[Heidestrand]

Ok perfect, I got it now I had no idea about this "mmmm" but after playing around with it I only need 3 m's So my final code is:

SELECT sapOD_orderDate, Format(sapOD_orderDate, "mmm")&" "&format(dateadd("yyyy",abs(month(sapOD_orderDate)>=10),sapOD_orderDate),"yy") as sapOD_month
FROM tblSAPOD;

.. and this gives me Jan 15. Suuuper cool

Thank you all a lot!

 

[CJ_London]

As plog suggests, you could put the calc into a public function so you can call it from a query or vba

 

[plog]

many dates in my table and in my situation it's better to have them calculated and stored rather than typing it in.

You've created a bad situation then. Calculated values shouldn't be stored, but calculated.

 

[namliam]

Calculated values shouldn't be stored, but calculated.

Unless for some reason you choose to use a Dimensions table.