Convert a formula to Function

[khwaja]

I use the following formula in my query to work FY where last week day in June is end of the FY. Works well. Is there a possibility that I can have function which does the same thing so that it readily available everywhere in my DB.


Fin Year: IIf(IsNull([budgetopening]),[FY],IIf([budgetopening]<=DateValue("30/06/" & Year([budgetopening]))-Weekday(DateValue("30/06/" & Year([budgetopening])))+1,"FY " & Right(Year([budgetopening]),2),"FY " & Right$(Year([budgetopening])+1,2)))

 

[JohnLee]

Hi,

Have you tried reading up on functions or search this forum for information on creating functions.

Here's an example of an Array Function that might be of help or point you in the right direction from the help files in Access:

[COLOR=blue]Dim[/COLOR] MyWeek, MyDay
MyWeek = [B]Array([/B]"Mon"[B],[/B] "Tue"[B],[/B] "Wed"[B],[/B] "Thu"[B],[/B] "Fri"[B],[/B] "Sat"[B],[/B] "Sun"[B])[/B]
[COLOR=darkgreen]' Return values assume lower bound set to 1 (using Option Base
' statement).
[/COLOR]MyDay = MyWeek(2)    [COLOR=darkgreen]' MyDay contains "Tue".
[/COLOR]MyDay = MyWeek(4)    [COLOR=darkgreen]' MyDay contains "Thu".
[/COLOR]

John

 

[khwaja]

Thanks John. No I have not tried writing a complex function, hence this request for help. I am a very basic VB user. If you could kindly help out, I will be grateful. Cheers

 

[jdraw]

If your FY means Fiscal Year yo may want to read this.
http://support.microsoft.com/kb/210249