Sorting Values

[wjoc1]

Hi,

I have a field in a table which needs to store values such as the following:

1, 16, 20, 20A, 43, 100

Essentially they are numbers but some have a letter attatched to the end. Therefore I set the data type for this field to be text. However I still wanted the values ordered "numerically" but because this fields data type is text I can only order them alphabetically so the values above would be ordered as follows:

1, 16, 100, 20, 20A, 43

To overcome this problem I have the following function:

Public Function getPoleSortValue(poleValue As String) As String

Dim formattedPole As String
formattedPole = Format$(poleValue, "00000")

getPoleSortValue = formattedPole

End Function

This works fine and orders them numerically but it screws up any values which have a letter appended to the end. Typically these appear before all the other values. For example if I have the following list of values:

1, 2 ,3 , 3A

Then they get ordered as follows:

3A, 1, 2, 3

Just to throw this out there, on Forms/Queries where I use this it works fine but the ordering really only gets screwed up when I use this function with the 'sorting and grouping' of records on a report! This is driving me nuts.
How can I fix this? Is there some simple change I can make to the function or is am I doing it all wrong?
Basically can somebody show me a better way!

Thanks,
Liam

 

[shay]

Possible solution...

Why not use the Val function in a query whenever you want to display your values in numerical order. This should do it...

SELECT DISTINCTROW Val([OriginalValue]) as NewValue FROM [YourTable] ORDER BY Val([OriginalValue]);

shay