Calendar Year to Fiscal Year

Question



I need to change the calendar year to fiscal year in Access VBA. Currently, in the VBA, I only need to find the Max of Calendar Year and then if it's different with the Year(Date) then I know it's a new calendar year. From that result, I can re-set the sequential number back to 000001. If it's the current calendar year (i.e., if the Max of Calendar Year = Year(Date()) then I only increase the sequential number by 1. The codes as follows.

'new calendar year
If [Forms]![frm1].[Form]![subform1]![maxOfCalendar_Year] <> Year(Date) Then
[Forms]![frm1]![SEQUENTIAL_NUMBER] = "000001"
[Forms]![frm1]![CUSTOMER#] = "xxxxxxxxx" & "_" & Year(Date) & "_" & "000001"
'current calendar year
If [Forms]![frm1].[Form]![subform1]![maxOfCalendar_Year] = Year(Date) Then 
[Forms]![frm1]![SEQUENTIAL#] = [Forms]![frm1].[Form]![subform1]![MAXOfSEQuential_Number] + 1

Now I need to change the calendar year to fiscal year starting 10/01 ending 09/30. Do you know how to do that? Thank you in advance for your help.

 

Answer 1

Use   DateAdd("m", 3, Date())

---

Build a little, test a little

Answer 2

Hi ttim,

Since you're looking for the "year," I'm just adding to what QA Guy already posted:

=Year(DateAdd("m", 3, Date()))

Cheers!

Answer 3

Thank you, QA Guy and the DBguy, but I don't understand. DateAdd("m", 3, Date()) as I tested today gave the result: 9/27/2017 and Year(DateAdd("m", 3, Date())) gave the result: 2017.

I don't see any thing that makes sense to me based on my OP. Also, the [maxOfCalendar_Year] field (from the OP) is the result of a query that has only the year information, not the day and month. How can I compare the maxOfCalendar_Year (year only) with the Fiscal year starting 10/01 and end 09/30? Thanks 

 

Answer 4

Hi, What does the maxOfCalendar_Year represent? If it represents the fiscal year, you could try the following: If maxOfCalendar_Year = Year(DateAdd("m",3,Date)) Then Hope it helps...

Answer 5

Hi theDBguy, the maxOfCalendar_Year represents last calendar year, i.e., 2017. It has nothing to do with the fiscal year. Why do you add 3 into the DataAdd function?

The fiscal year starting 10/01. For example, as of today, I have customer number is xxxx_2017_000035. In Oct 01, the new customer number will be xxxx_2018_000001.

Thank you.

Answer 6

Hi,

Thanks for the clarification. The code I just gave you should work then. So, something like:

If maxOfCalendar_Year = Year(DateAdd("m",3,Date())) Then

    'current fiscal year

    Sequence = Max + 1

Else

    'different fiscal year

    Sequence = 1

End If

We add 3 months to the current date to get the fiscal year. So, on Oct 1, 2017, adding 3 months would result in Jan 1, 2018, which gives us fiscal year 2018.

Hope it makes sense... 

Answer 7

Yes, it makes sense. Thanks a lot. How about 09/30, the end of the fiscal year? Do I have to take care of that also or it doesn't matter? Thank you, theDBguy.

Answer 8

Hi,

The Date() function in DateAdd("m",3,Date()) will return the current date. So, on 09/30/17, the Date() function will return 09/30/17, and the DateAdd() expression will return fiscal year 2017. On the next day, 10/01/17, the Date() function will return 10/01/17, and the DateAdd() expression will return fiscal year 2018.

In other words, the Date() function should take care of 09/30 for you.

Cheers!

Answer 9

Very good, theDBguy. Thanks a lot. I appreciate your help.

Answer 10

Very good, theDBguy. Thanks a lot. I appreciate your help.

Hi ttim,

You're welcome. QA Guy and I were happy to assist. Good luck with your project.