Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2007

    Unanswered: Converting Access SQL to T-Sql

    Any help converting the following sql to T-Sql would be helpful. I created it in Access ant works great but cant get the case to work. Need to put it into a accounting program that uses T-Sql. The purpose it to come up with a new field called STATUS based on key words in the "decoded" column.


    Status: IIf([TableName]![ColumnName] Like "*PA'D*","PA'D",IIf([TableName]![ ColumnName] Like "*SOLD*","SOLD",IIf([TableName]![ ColumnName] Like "*DNU*","DNU","ACTIVE")))

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    case when TableName.ColumnName Like '%PA''D%'
         then 'PA''D'
         when TableName.ColumnName Like '%SOLD%'
         then 'SOLD'
         when TableName.ColumnName Like '%DNU%'
         then 'DNU'
         else 'ACTIVE' end   as Status | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2003
    The Bottom of The Barrel
    Provided Answers: 1
    There is a saying that data should be stored in your tables, not in your code. Is there a specific reason you do not have a table for these status codes that you can then reference in your queries? What if one of those status codes changes or a new code needs to be added?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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