return null to date field

[LGarcia]

Hi all,
I took a stab at writing a function. I'm comparing 2 date fields. I am
choosing either the one with the latest date or the one that isn't null. If
both are null then leave it blank. Having problem with passing a null.
Hope someone can help. Here is the code:

Function GrantsDate(strSubmitDate, strAwardDate) As Variant

Dim strReportDate As Variant

If IsNull(strSubmitDate) And IsNull(strAwardDate) Then
strReportDate = Null '*****this is gving me an invalid use of null
error*******
End If

If IsNull(strSubmitDate) And Not IsNull(strAwardDate) Then
strReportDate = strAwardDate
End If

If Not IsNull(strSubmitDate) And IsNull(strAwardDate) Then
strReportDate = strSubmitDate
End If

If Not IsNull(strSubmitDate) And Not IsNull(strAwardDate) Then
If strSubmitDate > strAwardDate Then
strReportDate = strSubmitDate
Else
strReportDate = strAwardDate
End If
End If

GrantsDate = strReportDate

End Function

 

[John W. Vinson]

Function GrantsDate(strSubmitDate, strAwardDate) As Variant

Dim strReportDate As Variant

If IsNull(strSubmitDate) And IsNull(strAwardDate) Then
strReportDate = Null '*****this is gving me an invalid use of null
error*******
End If

A String variable cannot pass a NULL, whereas a Variant can. Try

Function GrantsDate(strSubmitDate As Variant, strAwardDate As Variant) As
Variant

 

[LGarcia]

Thanks for the info and your suggestion! The value that was returned was
being treated as text. I thought more about the logic and made it much
simpler: GrantsDate: IIf([SubmitDate]>[AwardDate] Or
IsNull([AwardDate]),[SubmitDate],[AwardDate])
Seems to work!