1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access 2007 VBA Fiscal Year Start Date

Discussion in 'Business Applications' started by spatha, Oct 21, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. spatha

    spatha Thread Starter

    Joined:
    Jun 20, 2011
    Messages:
    82
    I am trying to write a function that will determine the first day of our fiscal year for whatever date is inputed into it.

    Our fiscal year always ends on the friday closest to 1/31.

    I'm having trouble figuring out how to deal with january dates.

    Any help would be greatly appreciated.

    Code:
    Function FiscalYearStart(dteDate As Date)
    Dim dteNextFriday As Date
    Dim dteLastFriday As Date
    Dim x As Integer
    Dim dteJanThirtyFirst As Date
     
    'set as january thirty first of calendar year
    dteJanThirtyFirst = "1/31/" & Format(dteDate, "yyyy")
     
    'if 1/31 is a friday
    If Weekday(dteJanThirtyFirst) = 6 Then
     
        'use 1/31 and exit function
        FiscalYearStart = dteJanThirtyFirst
        Exit Function
     
    Else: End If 
     
    'set as friday after january thirty first
    dteNextFriday = DateAdd("d", 8 - Weekday(dteJanThirtyFirst, vbFriday), dteJanThirtyFirst)
     
    'determine day of week for january thirty first
    x = Weekday(dteJanThirtyFirst)
     
    'if weekday is saturday
    If x = 7 Then
     
        'subtract 1
        dteLastFriday = DateAdd("d", -1, dteJanThirtyFirst)
     
    Else
     
        'subtract 1 and the number of the weekday
        dteLastFriday = DateAdd("d", -1 - x, dteJanThirtyFirst)
     
    End If
     
    'if last friday is less than next friday
    If DateDiff("d", dteJanThirtyFirst, dteNextFriday) > DateDiff("d", dteLastFriday, dteJanThirtyFirst) Then
     
        'use last friday and add 1 day to get start of fiscal year
        FiscalYearStart = DateAdd("d", 1, dteLastFriday)
     
    Else
     
        'use next friday and add 1 day to get first of fiscal year
        FiscalYearStart = DateAdd("d", 1, dteNextFriday)
     
    End If
     
    End Function
    
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    If you establish what weekday the 31st of January is, (other than 6 of course), you only need to add or subtract a maximum of 3 days to get to the nearest friday.
    So if the 31st is 1, 2 or 7 then you subtract 3, 2 or 1 day.
    If it is 3,4 or 5 you add 3, 2 or 1 day.
     
  3. spatha

    spatha Thread Starter

    Joined:
    Jun 20, 2011
    Messages:
    82
    Thanks for the reply.

    My problem isn't finding the nearest friday to January 31st. The problem is if the input date is between January 1st and the end of the fiscal year, it's counted as part of the next fiscal year.

    For example, the current fiscal year started on 1/29/2011. If the date inputed is between 1/1/2011 and 1/28/2011, it's counted as part of fiscal year 2011, when it should be 2010.
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Sorry, that was not how I read your first post
    You can establish any day as the start of a fiscal year and use a query to work with that date.
    Is taht what you want to do?
     
  5. spatha

    spatha Thread Starter

    Joined:
    Jun 20, 2011
    Messages:
    82
    I would prefer to stay away from using static dates (this is the what the bandaid function I'm using now does), I want this function to be completely dynamic and work for any day (past or future) to determine the start of the fiscal year. Once this is working I'll be able to create other functions to determine fiscal quarters and months.
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    The date is not "static", it is stored in a table and worked with from there by combining it with a query.

    However if you want a VBA solution I am still not clear what you want it to do, originally you said "Our fiscal year always ends on the friday closest to 1/31", then you said "My problem isn't finding the nearest friday to January 31st. " and then
    "For example, the current fiscal year started on 1/29/2011. If the date inputed is between 1/1/2011 and 1/28/2011, it's counted as part of fiscal year 2011, when it should be 2010" - this last is solved using the Query method.

    But what is the application of the VBA function, is it just for a single day or a date period?

    I am trying to understand "How" you are going to use the function once you have it?
     
  7. spatha

    spatha Thread Starter

    Joined:
    Jun 20, 2011
    Messages:
    82
    This function itself will just be the basis for several more functions. Once I have a function that returns the start of the fiscal year, I can use that date to calculate the fiscal month, and then the fiscal quarter. The current application i'm working on is to group queries, but I'd like to be able to use these functions in other applications as well, hence using vba rather than a table.
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    OK, I understand now and I see your error.
    the Function is not complete, you are finding the Start date but not comparing it to the input date.
    Just add this on the bottom before the exit function
    If FiscalYearStart > dteDate Then
    FiscalYear = Year(FiscalYearStart) - 1
    Else
    FiscalYear = Year(FiscalYearStart)
    End If
    FiscalYear holds the Year that the date belongs to - see the attached database the Form gives a message box providing the Fiscal year based on the input in the unbound field.

    PS it might be possible that
    If FiscalYearStart > dteDate Then
    should be
    If FiscalYearStart >= dteDate Then
     

    Attached Files:

  9. spatha

    spatha Thread Starter

    Joined:
    Jun 20, 2011
    Messages:
    82
    I really appreciate your help.

    I tried adding the code above to my function but I'm still getting the same incorrect start date if the input date is before the fiscal year start date..

    I can't open the file you attached, I don't have anything to extract 7 zip files and the web services i've tried wont extract it either. Could you possibly attach it as a regular zip file?
     
  10. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    What zip can you handle, I only have 7zip and Zipgenius, both of which are free downloads.
     
  11. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    What is your Function returning, it needs to return FiscalYear.
     
  12. spatha

    spatha Thread Starter

    Joined:
    Jun 20, 2011
    Messages:
    82
    The problem is that my company is really restrictive on what programs we can install. I would have to submit a request and wait at least a couple weeks before I could download even a free program.

    I can handle any archive file that windows explorer can extract. So just a .zip or .rar I believe.
     
  13. spatha

    spatha Thread Starter

    Joined:
    Jun 20, 2011
    Messages:
    82
    Ok, I have it working now. I was expecting the function to return the actual start date, not the year. I can work with this to get the functions I want.

    Thank you.
     
  14. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Ok, I will try zipgenious instead.
     

    Attached Files:

  15. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    OK, we cross posted, glad we got there in the end.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1023347

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice