I use the following SQL:
SELECT tblProfilesStorage.StorageTime
FROM tblProfilesStorage
GROUP BY tblProfilesStorage.StorageTime, Right(" " & [StorageTime],4)
ORDER BY Right(" " & [StorageTime],4);
This works fine EXCEPT when there's a value with a decimal point. For
example, 6.5 will come after 599 instead of 6.
How can I revise this code to sort properly?
Thanks!!!
First of all, I can't see any reason to use a GROUP BY clause here.
You are selecting only one column out of your table. Although it is
syntactically permissible, GROUP BY only makes sense if you are
calculating sums or using some other aggregate function for multiple
rows containing the same value in the column to group by. If the table
contains duplicate values in that column, you can use SELECT DISTINCT
instead.
Second, in order to sort numerically, you need to ORDER BY the column
StorageTime without any of the formatting (assuming that StorageTime
is some numeric type); otherwise, if that column is stored as Text in
the table, you'll need to convert it to some numeric value, e.g. using
the CDbl() function.
Third, you aren't including the formatted StorageTime in your output
column list, so what good is it?
Fourth, if you do include it in your output list, you should give it
an alias so that Access doesn't show a column name such as "Expr1234".
Assuming that StorageTime is stored numerically, I would suggest the
following (incidentally, there is no need to prefix the field with the
table name if you are selecting from one single table):
SELECT StorageTime, Right(" " & StorageTime],4)
AS [Pretty Storage Time]
FROM tblProfilesStorage
ORDER BY StorageTime;
If you are storing StorageTime as Text for some reason, then you will
need to do something like the following:
SELECT StorageTime, Right(" " & StorageTime],4)
AS [Pretty Storage Time]
FROM tblProfilesStorage
ORDER BY CDbl(Nz([StorageTime],"0"));
The Nz() function should keep you from getting an error when
StorageTime contains null values.
Good luck!
