Removing duplicate characters from a word and rearranging it

[Kelvin Leong]

Hi there,

I have a field that stores 4 digit numbers as text. I would like these
numbers to be filtered so that the duplicate digits are removed and each
remaining digit is sorted ascendingly. I want to maintain the original
numbers and the filtered number, as the following, can be shown in a query or
report. The following are some exampls of numbers (as text) and the result
that I want:

1811 => 18
2929 => 29
0313 => 130
5359 => 359

If you noticed, the digits are arranged accending, except for the number 0
where it is put last. Is there a way to do so? Do I need to create a public
function so that I can use it in the query? Please help.

Thanks.

 

[mcescher]

Hi there,

I have a field that stores 4 digit numbers as text. I would like these
numbers to be filtered so that the duplicate digits are removed and each
remaining digit is sorted ascendingly. I want to maintain the original
numbers and the filtered number, as the following, can be shown in a query or
report. The following are some exampls of numbers (as text) and the result
that I want:

1811 => 18
2929 => 29
0313 => 130
5359 => 359

If you noticed, the digits are arranged accending, except for the number 0
where it is put last. Is there a way to do so? Do I need to create a public
function so that I can use it in the query? Please help.

Thanks.

Public Function FindDups(strDigits As String) As String
Dim intArrDups(9) As Integer, intPos As Integer
For intPos = 1 To 4
intArrDups(Val(Mid(strDigits, intPos, 1))) = 1
Next
For intPos = 1 To 9
FindDups = FindDups & IIf(intArrDups(intPos), CStr(intPos), "")
Next
FindDups = FindDups & IIf(intArrDups(0), "0", "")
End Function

 

[Kelvin Leong]

Thanks Chris...

I think it works. But I'm still testing it.