using case with access database : query is giving error

Question

User-610163401 posted

I have 2 datefields and i need to count days between these dates. I have a field say kind of category and i need to display these categories as columns and the days are dsplayed as per the category under that particular columns.
O/p
  x    |   y    |   z
-----------------------
  1    |         |
        |   3    |
  12   |        |
         |        |   4
here x, y, z are category in category column and the contents are date 2-date1+1

I was trying to use a query  such as,

SELECT CASE WHEN cat='x' THEN ([date2]-[date1]+1) END AS [X_COUNT],CASE WHEN cat='y' THEN ([date2]-[date1]+1) END AS [y_COUNT],CASE WHEN cat='z' THEN ([date2]-[date1]+1( END AS [z_COUNT]

It's giving me error as,

IErrorInfo.GetDescription failed with E_FAIL(0x80004005).

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: IErrorInfo.GetDescription failed with E_FAIL(0x80004005).

Stack Trace:

[OleDbException (0x80004005): IErrorInfo.GetDescription failed with E_FAIL(0x80004005).] System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +267 System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +192 System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +48 System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +106 System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) +111 System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +4 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +141 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +86 QueryFunctions.processSelect(OleDbCommand& cmd, DataSet& ds) in E:\www.asp.net\WebSites\Corporate\LeaveAppModule\App_Code\QueryFunctions.vb:32 Report_LeaveRecord.populateGridView() in E:\www.asp.net\WebSites\Corporate\LeaveAppModule\Report\LeaveRecord.aspx.vb:42 Report_LeaveRecord.Page_Load(Object sender, EventArgs e) in E:\www.asp.net\WebSites\Corporate\LeaveAppModule\Report\LeaveRecord.aspx.vb:13 System.Web.UI.Control.OnLoad(EventArgs e) +99 System.Web.UI.Control.LoadRecursive() +47 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1436

Answer 1

User-821857111 posted

You can't use CASE with Access.  It doesn't support it.  One way to do this is just retrieve the raw values, then use VB or C# in the RowDataBound event of the datacontrol to apply the logic.

 

Answer 2

User-1199946673 posted

Mikesdotnetting

You can't use CASE with Access.  It doesn't support it. 

That's correct, however the equivalent for the SQL Server CASE statement in Access is SWITCH....

-- SQL Server 
SELECT CASE  
    WHEN On=1 THEN 'On' 
    WHEN On=0 THEN 'Off' 
END FROM table 
 
-- or 

SELECT CASE On 
    WHEN 1 THEN 'On' 
    WHEN 0 THEN 'Off' 
END FROM table 

Translates in Access to:

SELECT Switch( 
    On=1,'On', 
    On=0,'Off' 
) FROM table

More information on the differences between Access and SQL Server can be found at:

Mike, maybe a new topic for your website?

Answer 3

User-821857111 posted

Mike, maybe a new topic for your website?

 

LOL.  I think Aaron has done a nice enough job on his site covering this topic.

Well, it's not actually "his" site any more.  He sold it a couple of years back...