Invalid use of Null (Error 94) - Need help w/ Code

[Jim Specht]

I am trying to create a custom function (which have need for often) in a Module. The function takes a date,(x), and returns the Fiscal year value. It works fine in a query where all values of the recordset are not null....but if just one record value is null, it returns an "Invalid use of Null (Error 94)" error. I can understand why it returns this but don't know how to accomidate it. Any suggestions...I'm not that great w/ code.

thx,
js

Here is my code:

;DECLARATIONS-----------
Option Explicit
Const FMonthStart = 6
Const FDayStart = 1
Const FYearOffset = -1

;GETFYR-------------
Function GetFYR(x As Variant)
If x < DateSerial(Year(x), FMonthStart, FDayStart) Then
GetFYR = Year(x) - FYearOffset - 1
Else
GetFYR = Year(x) - FYearOffset
End If
End Function

 

[simongallop]

How about using:

If IsNull(x) then

elseIf x < DateSerial(Year(x), FMonthStart, FDayStart) Then
GetFYR = Year(x) - FYearOffset - 1
Else
GetFYR = Year(x) - FYearOffset
End If

 

[Jim Specht]

Thank you. It worked perfectly.

I'm confused now though...my logic says that the required argument <<Result>> is missing... ie.
"If IsNull(x) then <<Result>>"

Why does your suggestion work if there is no specific <<Result>> specified??? I had tried something simular to this before....except I specified "is Null" for the result.
Like I said...I'm on a learning curve here. If you have the time to explain I would appriciate it.

thx,
js

 

[simongallop]

If statement logic (same as in Excel). If (statement) then, (if true what action),else (if false what action). At the end of the action got to End if

In the isnull example, the statement is saying if IsNull then do nothing (blank), otherwise carry on with the remainder of the code. On the do nothing, it then goes to end if and carries on hence why it misses out your calculations.

HTH

Simon

 

[Jim Specht]

Yes it does...Thank you for your time....I understand it now.

js