Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2012
    Posts
    188

    Unanswered: Access SQL to SQL Server -- Is it possible?

    I am running this code in Access SQL
    Code:
    IIF([table1].[EmployeeDaysSick]=[table2].[TotalSickDays], [table1].[EmployeeDaysSick], IIF([table1].[EmployeeDaysSick] Like "4*", [table1].[EmployeeDaysSick], "This employee has avaliable sick days")) As [Sick Days Remaining]
    How would that be converted to be a valid SQL Server statement?

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    The IIF function can only be used from the 2012 version of SQL Server.
    With previous versions try this:

    Code:
    case when [table1].[EmployeeDaysSick] = [table2].[TotalSickDays]
        then [table1].[EmployeeDaysSick]
        else case when [table1].[EmployeeDaysSick] Like '4%' 
                then [table1].[EmployeeDaysSick]
                else 'This employee has avaliable sick days'
             end
    end As [Sick Days Remaining]
    Hope this helps.

  3. #3
    Join Date
    Feb 2012
    Posts
    188
    Quote Originally Posted by imex View Post
    The IIF function can only be used from the 2012 version of SQL Server.
    With previous versions try this:

    Code:
    case when [table1].[EmployeeDaysSick] = [table2].[TotalSickDays]
        then [table1].[EmployeeDaysSick]
        else case when [table1].[EmployeeDaysSick] Like '4%' 
                then [table1].[EmployeeDaysSick]
                else 'This employee has avaliable sick days'
             end
    end As [Sick Days Remaining]
    Hope this helps.
    This is giving me a message of:
    The text, ntext, and image data types can not be compared or sorted, except when using IS NULL or LIKE operators.

  4. #4
    Join Date
    Apr 2012
    Posts
    213
    Which the data type of the columns [table1]. [EmployeeDaysSick] and [table2]. [TotalSickDays]?
    What values ​​do you want to identify with the condition ' [table1]. [EmployeeDaysSick] Like "4*" '?

  5. #5
    Join Date
    Feb 2012
    Posts
    188
    Quote Originally Posted by imex View Post
    Which the data type of the columns [table1]. [EmployeeDaysSick] and [table2]. [TotalSickDays]?
    What values ​​do you want to identify with the condition ' [table1]. [EmployeeDaysSick] Like "4*" '?
    Ah...I was trying to join a memo to a text field. I Used CONVERT to force them to match. Thank you for the assistance.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •