Sort of complext text field

[mongaro]

Dear all,
I found some interesting suggestions in the forum but I didn't solve my problem completely.

I have a form that collect the activities related to a single project.
Each activity has a IDPh text field where the user add the number of the phase. (1.0;1.1; 2.0;etc.)
The user is free to select the format: like 1.0;1;01; etc.
Of course he should keep a certain coerence, without mixing different formats.

In any case, I have the common problem to sort correctly this field and I partially solved by the function CInt but the result doesn't satisfy me completely. Infact, if the user write activities like :

1; 1.1; 1.1.1; 1.2
with CInt I will obtain the following result:
1; 11; 111; 12

with a wrong activities order.

To solve I should force the user to type in this way:

1.0.0;1.1.0;1.1.1;1.2.0

then with Cint: 100;110;111;120.

The problem is that the user is not alwsy so accurate to write the numbers.
Any idea to improve?
thanks a lot.
Marco

 

[spikepl]

AS long as you allow free format then I don't se ehow you can sort on this thing. Without free format you have to decide in advance if you need

1.01.01 or 1.001.001 etc. depending an how many different itemes there are. If you can, then use the forms input mask to get required format. With fixed format the thing will sort correctly as string.

But, this type of thing should not be typed at all but selected from a list/combobox. Then you can control the format for display, and some other number for sorting, if the display format is inadequate for sorting.

 

[WayneRyan]

m,

Sort by using an invisible field in your QUERY that is formatted like --> 001.002.001.012
the field in your query = SortParagraph: fnParagraphNumber([YourParagraphNumber]) ... it doesn't have to be visible,
just use it for sorting.

Write a Public Function to do the sort for you:

Public Function fnParagraphNumber(Paragraph As String) As String
Dim varArray As Variant
Dim i As Integer
Dim Temp As String

Temp = ""

varArray = Split(Paragraph, ".")

For i = 0 To UBound(varArray)
   If i > 0 Then
      Temp = Temp & "." & Format(varArray(i), "000")
   Else
      Temp = Format(varArray(i), "000")
   End If
   Next i

fnParagraphNumber = Temp

End Function

Wayne