Access: Number format for VBA in queries of variant type Lets say I have two queries like

select iif(condition, col, null) from table

select f(col) from table


where col is a double-valued column f is a VBA-public function(value as double) as variant. Somewhere later, I alter the format of the respective field to be displayed with a certain number format, for instance as a percentage. I have decided that f should be a variant since it is supposed to return Null if appropriate. However, in the former case, the format I specify is respected, but if I switch to the latter, the number format is totally ignored. Why?
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