Manipulating field data

[bowldog]

505681552
149934515
574883717

With these 3 records I would like to do the following, 1- order the contents of each record, and 2- remove duplicates in each record. Results to look like

012568
13459
134578

Is this possible?

Thanks in advance for any help.

 

[Jon K]

You can use the InStr() function to test for the existence of the digits. Type/Paste the following public function in a new Module and save the module as any name you like.

Public Function getDigits(Num As Variant) As Variant
  Dim Digit As Variant
   
  Digit = IIf(InStr(Num, "0"), "0", Null) & IIf(InStr(Num, "1"), "1", Null) _
        & IIf(InStr(Num, "2"), "2", Null) & IIf(InStr(Num, "3"), "3", Null) _
        & IIf(InStr(Num, "4"), "4", Null) & IIf(InStr(Num, "5"), "5", Null) _
        & IIf(InStr(Num, "6"), "6", Null) & IIf(InStr(Num, "7"), "7", Null) _
        & IIf(InStr(Num, "8"), "8", Null) & IIf(InStr(Num, "9"), "9", Null)
   
   getDigits = Digit
End Function

Then in the query grid you can use the function like this (replace with the correct field name):-

Field: Digits: getDigits([FieldName])
.

 

[bowldog]

Thank you,thank you

Man,you guys are awesome. Thanks again,it worked great.(Great forum BTW)

 

[sfreeman@co.mer]

Nothing wrong with that, but this seems to work too...

Public Function getDigits(Num As Variant) As Variant
Dim i As Integer
For i = 0 To 9
getDigits = getDigits & IIf(InStr(Num, i), i, Null)
Next i
End Function

 

[EMP]

This can directly work on a query:-

Digits: IIf(InStr([FieldName],"0"),"0",Null) & IIf(InStr([FieldName],"1"),"1",Null)
& IIf(InStr([FieldName],"2"),"2",Null) & IIf(InStr([FieldName],"3"),"3",Null)
& IIf(InStr([FieldName],"4"),"4",Null) & IIf(InStr([FieldName],"5"),"5",Null)
& IIf(InStr([FieldName],"6"),"6",Null) & IIf(InStr([FieldName],"7"),"7",Null)
& IIf(InStr([FieldName],"8"),"8",Null) & IIf(InStr([FieldName],"9"),"9",Null)


It's kind of strange. It seems all three options take approximately the same amount of time to run.