Hi!

I have problem sorting my table. It contains both text and numeric data. I made a links which could be used to sort the table by clicking the column title. I have problems sorting the numeric columns (for example 110 was smaller than 98). After a quick search with google, I found out that I should use COLLATE NUMERIC with the ORDER BY-clause. So I made the following select case:

-----------------------------------
sort = Request.QueryString("SortBy")

select case sort

case ""
sql = "SELECT * FROM Autot"

(this is for text-columns)
case "Aureknro", "Aumerkki", "Aumalli", "Aukori", "Auvari"
sql = "SELECT * FROM Autot ORDER BY "& sort &""

(this is for numeric-columns, and it doesn't work)
case "Auvm", "Auajetutkm", "Auhinta"
sql = "SELECT * FROM Autot ORDER BY "& sort &" COLLATE NUMERIC"

end select
-----------------------------------

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Auvm COLLATE NUMERIC'.

Sounds like you have your numeric values in character fields in your table. You shouldn't have to change collations to do this type of sorting. Double check your table "Autot" and make sure columns "Auvm," "Auajetutkm," and "Auhint" have numeric datatypes.

Thanks Seppuku!