Access 2003 SQL Switch breaks data types?

Question

Hi, I'm running Access 2003. I'm using Switch to select date fields based on a boolean criterion:

Switch(<criterion>, Date1, 1, Date2)

i.e., if "criterion" is true, then return Date1, otherwise return Date2.

Date1 and Date2 are Date/Time type columns in a table.

The problem is, Switch returns them as Text -- not Date/Time!

Is there a way to force them into Date-ness? I tried

Switch(<criterion>, #Date1#, 1, #Date2#)

And

Switch(<criterion>, Val(Date1), 1, Val(Date2))

Both of which fail with one error message or another.

Any ideas?

Answer 1

I think the Immediate If [IIf()] function is a better match for what you're trying to do:

IIf(<criterion>, Date1, Date2)

But the Switch() function shouldn't break data types, and is not incompatible with date/time data types. Consider this function:

Public Function trySwitch(ByVal pWhichDay As String) As Variant
    Dim varOut As Variant
    varOut = Switch(pWhichDay = "yesterday", Date - 1, _
        pWhichDay = "today", Date, _
        pWhichDay = "tomorrow", Date + 1)
    trySwitch = varOut
End Function

trySwitch("today") returns 10/6/2009 and TypeName(trySwitch("today")) returns Date

Answer 2

There's something strange with your example.

Switch accepts expression pairs, and if the first evaluates as True, its paired value is returned, otherwise, it passes on to the second, and evaluates that argument.

You seem to be treating 1 as True, which it is because it's not Fales, but you'd be better off with:

  Switch(<criterion>, Date1, True, Date2)

But that's just a replication of the functionality of the Immediate If function, IIf(), and IIf() takes fewer arguments.

But it has the same problem, in that it returns a variant. But you should be able to coerce that to a data type that can be formatted as a Date.

But whether or not that variant will be implicitly coerced or you'll need to do it explicitly, depends on where you're using it. In a query result, you can sort the output from IIf([criterion], Date1, Date2) as a date, because the column gets coerced to date type.

If you have to do the coercion explicitly, CDate() is the function to use -- you'd wrap the outside function that produces Variant output with the CDate() function in order to be certain that the variant output is explicitly coerced to date type:

  CDate(IIf(<criterion>, Date1, Date2))

But I might very well be missing something important here, as I appear to be off on a completely different track...

Answer 3

Can you post some code and data to reproduce the problem, please? As this is SWITCH() in SQL code then I think SQL DDL (CREATE TABLE etc) and DML (INSERT INTO to add data) would be most appropriate :)

[Picky point: Access Database SQL does not have a 'boolean' data type. It has a YESNO data type which can be the NULL value; three-value logic is not Boolean.]

Here's some SQL DML (ANSI-92 Query Mode syntax) to demonstrate how it works as expected for me:

SELECT TYPENAME
       (
          SWITCH
          (
             NULL, #2009-01-01 00:00:00#, 
             FALSE, #2009-06-15 12:00:00#, 
             TRUE, #2009-12-31 23:59:59#
          )
       );

Change any of the 'criterion' values and the value is always returned as 'Date' i.e. of type DATETIME.

---

UPDATE:

That TYPENAME function is a great tool... Access seems to interpret the entire "column" of the resultset differently

Indeed. Because a column can only be one data type the results of TYPENAME() at the row can be misleading. Row values of mixed types must be 'promoted' to a higher data type. As is usual with the Access Database Engine, the process is entirely opaque and the documentation on the subject completely absent, so you just have to suck it and see e.g.

SELECT #2009-01-01 00:00:00# AS row_value, 
       TYPENAME(#2009-01-01 00:00:00#) AS row_type
  FROM Customers
UNION ALL
SELECT 0.5, 
       TYPENAME(0.5) AS row_type
  FROM Customers

returns 'Date' and 'Decimal' respectively but what will the column be? Apparently, the answer is:

SELECT DT1.row_value, TYPENAME(DT1.row_value) AS column_type
  FROM (
        SELECT DISTINCT #2009-01-01 00:00:00# AS row_value 
          FROM Customers
        UNION ALL
        SELECT DISTINCT 0.5
          FROM Customers
       ) AS DT1;

'String'?!

...which of course isn't even a Access Database Engine SQL data type. So TYPENAME(), annoyingly, uses the name of the 'best fit' VBA type. For example:

SELECT TYPENAME(CBOOL(0));

returns 'Boolean' even though, as discussed above, there is no Boolean data type in Access Database Engine SQL. And

SELECT TYPENAME(my_binary_col)

returns 'String'. Note the same VBA mapping limitation applies to the CAST functions (yet another annoyance) e.g. there is no 'cast to BINARY' function and the CDEC() function remains broken since Jet 4.0 :(