sort text field as number

[tagteam]

I have a form with a field called Rider on it. Unfortunately, the bosses have done most of the riders in numbers and a few with letters in it so I can't make it a numeric field. They have said they will stop using letters but there are existing contracts and who knows, they may keep doing it.



Accounting wants to sort the report numerically by rider but that obviously doesnt work.

Most of the riders are numeric. 1, 2, 7, 8.0, 8.2, 9.1 etc, however, a few of them are like 5R.

I am looking for a way to split that or convert it and sort numerically. I kind of don't even know where to start because a left or right formula wont work because they are different lengths. I was thinking maybe I could do some kind of if statement but I don't know how to say =iif(rider has a letter in it, etc

As you will see below some riders have decimals, some don't, some have letters etc.

Any ideas on how to approach this?

For reference, these are all the unique riders we have:

9.1
9
8.2
8.1
8
7.5
7.4
7.3
7.2
7.1
7
6.2
6.1
6
5R
5.2
5.1
5
4.3
4.2
4.1
4
33
32
31.1
3.4
3.3
3.2
3.1
3
24.2
23.1
23
20
2.2
2.1
2
1X
1-E
1-D
17.1
15
14.1
14
13.1
13
12.1
12
11.2
11.1
11
10.2
10.1
10
1.3
1.2
1.1
1

[Micron]

One way would be to split the values if all the letters were at the beginning but you say they are not, so I'd suggest trying a sort on Left([Rider],1) or change 1 to something that gets you closer. I suppose that fixing the data to be numeric only is a non-starter?

EDIT - a quick test indicates that a sort using Left won't be good enough. Back to the drawing board...
Are there too many records to fix after you add a sorting field and manually enter sorting values?

[Gicu]

Please have a look at the attached, should do what you're after.

Cheers,

[tagteam]

I believe I have a working solution.
I created a new field in the query called NumRider. Then I used the base formula Expr1: IIf([Fieldname] Is Null, 0, Val([Fieldname])) (with my values of course)

This in essence created a new field that was a number and dropped any letters. On the report I kept the old ridernumber field on display and make the new NumRider field white to match the background.
So in the end it sorts by NumRider which is hidden and displays RiderNumber which still has the letters in it.

So far in my test cases it seems to be working fine.

[Gicu]

Sorting on Val itself will ignore the letters so you could have 1X before 1-D, have you had a chance to look at my upload? I sort on the Val and Right(FieldName,1) if that is alpha.

Cheers,

[tagteam]

Thanks Gicu for the suggestion. I am looking at it right now to see if it will also do what I want and / or use it for future needs.

[Gicu]

An alternative way that would not be dependant of the length or positioning of the alpha character(s) would be to simply create a new "mapping" table to hold the "original" field value (1X, 1-D,1-E,1.1, etc.) and the corresponding new numeric-only values (1.9,1.5,1.6,1.1). Then it is just a matter of adding this new table to your queries and sort on the numeric-only field. See Query2 in the updated upload.

Cheers,

[Micron]

Try as I might I still continue to struggle with RegEx. However, this is what I came up with (Vlad, sorry if it's a dupe of yours, I didn't want to have to download and examine code just to find out).

Public Function OnlyDigits(ByVal strIn As String) As String
Dim i As Integer

For i = 1 To Len(strIn)
     If Mid(strIn, i) = "" Then Exit For
     If Not IsNumeric(Mid(strIn, i, 1)) Then strIn = Replace(strIn, Mid(strIn, i, 1), "")
Next
OnlyDigits = strIn

End Function

EDIT - query calls as

SELECT onlydigits([Rider]) AS expr1
FROM tblRider
ORDER BY onlydigits([Rider]);

[Gicu]

Wouldn't this return the same value for 1X, 1-E and 1-D? I took the opposite approach, I check if the last character is alpha, isolate that in a new query field, get the numeric value using Val(Rider) and finally sort first on numeric then on alpha. But if the alpha is in a different position it won't work so I think the suggestion in my previous post would be the most accomodating.

Cheers,

[Micron]

It probably should, but didn't. I suspect that is because of the diminishing length and the limited field lengths. To answer specifically, no, not according to what I'm seeing (partial list). BTW, did you account for values like A123? None of the sample records start with a letter, but that's not to say none do or never will, so Val(Rider) could produce undesired results?

Rider
1X
1
10
10.1
10.2
1.1
11
11.1
11.2
1.2
12
12.1
1.3
13
13.1
14
14.1
15
17.1
1-D
1-E
2

[Gicu]

In the first post the OP mentions that that is the full list of existing values so my initial solution was based on that assumption. I still think the additional table approach would be the most accommodating.
Cheers,