# Solved: Access 2007 VBA Fiscal Year Start Date

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

Not open for further replies.

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

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

Else

'use next friday and add 1 day to get first of fiscal year

End If

End Function
```

2. ### 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.

Joined:
Jun 20, 2011
Messages:
82

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

Joined:
Mar 8, 2005
Messages:
19,895
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?

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

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?

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

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:

• ###### fiscal year.7z
File size:
11.8 KB
Views:
71

Joined:
Jun 20, 2011
Messages:
82

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

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

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

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.

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

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

File size:
14.1 KB
Views:
84
15. ### OBP

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

As Seen On