Sorting: Extracting numbers from string? (1 Viewer)

[ISDans]

Hi all,

Relative beginner at Access stuf here.. but at work they saw fit to let me fix a small existing db that is currently malfunctioning. Most stuf I've got working but I'm running into one problem I'm not sure how to solve.

I've got a form that needs to be sorted numerically on data from column A from table dataset containing strings. The strings consist of both letters and numbers. No uniform order or number of characters. Sou we could get strings like: v81, v100, v10, v01, v2, 11.0047, 2009/129, 2009-129, 2007.212, 2012.05.08Z

My predecessor tried to solve this by creating a B in the forms source like this:

SELECT dataset.organisatie, dataset.AIP, dataset.EDNA, dataset.dataset, dataset.A, IIf([a]<>"",Val([A])) AS B, dataset.C, dataset.D, dataset.E, dataset.PID FROM dataset;

This obviously won't work since it will not recognise de v numbers and sorting the string as is will give you 10, 100, 20, 200 etc instead of 10, 20, 100, 200

So I guess I need to extract the numerical data somehow and sort on that... I haven't got a clue how to do it though. Can it be done by queries or is it better to resort to visual basic for instance? Anyway any sugestions would be more than welcome

 

[Brianwarnock]

It depends on how you want to extract the numbers, for example a simple extraction function such as this

Public Function ExtractNumeric(strInput) As String
'http://www.utteraccess.com/forum/Creating-Unique-Property-t1964859.html

' Returns the numeric characters within a string in
' sequence in which they are found within the string

Dim strResult As String, strCh As String
Dim intI As Integer
If Not IsNull(strInput) Then
For intI = 1 To Len(strInput)
strCh = Mid(strInput, intI, 1)
Select Case strCh
Case "0" To "9"
strResult = strResult & strCh
Case Else
End Select
Next intI
End If
ExtractNumeric = strResult

End Function

gives

10
100
81
01
2
110047
2009129
2009129
2007212
30120508

CLng will convert this to proper numbers.

Brian

 

[gemma-the-husky]

note that if the numbers are leading, you can use the val function

val (20.5v) is 20.5
val (17s) is 17

unfortunately, this doesn't work with leading alphas, and there is no intrinsic function for leading alphas

 

[Brianwarnock]

Which is why he needs a function such as the one posted, but he may also have to decide how complex he wants to take it.

Brian

 

[ISDans]

Hi guys,

Thnx very much for the help! Starting to get an idea of how al this works because of it. It's quite hard being trown into the deep like this when my last programming experience was 8 years ago and to make it even worse quite limited.

Unfortunately I've got leading alpha's and they are here to stay... We're talking about publication referencecodes imported from different companies directly into the dataset table. That's why I've got loads of different formats in one column that al need to be sorted correctly. I probably would have tried to avoid this situation in my database design and import procedure but unfortunately this is what they handed me...

The code Brian gave works wonders on most reference codes. Looking at it again I'm affraid it's a bit more complicated than that though. The companies don't enter their own codes consistently *sight*. It is quite common to leave out a 0 or switch from . to - or / as a separator.

So a company could deposit this:
2003.001
2003.1
2003/10
2003-100

another:
2010-12-100
2010.5.07
2012.05.08Z

Record should be sorted they way they are listed above. Obviously Brians code can't do that. And I guess this would be hard converting the field to one number? Or am I mistaken.
So probably something that creates a different variable if a - . or / occurs and sorting on the first then second then third (we don't get more than three parts to a numerical code secion)

And just for the record she-noob talking here not a he

 

[Brianwarnock]

The examples chosen will almost sort in that order as it will be a character not a numeric sort, the one dissent is the order of the first two in the second set is reverserd as "." comes before"/" comes before "-"

I don't know how you would achieve what you want.

Brian