Tricky Sequencial re-numbering

[CJ_London]

I would have expected 8.1.5 to come out as 8010500 not 80105 - looks like you have set the subparalevels based on the current record, so changes for each record. In this example it should be 3 for each row

[Mick99]

Just doesn't seem to matter how I change it I just can seem to get it to pick up the last digits, I'm not very good with this. Any suggestions.

Cheers Mick

MASTERSORTTEST: Format(Int(Val([Agenda Item Number])),"0000") & Format(Val(Mid([Agenda Item Number],InStr([Agenda Item Number],".")+1)),"00.00.00")

8.1.5.7 turns into 000801.50.00

[Mick99]

Hi still can not get this to work, I have added some screen shot as I must be doing something wrong.




Cheers Mick

[IrogSinta]

Here's a revision to the function Ajax posted. Give it a try:

Public Function ParaSort(PNum As String, SubParaLevels As Integer) As Long
    Dim Strarray() As String
    Dim i As Integer
    Dim NStr As String
    
    Strarray = Split(PNum, ".")
    NStr = ""
    
    For i = 0 To UBound(Strarray)
        NStr = NStr & Format(Strarray(i), "00")
    Next i

    For i = UBound(Strarray) To SubParaLevels - 1
        NStr = NStr & "00"
    Next i

    ParaSort = Val(NStr)

End Function

Ron

[Mick99]

Hi Ron, I must be doing something wrong. I have just put together a sample DB attached of what I am needing to do. I figured it might be easier to work out what I am doing wrong if you have a working model.

Sequencial Numbering.accdb

Cheers Mick

[IrogSinta]

Well a big problem is the fact that you weren't making use of the ParaSort function in your query. Here's the SQL of what your query should look like:

SELECT [Agenda Item Number], ParaSort([Agenda Item Number],3) AS MASTERSORT FROM [tbl MASTER DATA];

Ron

[Mick99]

All good now, I was not calling the ParaSort at all as you pointed out. I used the updated code you gave me and all works a treat. A massive thankyou RON.

Cheers Mick