Want to sort the output to a XML file using VBA

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

Answer 1

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;"

Answer 2

Hi Stefan

Thanks for helping

I replaced my code with yours and get Compile Error.

---

Cheers // Peter Forss Stockholm

Answer 3

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.

Answer 4

Stefan

Thank you very, very much!

---

Cheers // Peter Forss Stockholm