DateAdd returns Error in calculated date field

[Santa-D]

I'm using a simple VBA function to calculate the return date of a
vehicle, except where a value is null the result comes up as #ERROR and
when I try to do sort on the query I also get "Invalid use of null".

Previously I used this code which gave me the error above:

Function EDD(FinDate As Date, TERM As Double) As Date

If IsNull(TERM) Or IsNull(FinDate) Then
EDD = ""
Else
EDD = DateAdd("m", TERM, FinDate)
End If
End Function

If a vehicle did not have a date in the FinDate field it resulted in
#ERROR

I then tried the following and I still got the same error

Public Function EDDR(FinDate As Date, TERM As Double) As Date

EDDR = nz(DateAdd("m", TERM, FinDate), 0)

End Function

I also tried returning the 0 as "" and = Null, as all I want is the
field to be empty and able to sort it without any troubles.

 

[Jeff Boyce]

Your first function is trying to set a Date (what the function returns) to a
string value of "" (zero-length string).

If there is no date in FinDate, maybe you need to treat it as a Variant,
rather than a date, to get a Null value into your function.

Have you set a breakpoint in your function to identify the point at which
the error occurs?

Have you considered adding error handling to help?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Smartin]

I'm using a simple VBA function to calculate the return date of a
vehicle, except where a value is null the result comes up as #ERROR and
when I try to do sort on the query I also get "Invalid use of null".

Previously I used this code which gave me the error above:

Function EDD(FinDate As Date, TERM As Double) As Date

If IsNull(TERM) Or IsNull(FinDate) Then
EDD = ""
Else
EDD = DateAdd("m", TERM, FinDate)
End If
End Function

If a vehicle did not have a date in the FinDate field it resulted in
#ERROR

I then tried the following and I still got the same error

Public Function EDDR(FinDate As Date, TERM As Double) As Date

EDDR = nz(DateAdd("m", TERM, FinDate), 0)

End Function

I also tried returning the 0 as "" and = Null, as all I want is the
field to be empty and able to sort it without any troubles.

Right, you need to test for Null on every term that might be Null. You
are testing too late. Try something like

EDDR = DateAdd("m", Nz(TERM,0), Nz(FinDate,0))

 

[Santa-D]

Thanks for pointing me in the right direction.

I've modified it to this and it works a charm.

Public Function EDDR(FinDate As Variant, TERM As Double) As Variant

If IsNull(TERM) Or IsNull(FinDate) Then
EDDR = ""
Else
EDDR = Format((DateAdd("m", TERM, FinDate)), "dd-mm-yy")
End If

Thanks again for your help.

 

[Smartin]

Right, you need to test for Null on every term that might be Null. You
are testing too late. Try something like

EDDR = DateAdd("m", Nz(TERM,0), Nz(FinDate,0))

Ah, of course Mr. Boyce is correct. And I am "testing too late" as well!

 

[Santa-D]

I've made the changes in my previous post but the problem now is that
rather than sorting it by date order it's sorting in any order.

EG:

Vehicle 1 - 10/06/2008
Vehicle 2 - 10/09/2006
Vehicle 3 - 10/11/2006
Vehicle 4 - 10/11/2008
Vehicle 5 - 10/11/2009
Vehicle 6 - 11/01/2007

I went into the query and did a cdate() on the function and it didn't
like the empty fields.

 

[Van T. Dinh]

In the declarations of the functions:

Function EDD(FinDate As Date, TERM As Double) As Date

and

Function EDDR(FinDate As Date, TERM As Double) As Date

You already declared FinDate as a date value and TERM as a Double so neither
of them can be Null. Therefore, you don't need to check for Null inside the
function. In addition, in this case, you can set the return as Date data
type.

If it is possible for the input arguments to be Null, you need to check and
ensure they are not Null before feeding them into your function. If you feed
Null to either function, you will get an error. It is likely that the
errors you have been getting are caused by supplying Null to the functions!

 

[John Spencer]

Change the function to return a Date value or null instead of a string -
which is what happens if your return "" or a formatted date. You can format
the returned date in the query if you need to show a specific format.
Better would be to apply the format to a control on a form or report.

Public Function EDDR(FinDate As Variant, TERM As Double) As Variant

If IsNull(TERM) Or IsNull(FinDate) Then
EDDR = Null
Else
EDDR = DateAdd("m", TERM, FinDate)
End If

End Function