order items in combobox

sal21 · Post by **sal21** » 28 Feb 2011, 09:51

I have this list of items in combobobx1

DAL 31/01/2011 AL 04/02/2011 - (S)
DAL 24/01/2011 AL 28/01/2011 - (S)
DAL 17/01/2011 AL 21/01/2011 - (S)
DAL 10/01/2011 AL 14/01/2011 - (S)
DAL 03/01/2011 AL 07/01/2011 - (S)
DAL 01/01/2011 AL 31/01/2011 - (M)
DAL 27/12/2010 AL 31/12/2010 - (S)
DAL 20/12/2010 AL 24/12/2010 - (S)
DAL 13/12/2010 AL 17/12/2010 - (S)
DAL 06/12/2010 AL 10/12/2010 - (S)
DAL 01/12/2010 AL 31/12/2010 - (M)
DAL 29/11/2010 AL 03/12/2010 - (S)
DAL 22/11/2010 AL 26/11/2010 - (S)
DAL 15/11/2010 AL 19/11/2010 - (S)
DAL 08/11/2010 AL 12/11/2010 - (S)

S=Week
M=Month

how to order items with first param is the week and after second param the mont dates?

Post by **HansV** » 28 Feb 2011, 10:04

How is the combo box populated?

sal21 · Post by **sal21** » 28 Feb 2011, 10:11

HansV wrote:How is the combo box populated?

Code: Select all

With Me.COMBO_SETTIMANE
        .Clear

        Do While Not RS.EOF
        
            DATA_LUN1 = Mid(RS(0).Value, 7, 2) & "/" & Mid((RS(0).Value), 5, 2) & "/" & Mid((RS(0).Value), 1, 4)
            DATA_VEN1 = Mid(RS(0).Value, 16, 2) & "/" & Mid((RS(0).Value), 14, 2) & "/" & Mid((RS(0).Value), 10, 4)

            Y = DateDiff("d", DATA_LUN1, DATA_VEN1)

            If Y <= 5 Then
                D = "S"
            End If
            If Y > 5 Then
                D = "M"
            End If
            'Debug.Print "DAL " & DATA_LUN1 & " AL " & DATA_VEN1 & " - (" & D & ")"
            .AddItem "DAL " & DATA_LUN1 & " AL " & DATA_VEN1 & " - (" & D & ")"
            'Debug.Print "DAL " & DATA_LUN1 & " AL " & DATA_VEN1 & " - (" & D & ")"
            RS.MoveNext
            CONTA = CONTA + 1
        Loop
        
    End With

Post by **HansV** » 28 Feb 2011, 10:36

Do you want to sort the items in descending order, as in your example, or in ascending order?

sal21 · Post by **sal21** » 28 Feb 2011, 10:45

HansV wrote:Do you want to sort the items in descending order, as in your example, or in ascending order?

1) First order param is the two date in S and M strings
note:
in this case first all strings with S and last with M

2) from the little date to the big date

Final list:
DAL 08/11/2010 AL 12/11/2010 - (S)
DAL 15/11/2010 AL 19/11/2010 - (S)
DAL 22/11/2010 AL 26/11/2010 - (S)
DAL 29/11/2010 AL 03/12/2010 - (S)
DAL 06/12/2010 AL 10/12/2010 - (S)
...
DAL 01/12/2010 AL 31/12/2010 - (M)
DAL 01/01/2011 AL 31/01/2011 - (M)

Post by **HansV** » 28 Feb 2011, 10:57

So you want the strings with "M" to come after ALL strings with "S", like this?

DAL 08/11/2010 AL 12/11/2010 - (S)
DAL 15/11/2010 AL 19/11/2010 - (S)
DAL 22/11/2010 AL 26/11/2010 - (S)
DAL 29/11/2010 AL 03/12/2010 - (S)
DAL 06/12/2010 AL 10/12/2010 - (S)
DAL 13/12/2010 AL 17/12/2010 - (S)
DAL 20/12/2010 AL 24/12/2010 - (S)
DAL 27/12/2010 AL 31/12/2010 - (S)
DAL 03/01/2011 AL 07/01/2011 - (S)
DAL 10/01/2011 AL 14/01/2011 - (S)
DAL 17/01/2011 AL 21/01/2011 - (S)
DAL 24/01/2011 AL 28/01/2011 - (S)
DAL 31/01/2011 AL 04/02/2011 - (S)
DAL 01/12/2010 AL 31/12/2010 - (M)
DAL 01/01/2011 AL 31/01/2011 - (M)

sal21 · Post by **sal21** » 28 Feb 2011, 11:02

HansV wrote:So you want the strings with "M" to come after ALL strings with "S", like this?

DAL 08/11/2010 AL 12/11/2010 - (S)
DAL 15/11/2010 AL 19/11/2010 - (S)
DAL 22/11/2010 AL 26/11/2010 - (S)
DAL 29/11/2010 AL 03/12/2010 - (S)
DAL 06/12/2010 AL 10/12/2010 - (S)
DAL 13/12/2010 AL 17/12/2010 - (S)
DAL 20/12/2010 AL 24/12/2010 - (S)
DAL 27/12/2010 AL 31/12/2010 - (S)
DAL 03/01/2011 AL 07/01/2011 - (S)
DAL 10/01/2011 AL 14/01/2011 - (S)
DAL 17/01/2011 AL 21/01/2011 - (S)
DAL 24/01/2011 AL 28/01/2011 - (S)
DAL 31/01/2011 AL 04/02/2011 - (S)
DAL 01/12/2010 AL 31/12/2010 - (M)
DAL 01/01/2011 AL 31/01/2011 - (M)

perfect!

Post by **HansV** » 28 Feb 2011, 11:20

You need some additional declarations at the beginning of the procedure:

Code: Select all

    Dim strDataLun As String
    Dim strDataVen As String
    Dim strSort As String
    Dim arr1() As String
    Dim arr2() As String
    Dim i As Long
    Dim j As Long
    Dim tmp1 As String
    Dim tmp2 As String

and DATA_LUN1 and DATA_VEN1 must be declared as dates:

Code: Select all

    Dim DATA_LUN1 As Date
    Dim DATA_VEN1 As Date

Now change the code as follows:

Code: Select all

    ' Fill arrays
    CONTA = 0
    Do While Not RS.EOF
        strDataLun = Mid(RS(0).Value, 1, 8)
        DATA_LUN1 = DateSerial(Mid(strDataLun, 1, 4), Mid(strDataLun, 5, 2), Mid(strDataLun, 7, 2))
        strDataVen = Mid(RS(0).Value, 10, 8)
        DATA_VEN1 = DateSerial(Mid(strDataVen, 1, 4), Mid(strDataVen, 5, 2), Mid(strDataVen, 7, 2))
        Y = DateDiff("d", DATA_LUN1, DATA_VEN1)
        If Y <= 5 Then
            d = "S"
            strSort = "1" & strDataLun
        Else
            d = "M"
            strSort = "2" & strDataLun
        End If
        RS.MoveNext
        CONTA = CONTA + 1
        ReDim Preserve arr1(1 To CONTA)
        arr1(CONTA) = "DAL " & Format(DATA_LUN1, "dd\/mm\/yyyy") & _
            " AL " & Format(DATA_VEN1, "dd\/mm\/yyyy") & " - (" & d & ")"
        ReDim Preserve arr2(1 To CONTA)
        arr2(CONTA) = strSort
    Loop

    ' Sort arrays
    For i = 1 To CONTA - 1
        For j = i + 1 To CONTA
            If arr2(j) < arr2(i) Then
                tmp1 = arr1(i)
                arr1(i) = arr1(j)
                arr1(j) = tmp1
                tmp2 = arr2(i)
                arr2(i) = arr2(j)
                arr2(j) = tmp2
            End If
        Next j
    Next i
    
    ' Fill combo box
    With Me.COMBO_SETTIMANE
        .Clear
        For i = 1 To CONTA
            .AddItem arr1(i)
        Next i
    End With

sal21 · Post by **sal21** » 28 Feb 2011, 13:35

HansV wrote:You need some additional declarations at the beginning of the procedure:

Code: Select all

    Dim strDataLun As String
    Dim strDataVen As String
    Dim strSort As String
    Dim arr1() As String
    Dim arr2() As String
    Dim i As Long
    Dim j As Long
    Dim tmp1 As String
    Dim tmp2 As String

and DATA_LUN1 and DATA_VEN1 must be declared as dates:

Code: Select all

    Dim DATA_LUN1 As Date
    Dim DATA_VEN1 As Date

Now change the code as follows:

Code: Select all

    ' Fill arrays
    CONTA = 0
    Do While Not RS.EOF
        strDataLun = Mid(RS(0).Value, 1, 8)
        DATA_LUN1 = DateSerial(Mid(strDataLun, 1, 4), Mid(strDataLun, 5, 2), Mid(strDataLun, 7, 2))
        strDataVen = Mid(RS(0).Value, 10, 8)
        DATA_VEN1 = DateSerial(Mid(strDataVen, 1, 4), Mid(strDataVen, 5, 2), Mid(strDataVen, 7, 2))
        Y = DateDiff("d", DATA_LUN1, DATA_VEN1)
        If Y <= 5 Then
            d = "S"
            strSort = "1" & strDataLun
        Else
            d = "M"
            strSort = "2" & strDataLun
        End If
        RS.MoveNext
        CONTA = CONTA + 1
        ReDim Preserve arr1(1 To CONTA)
        arr1(CONTA) = "DAL " & Format(DATA_LUN1, "dd\/mm\/yyyy") & _
            " AL " & Format(DATA_VEN1, "dd\/mm\/yyyy") & " - (" & d & ")"
        ReDim Preserve arr2(1 To CONTA)
        arr2(CONTA) = strSort
    Loop

    ' Sort arrays
    For i = 1 To CONTA - 1
        For j = i + 1 To CONTA
            If arr2(j) < arr2(i) Then
                tmp1 = arr1(i)
                arr1(i) = arr1(j)
                arr1(j) = tmp1
                tmp2 = arr2(i)
                arr2(i) = arr2(j)
                arr2(j) = tmp2
            End If
        Next j
    Next i
    
    ' Fill combo box
    With Me.COMBO_SETTIMANE
        .Clear
        For i = 1 To CONTA
            .AddItem arr1(i)
        Next i
    End With

No words!

Eileen's Lounge

order items in combobox

order items in combobox

Re: order items in combobox

Re: order items in combobox

Re: order items in combobox

Re: order items in combobox

Re: order items in combobox

Re: order items in combobox

Re: order items in combobox

Re: order items in combobox