Flexible Use of Functions

[khwaja]

I use following functions to centrally change FYs. I use these functions in my queries.

Function getFromFY() As String
getFromFY = "FY 10"
End Function

Function getToFY() As String
getToFY = "FY 12"
End Function

But sometimes I have had to change the range. So does it mean that I need to create more functions or can I use these functions and use these so that I add or substract FY from an existing function. For example if I want to change the 'gettoFY' funmction by one more FY to FY 13, how could that be achieved in a query without having to create more functions?

 

[vbaInet]

I'm not fully understanding what you're after but maybe this would help. You can amend the return value of the function by sending the number as the argument and concatenating to the result.

Function getToFY(myValue as string) As String
getToFY = "FY " & myValue
End Function

That is if the range can be set within the query.

But again, why would u need to do that if you could simply concatenate FY to the number in your query. Or is there something I'm missing that you haven't expressed?

 

[khwaja]

Sorry for being too clear. I have a number of queries which work off different range of FYs. Having these FYs sit as a function, allows me to modify my queries globallly as soon as I move to a new FY. Currently I am restricted to values as part of "FY From and To" functions I have created. But occassionaly I need to use these functions with an added year or two. For example, my functions have 'From' value FY 10 and 'To' value FY 12, I might need to extend FY 12 to Fy 13 without having to modify the function. If there is something I can do within the query to achieve this, that would serve the purpose.

 

[vbaInet]

Investigate the DateAdd() and Year() functions. It may be what you need.