Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2008
    Posts
    14

    Unanswered: Upsizing MS-Access Query function (e.g IIF...) to SQL Server

    I have a live VB6 application that presently uses an MS-Access 2003 db for backend.

    I wish to upsize the db to MS-SQLServer.

    The challenge is I used Access specific functions so much in both the VB6 code and internal Access queries. These functions include:

    ...LEFT(x, n)
    ...MID(x, n1, n2)
    ...FORMAT(n, s)
    ...IFF(field1=x, value1, value2)

    For instance for an Access query "SELECT f1, IIF(f2 = 4, f3, f4) FROM table1"

    How may I manipulate SQL-Sever to run such queries?

    thanks

    Vie
    @Abuja

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You want "CASE"

    SELECT f1, CASE WHEN f2 = 4 THEN f3 ELSE f4 END

    LEFT() still translates directly over. MID() is SUBSTRING() and FORMAT() should probably be done at the client.
    oh yeah... documentation... I have heard of that.

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

  3. #3
    Join Date
    Jul 2008
    Posts
    14

    FORMAT() should probably be done at the client

    a billion thanks Teddy! ...as it is said, you're the best...access monkey...if you please.

    however just clearify me on the last line "FORMAT() should probably be done at the client".

    Now I know I can use VB2005 to create functions in SQLServer and I was thinking of creating @FORMATn(byval n as number, s as string) and @FORMATd(d as date, and s as string) functions in the sql db version.

    So in the implementation of the, say @FORMATn(...) I would just return

    ... format(n, s) and for the other it will be ... format(d, s)

    these implementations are now in VB2005 you get?

    Is that what you mean?

    Vie
    @Abuja
    Last edited by Imatools; 08-21-08 at 04:53.

Posting Permissions

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