Sort Question 2

[herd]

I have a query with a field titled "location", with data like -
Buld. 109
Buld. 17
Buld. 8

How can I sort this data sort it come out looking like this
Buld. 8
Buld. 17
Buld. 109
etc..........

Thanks Gary

 

[M8KWR]

you would have to use a query like the following but changing the table and column names accordingly

SELECT TableName.FieldName, Mid([FieldName],7,255) AS Expr1
FROM FieldName
ORDER BY Mid([FieldName],7,255) DESC;

This assumes that each field will have "Buld." at the start of it.

Hope this helps.

 

[herd]

It works, but how do you get them to apperar only once?

 

[PHV]

SELECT TableName.FieldName
FROM FieldName
ORDER BY Val(Mid([FieldName],7))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[M8KWR]

Hope this helps. It now only displays the one field also which i called New Location

SELECT Mid([Location],7,255) AS [New Location]
FROM Locationtab
GROUP BY Locationtab.Location, Mid([Location],7,255)
ORDER BY Mid([Location],7,255) DESC;

Any issues let me know.