MS Access: Query rejects sorts when calculations are made field with null value

[David Blackman]

I have a query that shows date of birth from a field and calculates age as a variant with a simple function:

[Age]: fn_Age([DOB])

If any record in the table contains a null [DOB], the query rejects all sorts based on the [Age] field.

I’ve tried using the ‘Is Not Null’ criteria on [DOB], but still get the “Data type mismatch in criteria expression” error.

Any suggestion?

[Stewart Ross]

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

[zmbd]

+ David, what is omitted here is what exactly is it you want done with the sort order when ISNUll([DOB])=True

+ Personally, of the solutions offered by Stewart, [Age]: IIF([DOB] IS NULL, 999, fn_Age([DOB])) is the better option. :)

+ However, I don't like "false" values if they can be avoided; thus, another option, and this is a partial SQL from a production table I use, is to check for nulls, sort them first and then handle the date (or age in your case?):

SELECT tbl_EventHistory.EventHistory_pk
   , tbl_EventHistory.event_datetime
FROM tbl_EventHistory
ORDER BY IsNull([event_datetime]) DESC
   , tbl_EventHistory.event_datetime;

So, this will place all of the null value [Event_DateTime] at the bottom (true = -1 in Access). Either remove the DESC or change to ASC to group at the top.

Now, with [Age] being a calculated field, I'm not so sure that you can use the field name to replace [Event_DateTime] in this; however, isnull(fn_Age([DOB])) should work or you could just use the [DOB] as I have used [Event_DateTime] - I am guessing that [DOB] is a date/time field and I know that this construct works in my production database (active with more than a few hundred records :) )

[David Blackman]

Thanks for all the poop. I'm keeping your replies in OneNote for further reference.

What I'm doing is sorting ages into ranges; so if fn_Age returns a value that identifies the record as containing a null DOB, i.e. 999 as you suggest, I can simply use that value to exclude null records in the SQL WHERE clause.

You solved my problem. Thanks again. You folks are great. :)