sorting question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Greetings

I have a select query with a field containing text values like "xy7c_34dd".
I'd like have a calculated field that 'pulls out' any digits 0 to 9 and sorts
them as INTEGERS, not text. Values like this before sorting ...

xy7c_34d d
y 10w7c-aq
qr_ 107c-a q4
zz_hhq

.... after sorting becomes ...

zz_hhq
y 10w7c-aq 107
xy7c_34d d 734
qr_ 107c-a q4 1074

Can this be done as a calculated field, or do I need code (fairly new at
this, so help is much appreciated)
 
You'll need a function, along the lines of the following untested air-code:

Function ExtractValue(InputText As Variant) As Long
Dim intLoop As Integer
Dim strCurrChar As String
Dim strDigitsOnly As String

If IsNull(InputText) = False Then
For intLoop = 1 To Len(InputText)
strCurrChar = Mid(InputText, intLoop, 1)
If strCurrChar >= "0" And _
strCurrChar <= "9" Then
strDigitsOnly = strDigitsOnly & strCurrChar
End If
Next intLoop

If Len(strDigitsOnly) > 0 Then
ExtractValue = CLng(strDigitsOnly)
End If
End If

End Function

You can then use that function in your calculated field.
 
Hi Sophie,

You can use the rgxReplace() function at
http://www.j.nurick.dial.pipex.com/Code/index.htm in a query. Replacing
"\D" with "" will strip out everything except the digits, so the basic
expression will be like this:

rgxReplace([MyField], "\D", "")

But this returns an empty string if there are no digits in the value,
which will screw up the numeric sorting. To fix that, you could do
something like this

ORDER BY CLng("0" & rgxReplace([MyField], "\D", ""))

which will sort the digit-less values as 0.

You may find the resulting query runs quite slowly. Using the Persist
argument of rgxExtract may speed it up a bit. If speed is important and
the data is not continually updated, consider adding a number field to
the table, indexing the field, and usin rgxExtract in an update query to
populate it as often as required.
 
Back
Top