1

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?

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
  • Isn't this odd somehow, since a column of a certain datatype (in SQL) also can store NULL-values? – Bubaya Jan 24 '18 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 '18 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 '18 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 '18 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 '18 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.