VB.Net error with CASE SELECT

Question

The following VB.Net code runs fine on SQL Server but gives an error on Access:

sql = "SELECT sr.itemcode, sr.itemname, SUM(CASE WHEN sr.transdate='4/14/2016' THEN sr.temunits] END) AS [Units Sold], " & _
      "SUM(sr.itemunits) AS [Total Units Sold] " & _
      "FROM [tblINV_SalesRecord] AS sr WHERE sr.transdate <= '4/14/2016' GROUP BY sr.itemcode, sr.itemname"

The error is:

IErrorInfo.GetDescription Failed with E_FAIL(0x80004005), -System.Data

Answer 1

MS Access does not support CASE expressions. The most generic equivalent would be the Switch function. Using that function, a SQL CASE expression like this

CASE
  WHEN condition1 THEN value1
  WHEN condition2 THEN value2
  ...
END

can be rewritten like this:

Switch(
  condition1, value1,
  condition2, value2,
  ...
)

A more specific, and probably more appropriate for your situation, equivalent is the IIf function. It is intended for use when you need to return one of two values based on a single condition. In other words, it replaces the following CASE pattern:

CASE WHEN condition THEN true_value ELSE false_value END

and goes like this:

IIf(condition, true_value, false_value)

In your particular situation, the CASE expression does not have an ELSE clause. That means that ELSE NULL is implied, although ELSE 0 may be more appropriate here. So, you can rewrite the query like this:

sql = "SELECT sr.itemcode, sr.itemname, " & _
             "SUM(IIf(sr.transdate='4/14/2016', sr.temunits, 0)) AS [Units Sold], " & _
             "SUM(sr.itemunits) AS [Total Units Sold] " & _
      "FROM [tblINV_SalesRecord] AS sr WHERE sr.transdate <= '4/14/2016' " & _
      "GROUP BY sr.itemcode, sr.itemname"