2

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?

Improve this question

1 Answer 1

Reset to default
2

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? " & (rs.Fields(0).Type = dbText)
rs.Close
Improve this answer
5
  • Isn't this odd somehow, since a column of a certain datatype (in SQL) also can store NULL-values?
    – Bubaya
    Jan 24, 2018 at 12:01
  • There are other languages besides SQL that have nullable types--specific data types that can contain null--but which are not general data containers like the VB Variant. Relational databases and SQL are optimized and designed for application of specific behaviors for storing and retrieving data. Representing the case of "no value" or "missing value" (i.e. null) is actually a very natural implementation. Access does a pretty good job at merging an RDBS with the VBA language, but they are still separate concepts. The more systems you learn to use, the less "odd" it may seem.
    – C Perkins
    Jan 24, 2018 at 14:11
  • So, this means it is impossible to obtain a SQL-null from a VBA-null without doing some iif-workaround?
    – Bubaya
    Jan 25, 2018 at 15:18
  • Not necessarily. It only means that in the case of a column data type--since Access SQL has no way to explicitly define the type of a variant function--that the IIF solution provides a way for the SQL engine to determine the column type. However, other occurrences of Null from VBA functions can be handled directly, for instance when checking for null in the WHERE clause: ... WHERE func([Column parameter]) Is Null should work just fine.
    – C Perkins
    Jan 25, 2018 at 18:19
  • ... Of course, a variant-returning function can still return other valid variant values like Empty, Error, and Nothing, but how the SQL engine would handle those might be unpredictable or at least inconsistent. But such concerns about variants exist within VBA itself. If you are certain that a function will only return a single type or null, then the IIF function is sufficient. If a variant might contain various possible types or special variant values, it would be prudent to create an auxiliary function for handling strange cases rather than injecting it directly into an SQL statement.
    – C Perkins
    Jan 25, 2018 at 18:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.