Return Records in Current Fiscal Year

[BLFOSTER]

I have been trying to figure this out with no luck. I just want to return records that have a date within the current fiscal year - whatever the current fiscal year is. Ours starts 7/1 and ends 6/30. I am doing this in VBA so when the user selects the "YTD(year to date)" button, the records for the current fiscal year are retrieved. I found these functions, but am having no luck putting the whole thing together. Has anyone done this?

I have tried a lot of variations of this but records ending in 2011 are still being returned:[CODE]Month(GetFiscalMonth([End Date]) > 6 and Year([End Date] between Year(Date())-1 and Year(Date())

Thanks for any assistance you can provide!

FUNCTIONS:

[CODE]Option Explicit
Const FMonthStart = 7 ' Numeric value representing the first month
' of the fiscal year.
Const FDayStart = 1 ' Numeric value representing the first day of
' the fiscal year.
Const FYearOffset = -1 ' 0 means the fiscal year starts in the
' current calendar year.


' -1 means the fiscal year starts in the
' previous calendar year.

Function GetFiscalYear(ByVal x As Variant)
If x < DateSerial(Year(x), FMonthStart, FDayStart) Then
GetFiscalYear = Year(x) - FYearOffset - 1
Else
GetFiscalYear = Year(x) - FYearOffset
End If
End Function
Function GetFiscalMonth(ByVal x As Variant)
Dim m
m = Month(x) - FMonthStart + 1
If Day(x) < FDayStart Then m = m - 1
If m < 1 Then m = m + 12
GetFiscalMonth = m
End Function
/CODE]

[davegri]

Keep it simple. All you need to know is what fiscal year you are dealing with. Let's call it dFiscalYear, a date field holding the fiscal year start date, mm/dd/yyyy characters. This could be entered by the user on a form.
Now, any record whose date occurs within 12 months after this date is a candidate. If the target date in the records is named tranDate, then

Dim nMonthDiff as long
nMonthDiff = DateDiff("m",dFiscalYear, tranDate)

To avoid getting dates from previous fiscal years we have to check also if the date difference is negative.
The query criteria would then be

WHERE nMonthDiff > 0 and nMonthDiff < 13

[ssanfu]

Not enough information.......

I just want to return records that have a date within the current fiscal year - whatever the current fiscal year is. Ours starts 7/1 and ends 6/30. I am doing this in VBA so when the user selects the "YTD(year to date)" button, the records for the current fiscal year are retrieved. I found these functions, but am having no luck putting the whole thing together.

You posted a couple of functions, but not the code you are using to retrieve the records.
What is the code behind the button?
SQL of a query?

[BLFOSTER]

Well, I am not sure it makes any sense at all is why I didn't post it! But here it is. I have tried several variations...just not working. Of course, I don't really know what I am doing might have something to do with it!

Behind the button:

"SELECT * FROM MainFormData " _& "WHERE [activity] = 'Job Search (JO)' and [Referring Branch] = '3402' and Not IsNull([Start] and IsNull([End]) OR [activity] = 'Job Search (JO)' and [Referring Branch] = ’3402’  and Month(GetFiscalMonth([End] > 6 and Year([End] between Year(Date())-1 and Year(Date()))))) "

[ssanfu]

First be aware that "Start" and "End" are reserved words and should not be used as object names. Plus they are not very descriptive... "Start" of what.
You know what "Start" and "End" refer to, but in 6 months it will be hard to follow... or if someone else has to debug the dB.......
Better names might be "FYStart", "FY_Start", Start_FY", "FYEnd", FY_End", End_FY".

Also, shouldn't use spaces in object names.


-----------------------------------------------

Behind the button:

That can't be all there is behind the button - just a SQL string?
No VBA?
-----------------------------------------------


There are errors in the SQL string. I broke the string by clause:

SELECT * 

FROM MainFormData

WHERE [activity] = 'Job Search (JO)' 
and [Referring Branch] = '3402' 
and Not IsNull([Start] 
and IsNull([End]) 
OR [activity] = 'Job Search (JO)' 
and [Referring Branch] = ’3402’  
and Month(GetFiscalMonth([End] > 6 
and Year([End] between Year(Date())-1 and Year(Date())   ))))

Missing 2 closing parenthesis Month(GetFiscalMonth([End]
Missing closing parenthesis Year([End]
Too manyclosing parenthesis ))))

Not sure how the "Or" fits in. Not sure how Access will parse the string.

[orange]

BLFoster,

You may get some ideas from this older post. (especially John's comment .... post #6)

Good luck with your project