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

    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?



  2. #2
    Join Date
    Mar 2003
    The Bottom of The Barrel
    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

    FORMAT() should probably be done at the client

    a billion thanks Teddy! 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?

    Last edited by Imatools; 08-21-08 at 03: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