none
Want to sort the output to a XML file using VBA RRS feed

  • Question

  • Hi

    I have data in a temp table and can export it to a XML-file. Everything run just great, but not the sorting of the output to XML-file.

    This line of code works in its content of other code rows:

    orderBodyQuery = "SELECT distinct DesignationOfOrigin, BodyRecordUniqueReference, ExciseProductCode, CnCode, Quantity,  NetWeight, AlcoholicStrength, DegreePlato,  FROM " + mainTable + " WHERE [" + uniqOrderColumn + "] = """ + Order + """"

    I can’t figure out how to sort the output. This is what I want:

    I want is to have the orderBodyQuery sorting the data by content in “BodyRecordUniqueReference”, ascending order.

    Any ideas?


    Cheers // Peter Forss Stockholm

    Wednesday, September 5, 2018 8:22 AM

Answers

  • First of all: This should not work, cause it is incorrect SQL, error at the end of the SELECT column list.

    As sets are unordered per definition, add an appropriate ORDER BY clause. E.g.

    orderBodyQuery = _
      "SELECT DISTINCT DesignationOfOrigin, BodyRecordUniqueReference, ExciseProductCode, CnCode, Quantity,  NetWeight, AlcoholicStrength, DegreePlato " & _
      "FROM " + mainTable + " " & _
      "WHERE [" + uniqOrderColumn + "] = '" + Replace(Order, "'", ""'") + "' " & _
      "ORDER BY BodyRecordUniqueReference ASC;"

    • Marked as answer by ForssPeterNova Thursday, September 6, 2018 5:34 AM
    Wednesday, September 5, 2018 9:44 AM
  • A typo. Obviously. Should be

    Replace(Order, "'", "''")

    You need to replace any single quote in the order string with two of them to escape them.

    p.s. do yourself a favor and use the line continuations as I did.

    • Marked as answer by ForssPeterNova Thursday, September 6, 2018 5:34 AM
    Wednesday, September 5, 2018 1:43 PM

All replies

  • First of all: This should not work, cause it is incorrect SQL, error at the end of the SELECT column list.

    As sets are unordered per definition, add an appropriate ORDER BY clause. E.g.

    orderBodyQuery = _
      "SELECT DISTINCT DesignationOfOrigin, BodyRecordUniqueReference, ExciseProductCode, CnCode, Quantity,  NetWeight, AlcoholicStrength, DegreePlato " & _
      "FROM " + mainTable + " " & _
      "WHERE [" + uniqOrderColumn + "] = '" + Replace(Order, "'", ""'") + "' " & _
      "ORDER BY BodyRecordUniqueReference ASC;"

    • Marked as answer by ForssPeterNova Thursday, September 6, 2018 5:34 AM
    Wednesday, September 5, 2018 9:44 AM
  • Hi Stefan

    Thanks for helping

    I replaced my code with yours and get Compile Error.


    Cheers // Peter Forss Stockholm

    Wednesday, September 5, 2018 1:39 PM
  • A typo. Obviously. Should be

    Replace(Order, "'", "''")

    You need to replace any single quote in the order string with two of them to escape them.

    p.s. do yourself a favor and use the line continuations as I did.

    • Marked as answer by ForssPeterNova Thursday, September 6, 2018 5:34 AM
    Wednesday, September 5, 2018 1:43 PM
  • Stefan

    Thank you very, very much!


    Cheers // Peter Forss Stockholm

    Thursday, September 6, 2018 5:34 AM