Post reply

Convert Access query to sql query

  • March 10, 2009 at 9:54 am

    #201225

    How to convert this to SQL query:

    IIf([Field4] Like ("*,*"),Left([Field4],InStr(1,[Field4],",")-1),IIf([Field4] Like ("*III"),Left([Field4],Len([Field4])-3),Left([Field4],Len([Field4])-2)))

  • March 10, 2009 at 11:10 am

    #956960

    Look at the SQL Server 2005 Books Online (September 2007)

    CASE (Transact-SQL)

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/658039ec-8dc2-4251-bc82-30ea23708cee.htm

    C. Using CASE to replace the IIf function that is used in Microsoft Access

    CASE provides functionality that is similar to the IIf function in Microsoft Access. The following example shows a simple query that uses IIf to provide an output value for the TelephoneInstructions column in an Access table that is named db1.ContactInfo.

    SELECT FirstName, Lastname, TelephoneNumber,

    IIf(IsNull(TelephoneInstructions),"Any time",

    TelephoneInstructions) AS [When to Contact]

    FROM db1.ContactInfo

    The following example uses CASE to provide an output value for the TelephoneSpecialInstructions column in the AdventureWorks view, Person.vAdditionalContactInfo.

    USE AdventureWorks

    GO

    SELECT FirstName, Lastname, TelephoneNumber, 'When to Contact' =

    CASE

    WHEN TelephoneSpecialInstructions IS NULL THEN 'Any time'

    ELSE TelephoneSpecialInstructions

    END

    FROM Person.vAdditionalContactInfo

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply