Access: Number format for VBA in queries of variant type
The answer can be found here in Allen Browne's page:


  in a query, a column can be only be ONE data type. JET therefore treats Variants as Text, since anything (numbers, dates, characters, ...) is valid in a Text column.


Numeric formatting options will not affect the text values which are automatically converted from the VBA Variant, even if the original value was coded as numeric.

His recommended solution is to do what you have already discovered: Use the IIF statement.  The Jet engine uses datatype clues from the parameters to establish the returned column's data type.



Here's some code that will reveal the automatically-selected column type:

Dim rs As Recordset2
Set rs = CurrentDb.OpenRecordset("SELECT f([Col]) FROM table")
Debug.Print "Is text? " &amp; (rs.Fields(0).Type = dbText)
rs.Close