Help! Sorting Numbers in Text Field in SQL Server

[johnsun]

Hi, I'm converting my database from Access to MSDE SQL 2000.. One problem that I ran acrossing is sorting numbers in a text field. I have to store numbers like 3, 3A, 3B, 4, 5, etc.. Is Access, I used to use:


SELECT * FROM [Item] WHERE CATID = "& CatID & "ORDER BY Val([LotNumber]) DESC, IIf(Val(Right$([LotNumber],1))=0,Right$([LotNumber],1),"""") DESC;"


But when I'm connect to the MSDE SQL database, it complains.. Is there anyway to sort this data the way I need it to?
Thanks
John

[Aaron Young]

Try something like this:

"SELECT * FROM [Item], Val([LotNumber]) AS Sort1, IIf(Val(Right([LotNumber],1))=0,Right([LotNumber],1),'') AS Sort2 WHERE CATID = " & CatID & " ORDER BY Sort1 DESC, Sort2 DESC"

[johnsun]

Hi, thanks for the reply.. Can't seem to get your SQL query to work.. Does SQL MSDE doesn't like the Val() function? I can't so a simple sort using Val(LotNumber).. It return an error..
Thanks
John