Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Convert Access Query to SQL

    Hi,

    I am looking into to swapping one of access queries to a SQL view for better integration into our database viewer software app on the iPAD's.

    However I am not familiar with the sql language.

    How would I convert the following to a SQL view:

    AskingPrice: IIf([pricequalify]="asking Price",Format([Price],"#,##0"),[QualifyAb] & " " & Format([Price],"#,##0"))

    and

    Viewer: IIf([tblPurchaser]![PPLastname]=[tblPurchaser]![LASTNAME],[tblPurchaser]![TITLE] & " and " & [tblPurchaser]![PPTitle] & " " & [tblPurchaser]![LASTNAME],[tblPurchaser]![TITLE] & " " & [tblPurchaser]![LASTNAME] & IIf(IsNull([tblPurchaser]![PPLastname]),""," and " & [tblPurchaser]![PPTitle] & " " & [tblPurchaser]![PPLastname])) & " / " & [mobiletel] & IIf(IsNull([PPMobile]),""," / " & [PPMobile])

    Any help would be greatly appreciated.
    Best Regards,

    Luke

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    With any Access query you can look at it as SQL just by looking at the query in SQL view. How you do that varies a bit with the version of Access.

    Here is where it is in Access 2010.
    ViewSQL.gif
    Regards
    John



  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I presume by "SQL view" you mean you are using data from another database engine than Access. In ANSI SQL you don't have the IIF() function that Access provides, so the typical solution is to use the CASE statement. Unfortunately the syntax may vary from vendor to vendor. So we need to know whether you are working with mySQL, Oracle, SQL Server or some other database backend.
    Wendell

  4. #4
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am using Ms SQL 2005
    Best Regards,

    Luke

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    In order to convert those expressions to T-SQL, you need to use CASE statements in SQL Server 2005, but you will need to consult with the SQL Server Books Online to see the syntax for doing formatting currency as Sterling. The CASE statement is similar to the Select Case construct in Access VBA.
    Wendell

  6. #6
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Manchester, United Kingdom
    Posts
    116
    Thanks
    8
    Thanked 17 Times in 16 Posts
    If I may jump in here, I have spent ages converting Access SQL to the T-SQL used by MS SQL Server and I feel your pain Lstclair! As WendellB says, the big thing is to use CASE statements instead of IIF, but there are a load of common conversion issues illustrated by your question. So here goes with a long answer.

    Your first expression is
    Code:
    AskingPrice: IIf([pricequalify]="asking Price",Format([Price],"#,##0"),[QualifyAb] & " " & Format([Price],"#,##0"))
    And that can be put into a SQL view like this:
    Code:
    SET QUOTED_IDENTIFIER ON;
    
    GO
    
    CREATE VIEW vwSomethingOrOther AS
      SELECT
        CASE WHEN "pricequalify" = 'asking Price' THEN
          Format("Price",'#,##0')
        ELSE
          "QualifyAb" + ' ' + Format("Price",'#,##0')
        END AS AskingPrice,
        other_field_or_expression AS other_result_field
      FROM source_table_or_join_expression
      WHERE selection_criteria;
    
    GO
    That still won't work though, because T-SQL has no Format() function. See later.

    The above illustrates several things:

    1. The syntax for creating a view. Note that once created the view lives in the database like an Access query. If you want to change it later, replace CREATE with ALTER.

    2. T-SQL uses double quotes not square brackets to "escape" identifiers. You only need to do that if your identifiers have spaces or other characters T-SQL doesn't allow. As yours don't, you could omit the double quotes and the SET QUOTED_IDENTIFIER.

    3. If QUOTED_IDENTIFIER is ON, you must use single quotes for string literals. That is good practice anyway and it also works in Access. Note you can put double quotes inside a single quoted string literal without escaping them, but if you include an apostrophe you must escape it by using two apostrophes.

    4. +, not &, is the string concatenation operator in T-SQL

    5. Not illustrated above is the ISNULL problem you would hit in your second expression. In T-SQL there is a function ISNULL() but it actually replaces a NULL value with some other value, like Access NZ(). To get the effect of Access SQL IIF(IsNull(field),expression1, expression2) you need something like

    Code:
    CASE WHEN field IS NULL THEN expression1 ELSE expression2 END
    Also, if you need to qualify a field name with a table name use a dot, like table1.field1 or, if you need to quote it, "table 1"."field 1".

    (digression: I think it really pays to make field names unique, by adopting a convention of using short table-name prefixes on field names, like tblAddresses.adrStreet. That "adr"is faster to type and much shorter than "tblAddresses." but still allows you (and SQL) to work out which table contains the field. When displaying results you can put "user friendly" names in the front-end form)

    Now the Format(value, codes) problem.

    To quote someone else on the subject "why would you want to format text? T-SQL is for storage, use a front-end for formatting". That quote isn't the whole story, because I use the database for formatting all the time when using MySQL, which has a plethora of formatting functions to help me do that, but it does seem to be the T-SQL philosophy.

    T-SQL has a function CONVERT which changes datatypes and has limited string-formatting ability. You could use something like this:

    Code:
    '' + substring(CONVERT(varchar(100), CONVERT(money, numFloat), 1), 1, len(CONVERT(varchar(100), CONVERT(money, numFloat), 1)) - 3)
    You wouldn't want to type that very often, so you could write a user defined function in T-SQL to do it

    Code:
    create function FormatMoney (@Float float) returns varchar(100)
      begin
        return '' + substring(CONVERT(varchar(100), CONVERT(money, @Float), 1), 1, len(CONVERT(varchar(100), CONVERT(money, @Float), 1)) - 3);
      end;
    go
    What the above does incidentally is to convert numFloat, which is a float type, to a money type, then convert that to a varchar string using format 1 which is like 1,234.56 and finally strip off the .56 using substring. If your money values are already stored as money types you have an easier time of it. There may be an easier way to do it, I always format in the front end when using MS SQL.

    And finally (!) a big disclaimer. In the above examples I've skipped over issues around schemas. I know that when I tested the "create view" with the FormatMoney function T-SQL insisted I use dbo.FormatMoney, there may be other issues in your setup.

    Hope I have helped, not confused, you

    Ian.
    Last edited by iansavell; 2012-02-09 at 07:35.

Posting Permissions

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