Solved How to sort numbers that contain slashes? (1 Viewer)

[MajP]

You are likely going to want to do this more than one place in your DB. So for easy usability I like to make individual functions. When I call a function from a query I always make sure I can handle bad data especially nulls and then return either Null or an empty string if the data is bad.

Public Function GetPatientNumber(varInput As Variant) As String
Dim aInput() As String
If Not IsNull(varInput) Then
aInput = Split(varInput, "/")
If UBound(aInput) = 1 Then
GetPatientNumber = aInput(0)
End If
End If
End Function

Public Function GetInputYear(varInput As Variant) As Variant
Dim aInput() As String
If Not IsNull(varInput) Then
aInput = Split(varInput, "/")
If UBound(aInput) = 1 Then
GetInputYear = CInt("20" & aInput(1))
End If
End If
End Function

now my sql string is real easy

Order by getPatientNumber([someField]), getInputYear([someField])

It checks to make sure the input is not null, has one /, and has info on both sides of the /. Also the year is numeric (2020, 2021), make a little easier to read.

 

[Pat Hartman]

Just because data comes to you unnormalized doesn't mean that you can't fix it on the way in. So, you fix it ONCE or you futz with it every single time you need to use it.

As someone mentioned early on, 012 is a string but 12 is a number. You need to know what you are dealing with and you need to decide whether you can safely convert strings to numbers. If you can't convert strings to numbers, you need to know if the strings are fixed length and that is why they have leading zeros. If the string is max of three characters and ALWAYS has leading zeros - 012 or 009 to fill out the three characters, the field can be left as a string and will sort correctly. If the string is variable in length then the only way to make it sort correctly when leading zeros are not used consistently, then you need to right justify the field to a fixed length. To do this, you would concatenate leading spaces. so if the length is fixed at 6, you have bbb012 or bbbbb9 with the "b" = space. We can help with the code once you know your objective.

 

[NauticalGent]

You did not tell him what to replace!

The OP should be able to figure that part out when they google "Replace() + VBA"

 

[Gasman]

The OP should be able to figure that part out when they google "Replace() + VBA"

Most do not Google though? , just ask in forums. Especially the Olivers.
The number of times I Google their requirement and get a large amount of hits that show the way, I've lost count.
I do not know the amswer either, but a quick Google gets me the answer, or enough to work it out.