Results 1 to 11 of 11

Change Fiscal Year based on date entered into incremented field

  1. #1
    rhokatd04 is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2018
    Posts
    6

    Change Fiscal Year based on date entered into incremented field

    Requesting your assistance....I've researched, copied, tested many codes and scripts from the forum. I am building a database and need to incremented numbers i.e. 2018-001; 2018-002 but I need it to auto change to the next fiscal year based on the Travel Start date and not today's date. I've attached the database to show that I need 2018-002 to be 2019-001 because the start date is after the fiscal year. Any assistance will be greatly appreciated.
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    6,797
    I have a table that shows my Fiscal years:
    MoNum Mo MoName FiscalOrder FiscalYr
    1 Jan January 10 1
    2 Feb February 11 1
    3 Mar March 12 1
    4 Apr April 1 0
    5 May May 2 0
    6 June June 3 0
    7 July July 4 0
    8 Aug August 5 0
    9 Sep September 6 0
    10 Oct October 7 0
    11 Nov November 8 0
    12 Dec December 9 0

    the fiscal start month uses the current year, has fiscal year 0 to add to the year.
    fiscal year 11 (above) adds 1 to the current year.

  3. #3
    rhokatd04 is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2018
    Posts
    6
    I have the incremented field set-up based on the current date, but I want the incremented field to change based on the date entered into the start date and not the current date.

  4. #4
    JSR is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2018
    Location
    Reno, NV
    Posts
    38
    When does the fiscal year start?

    Does it have to be in date order? If so, you will need a VBA function to calculate the "auto" numbers for each fiscal year.

    Ideally, you should not shove data together like that. You should have one field for fiscal year (easy calculation, as above, add 0 or 1 depending on month) and another field that is simply an autonumber. You can then add data all you like and the autonumber will keep increasing. If date order is not relevant, a query can renumber those autonumbers for each fiscal year.

  5. #5
    rhokatd04 is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2018
    Posts
    6
    No, it doesn't have to be in date order, but the number needs to start over at one in the fiscal year. Fiscal Year start 1 October. Based on the travel date entered.

  6. #6
    JSR is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2018
    Location
    Reno, NV
    Posts
    38
    Do you need this value to be populated once a new row is entered, or are you trying to update existing data?

    If the former, you will have to use a form with VBA code in it to increment the value based on the fiscal year. Autonumber is a nice feature but it cannot be reset within a table.

    If the latter, I will show you how to do this (already tested it in your db, works fine).

  7. #7
    rhokatd04 is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2018
    Posts
    6
    Once a new row is entered. I will be forever grateful if this works....Database is due to start this FY.

  8. #8
    JSR is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2018
    Location
    Reno, NV
    Posts
    38
    Okay, then you will need to use a form. It's more user-friendly anyway.

    Have one box for each piece of data that the user enters, and a command button called "add". This button will run VBA code to query the table for the last FY trip ID for the relevant fiscal year, add 1 to it, then add the row of data to the table, with everything the user typed in plus that FY trip ID.

  9. #9
    rhokatd04 is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2018
    Posts
    6
    Do you have an example of a vba code?

    Ok, so I add this to the field in the form, it changes based on fiscal year. But it formats to the AutoNumber "ID" field. How do I set up the VBA to query as you stated above?

    =IIf(month([Start Date])<10,Year([Start Date]),Year([Start Date])+1) & "-" & Format([ID]), "0000")

  10. #10
    JSR is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2018
    Location
    Reno, NV
    Posts
    38
    What you want to do is take [ID]-Min([ID])+1 for that fiscal year (i.e. 5 is the first ID for FY 2019, so that one would be 5-5+1=1, the next will be 6-5+1=2, etc.)

    This code will return the minimum of ID for the given fiscal year:

    Code:
    Public Function GetMinID(FiscalYear As Integer) As Long
    
    Dim db As Database
    Dim rst As Recordset
    
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("select min([ID]) as MinID from [tblTasks] where year([start date])+iif(month([start date])>=10,1,0)=" & FiscalYear)
    GetMinID = rst![MinID]
    rst.Close
    db.Close
    
    End Function
    In the form, you want this:
    =[fiscal_year] & "-" & Format([ID]-GetMinID([fiscal_year])+1,"0000")

    (I made another text box called fiscal_year which does your IIf() calculation)

    I don't like having to open and close a database and recordset object for every single row, but I can't think of anything else right now.

  11. #11
    rhokatd04 is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2018
    Posts
    6
    Thanks, I will try this.

    Where do I put this? =[fiscal_year] & "-" & Format([ID]-GetMinID([fiscal_year])+1,"0000")
    It return a #Type! when I place it on a field in the form.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 10-14-2015, 02:45 PM
  2. Replies: 7
    Last Post: 06-07-2015, 10:57 PM
  3. Replies: 2
    Last Post: 07-15-2014, 11:00 AM
  4. Date Function to dynamically produce fiscal year?
    By aellistechsupport in forum Programming
    Replies: 15
    Last Post: 05-15-2014, 09:28 AM
  5. Replies: 2
    Last Post: 03-07-2013, 02:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums