Sorting Text as a number in Access (1 Viewer)

[KSDavis]

Is there a way to sort text as numbers in access without omitting the data that contains text values? I am currently trying to sort by operation which includes the values 1,2,2A,3,10,11,12,....ect. When I currently sort these values because the are stored as text they appear as 1,10,11,12,2,2A,3.......ect. I have tried using the expression Expr1: IIf([Operation] Is Null, 0, Val([Operation])) but it omits the operations that contain text values such as 2A. Is there a way to sort this information correctly?

 

[The_Doc_Man]

When sorting from an Access table, the numbers are stored according to the field type of the field in which they appear. If they are stored as text, they should sort as text and not omit anything. However, "VAL()" attempts to form a numeric result, and "VAL(2A)" will return the value 2 (NOT as text) when you sort it.

Using "[Operation] Is Null" is also a little off. Instead of the IIF, use "Nz([Operation],'0')" to provide an alternative value. AND if you are sorting text, the IIF would have ALSO returned a numeric value for null cases. Just like the "VAL(2A)" case, a text sort would have trouble with those numeric returned values.

 

[plog]

Yes, but its going to require you build a custom function.

First, why are you storing numerical data as text? And with text? If you want to use data as numbers, you need to store it as such. Most likely, your data needs to go into 2 fields--one to hold the numerical part and one the text part.

For the sorting of this poorly stored data, you need to build a function that returns just the number portion of your values. You would pass it the value, it would determine where the numerical data occurs within it, extract it and return that numerical data back to you so that you can then sort by it.

 

[KSDavis]

I agree the data should have been stored as a numerical value but unfortunately I am working with a pre-existing data base which stores information all the way back to 2012. When the data base was set up the operation field was set up as a text field in order to be able to include those text values and if I change the field type now it will delete all of the stored date which contains those values. Operation 2 and 2A are used to distinguish between two different machines so is there a way to sort this text how I want to be able to sort it and include this data? I appreciate your help as I am new to access and this is a learn as I go process for me.

 

[arnelgp]

you may try some experiment with sort order:

SELECT Table1.[field]
FROM Table1
ORDER BY IIF(ISNULL([field]),0,VAL(VAL([field]) & LEN([field]) & ASC(RIGHT([field],1))))

 

[static]

SELECT *
FROM Table1
ORDER BY Len([Operation]) & [Operation]

edit arnelgp's looks like it may do a similar thing more thoroughly.