Sort the columns one at a time from the least-significant to the most-significant.

Hello

so you are saying that i can't sort 2 column like Microsoft excel. Right?

what akimark means is that you do two sorts, starting with the Least significant

this code creates a random list, then sorts by column 1 , then by column 0.  

result is sorted by column 0, then sorted by column 1 within the column 0 arrangement.  Just like excel.

Private Sub Command1_Click()
    With MSFlexGrid1
        .ColSel = 1
        .Sort = flexSortStringAscending
        .ColSel = 0
        .Sort = flexSortStringAscending
    End With
End Sub

Private Sub Form_Load()
    Randomize Timer
    For i = 1 To 10
        MSFlexGrid1.AddItem Format(Rnd() * 10, "00") & vbTab & Format(Rnd() * 10, "00")
    Next i
    
    MSFlexGrid1.RemoveItem 1
End Sub

Select allOpen in new window

example1.jpg

I think we are almost there.

If you look at the picture, first column, i have dates and second column, employe.

with this code:
 

With MSFlexGrid1
        .ColSel = 1
        .Sort = flexSortStringAscending
        .ColSel = 0
        .Sort = flexSortStringAscending
    End With

Select allOpen in new window

It did what you can see on the picture.

The fifth row should of been at the beginning, since it is from the 10 of Decembre.

Is that because it does not understand the format in the first column?
result.jpg

all columns are treated as text.  I would suggest that you create a hidden (zero width) column that contains sortable dates (yyyymmdd format).

Can i convert the text date to real date format?

>>Can i convert the text date to real date format?

Yes.  How are you populating the flexgrid?

The first thing to try is the CDate() function.

The information come from an access dbase, in the same format.

ex:
10 Decembre 2010......

This is the code i use:

 Dim sSQL2 As String
    Dim oRST2 As ADODB.Recordset
  Set oRST2 = New ADODB.Recordset
  Dim oConnect2 As ADODB.Connection
  Set oConnect2 = New ADODB.Connection


     oConnect2.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=" & Form4.txtBaseDe.Text

sSQL2 = "SELECT [Période],[Employé],[Description_du_produit],[Quantité_assignée_ajustée],[Montant_ajusté] FROM [Distribution_employé] Where " _
& "Période >= '" & ComDateDisponible.Text & "' and " _
 & "Période <= '" & ComDateDisponible2.Text & "'"

oRST2.Open sSQL2, oConnect2

If oRST2.BOF = False Then

Do Until oRST2.EOF
  With MSFlexGrid1
        'do the this if u have multiple field to add on your flexgrid
        .AddItem oRST2("Période") & vbTab & oRST2("Employé") & vbTab & oRST2("Description_du_produit") & vbTab & oRST2("Quantité_assignée_ajustée") & vbTab & oRST2("Montant_ajusté") & vbTab
  oRST2.MoveNext
  End With
Loop

Select allOpen in new window

The date = ("Période")

Then try adding a column to the query
Format ("Période", "yyyymmdd") As SortableDate

=======
Depending on the number of rows, you might get better performance using the TextMatrix property of the FlexGrid

Reference:
http://www.pbdr.com/vbtips/gen/Article-UsingFlexGrids(2).htm

Is it posible to convert column 0 to format "yyyymmdd" once in the grid?

yes.  You can also display the data in its sortable format by using the Format() function.  Doing this wouldn't require the extra hidden column.

I have a ruff time.

This does not work

Dim i As Integer
For i = Form10.MSFlexGrid1.Rows To 1 Step -1

   If Form10.MSFlexGrid1.TextMatrix(i, 0) > "" Then
           Form10.MSFlexGrid1.TextMatrix(i, 0) = Format(Form10.MSFlexGrid1.TextMatrix(i, 0), "ddmmyy")
   End If
Next i

Select allOpen in new window

It say subscript out of range

try this.  

Dim i As Integer
For i = Form10.MSFlexGrid1.Rows To 1 Step -1

   If Form10.MSFlexGrid1.TextMatrix(i, 0) > "" Then
           Form10.MSFlexGrid1.TextMatrix(i, 0) = Format(cdate(Form10.MSFlexGrid1.TextMatrix(i, 0)), "yymmdd")
   End If
Next i

Select allOpen in new window

I still have the same error on this part of the code:

If Form10.MSFlexGrid1.TextMatrix(i, 0) > "" Then

Select allOpen in new window

try this

Dim i As Integer
For i = (Form10.MSFlexGrid1.Rows -1) To 1 Step -1

   If IsDate(Form10.MSFlexGrid1.TextMatrix(i, 0)) Then
      Form10.MSFlexGrid1.TextMatrix(i, 0) = Format(cdate(Form10.MSFlexGrid1.TextMatrix(i, 0)), "yymmdd")
   End If
Next i

Select allOpen in new window

good, the code is good.

But even with the new format, still don't sort it properly.

Still roww 5
result2.jpg

You are not using the format I posted.  In order for text dates to sort properly, you must have year before month before day.

Ok, back to format yymmdd

But still in row 5
result3.jpg

did you do your sort after you converted the data?

This is the total code now.

Dim gg As Integer
For gg = (MSFlexGrid1.Rows - 1) To 1 Step -1

   If IsDate(MSFlexGrid1.TextMatrix(gg, 0)) Then
      MSFlexGrid1.TextMatrix(gg, 0) = Format(CDate(MSFlexGrid1.TextMatrix(gg, 0)), "dd mmmm yyyy")
   End If
 Next gg

With MSFlexGrid1
        .ColSel = 0
        .Sort = flexSortStringAscending
        .ColSel = 1
        .Sort = flexSortStringAscending
    End With

Select allOpen in new window

oupssss sorry, this code:

Dim gg As Integer
For gg = (MSFlexGrid1.Rows - 1) To 1 Step -1

   If IsDate(MSFlexGrid1.TextMatrix(gg, 0)) Then
      MSFlexGrid1.TextMatrix(gg, 0) = Format(CDate(MSFlexGrid1.TextMatrix(gg, 0)), "yymmdd")
   End If
 Next gg

With MSFlexGrid1
        .ColSel = 0
        .Sort = flexSortStringAscending
        .ColSel = 1
        .Sort = flexSortStringAscending
    End With

Select allOpen in new window

1/ assuming the [Période] is a true database Date type, why dont you sort on date as part of the sql query ? The records are added to the grid in date order.
1.1/ then you only have to sort on the one column Employe and it doesnt matter what format the date is in (it will be shown according to the individual PC settings I believe)

2/ the code you posted seems ok so maybe there is a Space at the end of the employee code for row 5 ?
will sort on Col0 (date) and then on Col1 (emp).  This will end up sorted by date within employee.  I added a trim to the emp code prior to adding to the grid.

2.1/ so you still may want to format the date prior to displaying in the grid, but the format can be as you wish to see as it is not needed to be sorted.  eg MSFlexGrid1.AddItem Format(oRST2("Période"), "dd mmmm yyyy") & vbTab & ....

oConnect2.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=" & Form4.txtBaseDe.Text

sSQL2 = "SELECT [Période],[Employé],[Description_du_produit],[Quantité_assignée_ajustée],[Montant_ajusté] FROM [Distribution_employé] Where " _
& "Période >= '" & ComDateDisponible.Text & "' and " _
 & "Période <= '" & ComDateDisponible2.Text & "'"
 
 sSQL2 = sSQL2 & " ORDER BY [Période]"

oRST2.Open sSQL2, oConnect2

If oRST2.BOF = False Then

Do Until oRST2.EOF
  MSFlexGrid1.AddItem oRST2("Période") & vbTab & Trim(oRST2("Employé")) & vbTab & oRST2("Description_du_produit") & vbTab & oRST2("Quantité_assignée_ajustée") & vbTab & oRST2("Montant_ajusté") & vbTab
  oRST2.MoveNext
Loop

With MSFlexGrid1
    .Col = 1 'the Employe field
    .Sort = flexSortStringAscending
End With

Select allOpen in new window

@Wilder1626

Based on your code, you want to see dates within employee name order.  Did you understand what we posted about the order of column sorting?

Hello all,

Thanks for your help.

I think i'm almost there with your help.

I have change the code to this now:

 Dim sSQL2 As String
    Dim oRST2 As ADODB.Recordset
  Set oRST2 = New ADODB.Recordset
  Dim oConnect2 As ADODB.Connection
  Set oConnect2 = New ADODB.Connection


   oConnect2.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=" & Form4.txtBaseDe.Text

sSQL2 = "SELECT [Période],[Employé],[Description_du_produit],[Quantité_assignée_ajustée],[Montant_ajusté] FROM [Distribution_employé] Where " _
& "Période >= '" & ComDateDisponible.Text & "' and " _
 & "Période <= '" & ComDateDisponible2.Text & "'"
 
 sSQL2 = sSQL2 & " ORDER BY [Période]"

oRST2.Open sSQL2, oConnect2

If oRST2.BOF = False Then

Do Until oRST2.EOF
  MSFlexGrid1.AddItem Format(oRST2("Période"), "dd mmmm yyyy") & vbTab & Trim(oRST2("Employé")) & vbTab & oRST2("Description_du_produit") & vbTab & oRST2("Quantité_assignée_ajustée") & vbTab & oRST2("Montant_ajusté") & vbTab
  oRST2.MoveNext
Loop

  With MSFlexGrid1
        .ColSel = 0
        .Sort = flexSortStringAscending
        .ColSel = 1
        .Sort = flexSortStringAscending
    End With

Select allOpen in new window

the only problem now is about the 10th as you can see on the picture.
result-5.jpg

@Wilder1626

Where did the sortable dates go?!?

"dd  mmm  yyyy" formatted dates can not be ordered by date.

it is at the end of the code:

 

With MSFlexGrid1
        .ColSel = 0
        .Sort = flexSortStringAscending
        .ColSel = 1
        .Sort = flexSortStringAscending
    End With

Select allOpen in new window

1/ try not sorting by Column 0 as this sort is done by the query.

2/ in fact, why not sort by both columns in the query if this is the only thing it is used for ?

3/if this still gives strange results, then your data may have something extra like the Date field having a time component though Akimark's code should have corrected this.  that is, if the field has just a date, it assumes the time is 00:00.  Other records may have the same date but have a time also.  eg  10 Dec 2010 is before 10.Dec.2010 09:00.  Can you confirm the database type and the field type of Periode.

in this case you would need to truncate the date field to drop the time.   to test this, change the "dd mmmm yyyy" to be "dd mmmm yyyy  hhnn")

@Wilder1626

that code does the sorting, but doesn't answer my question about the "sortable dates".

A "sortable date" is in a format that resembles yyyymmdd

Hello robberbaron,

This is the result if a put format:
in this case you would need to truncate the date field to drop the time.   to test this, change the "dd mmmm yyyy hhnn" to be "

See picture 1.

aikimark,

If i use format: yyyymmdd like you say, see the picture 2. It his worst.



result6.jpg
Picture-2.jpg

@Wilder1626

Please post your code, including the formatting of the date column and the sorting of the grid

Wilder.  can you export your testdata to CSV or Excel format and post that file so we can test using the same data as something doesnt make sense.

Hello all,
I will export to CSV.

I think that since the date column in my access dbase his in text format, this is causing the problem.

Please post your code.  You have multiple experts giving you advice.  We don't know what you've done.

Here is the full code and the export in excel of the dbase.

Private Sub cmdRechercher_Click()
If ComDateDisponible <> "" Then
MSFlexGrid1.Clear
MSFlexGrid1.Rows = 2
MSFlexGrid1.Cols = 5


'Column name
MSFlexGrid1.TextMatrix(0, 0) = "Date"
MSFlexGrid1.TextMatrix(0, 1) = "Employé"
MSFlexGrid1.TextMatrix(0, 2) = "Description de(s) produit(s)"
MSFlexGrid1.TextMatrix(0, 3) = "Qté(s) réalisée(s)"
MSFlexGrid1.TextMatrix(0, 4) = "Gain(s) réalisée(s)"

'autofit column
MSFlexGrid1.ColWidth(0) = 3400
MSFlexGrid1.ColWidth(1) = 4800
MSFlexGrid1.ColWidth(2) = 4400
MSFlexGrid1.ColWidth(3) = 3100
MSFlexGrid1.ColWidth(4) = 3100



  Dim sSQL2 As String
    Dim oRST2 As ADODB.Recordset
  Set oRST2 = New ADODB.Recordset
  Dim oConnect2 As ADODB.Connection
  Set oConnect2 = New ADODB.Connection


   oConnect2.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=" & Form4.txtBaseDe.Text

sSQL2 = "SELECT [Période],[Employé],[Description_du_produit],[Quantité_assignée_ajustée],[Montant_ajusté] FROM [Distribution_employé] Where " _
& "Période >= '" & ComDateDisponible.Text & "' and " _
 & "Période <= '" & ComDateDisponible2.Text & "'"
 
 sSQL2 = sSQL2 & " ORDER BY [Période]"

oRST2.Open sSQL2, oConnect2

If oRST2.BOF = False Then

Do Until oRST2.EOF
  MSFlexGrid1.AddItem Format(oRST2("Période"), "yyyymmdd") & vbTab & Trim(oRST2("Employé")) & vbTab & oRST2("Description_du_produit") & vbTab & oRST2("Quantité_assignée_ajustée") & vbTab & oRST2("Montant_ajusté") & vbTab
  oRST2.MoveNext
Loop

  With MSFlexGrid1
        .ColSel = 0
        .Sort = flexSortStringAscending
        .ColSel = 1
        .Sort = flexSortStringAscending
    End With

Else
   'no record found...do something here
End If
End If

With MSFlexGrid1
    If (.Rows > .FixedRows + 1) Then
        .RemoveItem .Row
    Else
        .Rows = .FixedRows
    End If
End With



'Total

For r = 1 To MSFlexGrid1.Rows - 1
  If Len(MSFlexGrid1.TextMatrix(r, 3)) Then Total = Total + 1

Next r
lblTotalrecord = CStr(Total)


cmdCommand1.Enabled = True



End Sub

Select allOpen in new window

Distribution-employ-.csv

Try this.

Private Sub cmdRechercher_Click()

If ComDateDisponible <> "" Then
  MSFlexGrid1.Clear
  MSFlexGrid1.Rows = 2
  MSFlexGrid1.Cols = 5


  'Column name
  MSFlexGrid1.TextMatrix(0, 0) = "Date"
  MSFlexGrid1.TextMatrix(0, 1) = "Employé"
  MSFlexGrid1.TextMatrix(0, 2) = "Description de(s) produit(s)"
  MSFlexGrid1.TextMatrix(0, 3) = "Qté(s) réalisée(s)"
  MSFlexGrid1.TextMatrix(0, 4) = "Gain(s) réalisée(s)"

  'autofit column
  MSFlexGrid1.ColWidth(0) = 3400
  MSFlexGrid1.ColWidth(1) = 4800
  MSFlexGrid1.ColWidth(2) = 4400
  MSFlexGrid1.ColWidth(3) = 3100
  MSFlexGrid1.ColWidth(4) = 3100

  Dim sSQL2 As String
  Dim oRST2 As ADODB.Recordset
  Dim oConnect2 As ADODB.Connection

  Set oRST2 = New ADODB.Recordset
  Set oConnect2 = New ADODB.Connection


  oConnect2.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=" & Form4.txtBaseDe.Text

  sSQL2 = "SELECT [Période],[Employé],[Description_du_produit],[Quantité_assignée_ajustée],[Montant_ajusté] FROM [Distribution_employé] Where " _
    & "Période >= #" & ComDateDisponible.Text & "# and " _
    & "Période <= #" & ComDateDisponible2.Text & "#"
 
  oRST2.Open sSQL2, oConnect2

  If oRST2.BOF = False Then

    Do Until oRST2.EOF
      MSFlexGrid1.AddItem Format(oRST2("Période"), "yyyymmdd") & vbTab & Trim(oRST2("Employé")) & vbTab & oRST2("Description_du_produit") & vbTab & oRST2("Quantité_assignée_ajustée") & vbTab & oRST2("Montant_ajusté")

      'Total non-empty descriptions
      If Len(oRST2("Description_du_produit")) <> 0 Then Total = Total + 1

      oRST2.MoveNext
    Loop

    With MSFlexGrid1
        .ColSel = 0     'minor key = date
        .Sort = flexSortStringAscending
        .ColSel = 1     'major key = employee
        .Sort = flexSortStringAscending
    End With

  Else
    'no record found...do something here
  End If

End If

With MSFlexGrid1
    If (.Rows > .FixedRows + 1) Then
        .RemoveItem .Row
    Else
        .Rows = .FixedRows
    End If
End With

lblTotalrecord = CStr(Total)

cmdCommand1.Enabled = True

End Sub

Select allOpen in new window

if [Période] is a date column you should be able to eliminate your grid sorting by letting the database engine order the rows before you load the data into your grid.

sSQL2 = "SELECT [Période],[Employé],[Description_du_produit],[Quantité_assignée_ajustée],[Montant_ajusté] FROM [Distribution_employé] Where " _
    & "Période >= #" & ComDateDisponible.Text & "# and " _
    & "Période <= #" & ComDateDisponible2.Text & "#"

 sSQL2 = sSQL2 & " ORDER BY [Employé], [Période]"

Select allOpen in new window

Hummm, that would be a big change, i think.

I will take a look if this is a major work to bring Periode has a date value.

I will let you know the result shortly.

1/ based upon your export CSV, it appears to be a type other than text.   the 10-d,c-10  is not a text represtentaion of a date and appears only partial ?

Nø;P‚riode;Employ‚;No_Bon;
183;10-d‚c-10;POIT JMP;789;

2/ What is your datasource ?  MDB, XLS ?   Please post the actual source string and file.

If i understand your question, my data source is Microsoft Access 2007 .mdb

does it answer your question?

I just want to make sure that i understand your question right.

apologies. just realised that 10-d,c-10 is a translation of 10-dec-10  where the e is the european character.   so could be either date or text field.

BUT MSDN quote"Remarks

The Sort property always sorts entire rows. To specify the range to be sorted, set the Row and RowSel properties. If Row and RowSel are the same, the MSHFlexGrid will sort all non-fixed rows.

The keys used for sorting are determined by the Col and ColSel properties. Sorting is always done in a left-to-right direction. For example, if Col =3 and ColSel =1, the sort is done according to the contents of columns 1, then 2, then 3."

So we have a problem using the flexgrid to sort.  So back to my comment #34359629, we need to sort as part of the query.

Adding an order by SQL clause (same as akimark), produced the desired result when I used an Excel file as the input source, and the Date column was a Date format.

sSQL2 = "SELECT [Période],[Employé],[Description_du_produit],[Quantité_assignée_ajustée],[Montant_ajusté] FROM [Distribution_employé] " _
        & "WHERE Période >= #" & ComDateDisponible_Text & "# " _
        & "AND Période <= #" & ComDateDisponible2_Text & "#" _
        & " ORDER BY [Employé], [Période];"

        
     
      oRST2.Open sSQL2, oConnect2
    
      If oRST2.BOF = False Then
    
        Do Until oRST2.EOF
          MSFlexGrid1.AddItem Format(oRST2("Période"), "yyyymmdd") & vbTab & Trim(oRST2("Employé")) & vbTab & oRST2("Description_du_produit") & vbTab & oRST2("Quantité_assignée_ajustée") & vbTab & oRST2("Montant_ajusté")
    
          'Total non-empty descriptions
          If Len(oRST2("Description_du_produit")) <> 0 Then Total = Total + 1
    
          oRST2.MoveNext
        Loop
    
        'With MSFlexGrid1
        '    '.ColSel = 0     'minor key = date
        '    .Sort = flexSortStringAscending
        '    .ColSel = 1     'major key = employee
        '    .Sort = flexSortStringAscending
        'End With
        
      Else
        'no record found...do something here
      End If

Select allOpen in new window

So i realy have to change it to a date format.

Right?

OK thanks. I will take a look at it right know and let you know of the result very shortly.

Thanks again to both of you for your help and patience.

@robberbaron

couldn't we change both the colstart and col properties for the two sorts?

appears not.
my test shows that it doesnt matter if col is less than colstart.  the sort starts with the lowest column and uses all in sequence to the right.  trying to do a sort on Col 3, then col 2 (where colsel=col both cases), the order of grid is not retained between the sorts.


my test code....
Private Sub cmdTestSort_Click()
    MSFlexGrid1.Col = Val(Text1.Text)
    MSFlexGrid1.ColSel = Val(Text2.Text)
   
    MSFlexGrid1.Sort = flexSortStringAscending
End Sub

Private Sub Test_Click()
    Dim i As Integer, j As Integer, sData As String
    Randomize Timer
    MSFlexGrid1.Clear
    MSFlexGrid1.Rows = 2
    MSFlexGrid1.Cols = 5
   
   
    'Column name
    MSFlexGrid1.TextMatrix(0, 0) = "AA"
    MSFlexGrid1.TextMatrix(0, 1) = "BB"
    MSFlexGrid1.TextMatrix(0, 2) = "CC"
    MSFlexGrid1.TextMatrix(0, 3) = "DD"
    MSFlexGrid1.TextMatrix(0, 4) = "EE"
   
    For i = 1 To 12
        sData = Format(Rnd() * 5, "00")
        For j = 1 To 4
            sData = sData & vbTab & Format(Rnd() * 5, "00")
        Next j
        MSFlexGrid1.AddItem sData
       
    Next i
   
    With MSFlexGrid1
        If (.Rows > .FixedRows + 1) Then
            .RemoveItem .Row
        Else
            .Rows = .FixedRows
        End If
    End With
End Sub

Thanks for that test.  Well, the only way to sort this using the grid's sort method is to create a hidden column so that the (sorted) columns are in adjacent left-to-right position.  Since we were reformatting the date anyway, it seems logical that we create a hidden column for the date to the right of the Employee column.

I still think that doing the sort coming out of Access is the best way.

So i realy have to change it to a date format.

Right?

 Yes, if the data is a date then store it as one as it is more efficient on a number of levels.

Create a new date field in the Access table and run a transform on your current date-string field to fill the new field.

@robberbaron

Yes.  Letting the database sort the data is certainly better than flexgrid sorting.

Hello all,

Sorry for the delay. LOt of work on my code to be sble to fix this also.

Now they are both date field and it work very good.

Thanks again to both of you