none
Calendar Year to Fiscal Year RRS feed

  • 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.

     

    Tuesday, June 27, 2017 7:52 PM

Answers

  • 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!

    • Marked as answer by ttim Wednesday, June 28, 2017 3:33 PM
    Wednesday, June 28, 2017 3:20 PM

All replies

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

    Build a little, test a little

    Tuesday, June 27, 2017 8:39 PM
  • 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!

    Tuesday, June 27, 2017 9:09 PM
  • 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 

     

    Wednesday, June 28, 2017 3:53 AM
  • 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...
    Wednesday, June 28, 2017 5:45 AM
  • 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.

    • Edited by ttim Wednesday, June 28, 2017 2:12 PM
    Wednesday, June 28, 2017 2:07 PM
  • 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... 

    Wednesday, June 28, 2017 2:41 PM
  • 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.
    Wednesday, June 28, 2017 3:07 PM
  • 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!

    • Marked as answer by ttim Wednesday, June 28, 2017 3:33 PM
    Wednesday, June 28, 2017 3:20 PM
  • Very good, theDBguy. Thanks a lot. I appreciate your help.
    Wednesday, June 28, 2017 3:32 PM
  • 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.

    Wednesday, June 28, 2017 4:01 PM