One way to ensure that the [Age] field aliasing the function call always has a numeric value is to wrap the function call inside an Nz statement which returns a specified value if the inner statement is null. As Nz would return a value treated as text to the query this in turn has to be wrapped in another function such as Val or CLng to convert the text value to a whole-number:
- Age: CLng(Nz(fn_Age([DOB], 0)))
In the statement above a 0 is returned if fn_Age returns a null. Sorting on such a value will place unknown ages first in the sort if in ascending order. If you want these to be last in the sort order, or to be flagged in an unambiguous way, you could just use a placeholder such as 999 for the age instead:
- Age: CLng(Nz(fn_Age([DOB], 999)))
That way, all ages listed as 999 in value represent records with an unknown DOB.
Assuming that the null returned by fn_Age results from a null DOB, if you want to use the SQL IS NULL statement which you mention did not work correctly for you, you'd need to test the DOB field in an IIF statement like this:
- [Age]: IIF([DOB] IS NULL, 999, fn_Age([DOB]))
Using IS NULL is a more general solution, partly because Nz is not an obvious function to call (the name does not identify what it does at all, unlike Val, say) and it is peculiar to MS VBA, so not easy to transfer if you need to port your Access SQL queries to other systems.
Lastly, you might find it better to resolve this issue at source in your fn_Age function code, by modifying the function so that it always returns an integer instead of a variant value. If it is [DOB] which may be null you could use the IsNull function within an IF statement in the VBA code for fn_Age to test for this and set or return an appropriate dummy value such as 0 or 999 in the same way:
- If IsNull(DOBVariableName) Then
-
fn_Age = 999
-
Else
-
{your existing calculation for DOB here}
-
End If
-Stewart