Sort Data

Set the Sorted Property to True..

Hi cyseng10,

If the Combo box is bound to a data control then simply sort it in the SQL. Use "Order By" in your SQL.

'Hope will help.

like ryancys has stated, set the sort property to the combo box you have created on your form.

this will do it automaticaly for you.

cheers
Andy

If you are opning the recordset to fill cobo , then better sort data by SQL

Sanjeev

Setting Combo1.Sort = True WILL NOT necessarily work!
15 January 2001 would come AFTER 15 February 2001 (J > F),
so the items must be interpreted as dates when they're
sorted.

Using SQL is not the *best* solution. The sorting is really
only for presentation in the user interface, so why burden
the database with the extra overhead - it could be significant with larger result sets.

I reckon the best way is to write a small routine in the
client tier that'll do the job. If you find yourself
sorting data from lots of other database result sets then
SQL may be an option.

Below is a sub that'll sort in ascending or descending order a combo box whose string items are formatted as
VB-recognized dates (ie. Compatible with the CDate function).

----------------------- CODE SNIPPET ---------------------

'This example uses a form with 2 buttons, a Text box and
'a combo box.

Option Explicit

Private Sub Command1_Click()
  Combo1.AddItem Text1.Text
End Sub

Private Sub Command2_Click()
  SortComboDates Combo1
End Sub

Private Sub SortComboDates(Combo As ComboBox, Optional Ascending As Boolean = True)
  'assuming the strings are formatted correctly,
  'bubble-sort the date strings
  Dim lb&, ub&, i&, d1#, d2#, s1$, s2$, anybubbles As Boolean
  ub = Combo.ListCount - 1
  lb = 0
  anybubbles = True
  Do While anybubbles
    anybubbles = False
    For i = lb To ub
      If (i + 1) > ub Then Exit For
      s1 = Combo.List(i)
      s2 = Combo.List(i + 1)
      d1 = CDbl(CDate(s1))
      d2 = CDbl(CDate(s2))
      If (Ascending And (d2 < d1)) Or ((Not Ascending) And (d2 > d1)) Then
        anybubbles = True
        Combo.List(i) = s2
        Combo.List(i + 1) = s1
      End If
    Next
  Loop
End Sub

--------------------- EOF CODE SNIPPET --------------------

Hope this helps,

rondi.

watching ....

Rondi, I don't know how much you've participated in the VB forum in the past, but the etiquette here is comment only.  Especially where there is other expert input and the question hasn't been open for several days.

>Using SQL is not the *best* solution. The sorting is really only for presentation in the user interface, so why burden the database with the extra overhead - it could be significant with larger result sets.<

Sorting a large result set "in place" in a combo box is incredibly inefficient.  If you use client side sorting as in rondi's example, sort first, then add to the combo.  SQL sorting is also a very valid solution as suggested by ryancys.  Database code is highly optimized (way faster than vb code that has not been optimized, as above) for this kind of query, especially if the sort fields are indexed.  

to all helper,

  can't use the Sort in property, it sort the data but not in Date format. i need to sort it ascending based on DD/MM/YY. currently, is only sort by DD.

  anyway, i can sort the DATE.

rondi,

  i only using combo box to add the data from database and i'm but confused on your code provided. can you explain it. i only need to sort date based DD/MM/YY. if i using sort in property and is only sort based on DD.

  by using  SQL is not working. below is my SQL statement:-


Set Rs = DBConn.Execute("SELECT DISTINCT TO_CHAR(IN_DATETIME,'DD-MON-YY') FROM TGVTRANSACTION")


 

Hi,

Try:

Set Rs = DBConn.Execute("SELECT DISTINCT TO_CHAR(IN_DATETIME,'DD-MON-YY') FROM TGVTRANSACTION Order By IN_DATETIME")

if the function TO_CHAR(IN_DATETIME,'DD-MON-YY') is correct.

'Hope will help.
 

ryancys,

  I try on it but not working and error occur " not selected expresion". if i didn't add the "ORDER BY" is working.  

Set Rs = DBConn.Execute("SELECT DISTINCT TO_CHAR(IN_DATETIME,'DD-MON-YY'), IN_DATETIME FROM TGVTRANSACTION Order By IN_DATETIME")

Or you could eliminate the text formatting in the query and use Format(rs("IN_DATETIME", "dd-mmm-yy") when adding to the combo.

Format(rs("IN_DATETIME"), "dd-mmm-yy")

Or you could use a syntactically correct version.  :)

ryancys,

  I try on it but not working and error occur " not selected expresion". if i didn't add the "ORDER BY" is working.  

PaulHews

   is not working also...

Hi cyseng10,

Self Tested example:

Select Distinct Format$(Customer.DOB,"dd-mmm-yy") From Customer Order By Format$(Customer.DOB,"dd-mmm-yy")

Use the 'Format' provided.

'Tested in Access 2000

'Hope will help.

ryancys, that goes back to the problem that you are sorting a date field alphabetically.  That only works if you format the field yyyy-mm-dd.  Otherwise, you have bad ordering.

Tested this and it works fine.

SELECT Format$([IN_DateTime],"dd-mmm-yy") AS TextIN_Date, TGVTRANSACTION.IN_DateTime
FROM TGVTRANSACTION
ORDER BY TGVTRANSACTION.IN_DateTime;

PaulHews

   is not working also...

Probably SQL server doesn't like the Format function, but the basic approach is correct.  If you tell us what errors you are getting instead of just telling us it doesn't work, we might be able to help better.

PaulHews,

  i admended it as below: -
   Set Rs = DBConn.Execute("SELECT Format$([IN_DateTime],"dd-mmm-yy") AS TextIN_Date, TGVTRANSACTION.IN_DateTime From TGVTRANSACTION ORDER BY TGVTRANSACTION.IN_DateTime")
 
  error accoured " Expected : list or separator )". is that missing something.

PaulHews,

  i admended it as below: -
   Set Rs = DBConn.Execute("SELECT Format$([IN_DateTime],"dd-mmm-yy") AS TextIN_Date, TGVTRANSACTION.IN_DateTime From TGVTRANSACTION ORDER BY TGVTRANSACTION.IN_DateTime")
 
  error accoured " Expected : list or separator )". is that missing something.

Yup, we have to embed the quotes or use single quotes to prevent from breaking the string:

Set Rs = DBConn.Execute("SELECT Format$([IN_DateTime],'dd-mmm-yy') AS TextIN_Date, TGVTRANSACTION.IN_DateTime
From TGVTRANSACTION ORDER BY TGVTRANSACTION.IN_DateTime")

PaulHews,

  i admended it as below: -
   Set Rs = DBConn.Execute("SELECT Format$([IN_DateTime],"dd-mmm-yy") AS TextIN_Date, TGVTRANSACTION.IN_DateTime From TGVTRANSACTION ORDER BY TGVTRANSACTION.IN_DateTime")
 
  error accoured " Expected : list or separator )". is that missing something.

See my comment above

PaulHews,

   still not working but no error. when i click on the combo box no data. below is the code to get the DATE from database:-

Set Rs = DBConn.Execute("SELECT Format$([IN_DateTime],'dd-mmm-yy') AS TextIN_Date, TGVTRANSACTION.IN_DateTime From TGVTRANSACTION ORDER BY TGVTRANSACTION.IN_DateTime")

Do While Not Rs.EOF
    cboInDateTime.AddItem Rs(0)
    Rs.MoveNext
Loop

PaulHews,

   still not working but no error. when i click on the combo box no data. below is the code to get the DATE from database:-

Set Rs = DBConn.Execute("SELECT Format$([IN_DateTime],'dd-mmm-yy') AS TextIN_Date, TGVTRANSACTION.IN_DateTime From TGVTRANSACTION ORDER BY TGVTRANSACTION.IN_DateTime")

Do While Not Rs.EOF
    cboInDateTime.AddItem Rs(0)
    Rs.MoveNext
Loop

> still not working but no error

What is the output on Combo box?

PaulHews,

   still not working but no error. when i click on the combo box no data. below is the code to get the DATE from database:-

Set Rs = DBConn.Execute("SELECT Format$([IN_DateTime],'dd-mmm-yy') AS TextIN_Date, TGVTRANSACTION.IN_DateTime From TGVTRANSACTION ORDER BY TGVTRANSACTION.IN_DateTime")

Do While Not Rs.EOF
    cboInDateTime.AddItem Rs(0)
    Rs.MoveNext
Loop

ryancys,


   no output on combo box...

Hi,

As me and PaulHews emphasised, Use the " Not ' .

Set Rs = DBConn.Execute("SELECT Format$([IN_DateTime],"dd-mmm-yy") AS TextIN_Date, TGVTRANSACTION.IN_DateTime
From TGVTRANSACTION ORDER BY TGVTRANSACTION.IN_DateTime")

Change

cboInDateTime.AddItem Rs(0)

to

cboInDateTime.AddItem Rs(0).Value  ?
   
Or

cboInDateTime.AddItem Rs.Fields!TextIN_Date ?


Are you using SQL Server or Access?

regards ..

ryancys,


   no output on combo box...

Hi,

Set Rs = DBConn.Execute("SELECT Format$([TGVTRANSACTION.IN_DateTime ],"dd-mmm-yy") AS TextIN_Date, TGVTRANSACTION.IN_DateTime

From TGVTRANSACTION ORDER BY TGVTRANSACTION.IN_DateTime")

?

ryancys,

  i using SQL Server...I try on the code you recomend but not working and no error. no output at combo box, empty...

ryancys,

   What should i put for

    cboInDateTime.AddItem Rs(0)
   
    maintained the current code or change...

Hi,

You can try to get the RecordCount from the SQL executed first, if the RecordCount is Not 0 then you may change the " cboInDateTime.AddItem Rs(0) ", but i think the problem is on your SQL, check your SQL and if there is no data.

regards.

ryancys,

   What should i put for

    cboInDateTime.AddItem Rs(0)
   
    maintained the current code or change...

ryancys,


   i can get data by using below SQL:-

Set Rs = DBConn.Execute("SELECT DISTINCT TO_CHAR(IN_DATETIME,'DD-MON-YY') FROM TGVTRANSACTION")

Do While Not Rs.EOF
    cboInDateTime.AddItem Rs(0)

    Rs.MoveNext
Loop
 
   after i get the data, i need to sorted it.

Hi cyseng10,

As posted earlier:

Should this works ?

"SELECT DISTINCT TO_CHAR(IN_DATETIME,'DD-MON-YY') FROM TGVTRANSACTION ORDER BY TO_CHAR(IN_DATETIME,'DD-MON-YY')"

regards.

ryancys,


   i can get data by using below SQL:-

Set Rs = DBConn.Execute("SELECT DISTINCT TO_CHAR(IN_DATETIME,'DD-MON-YY') FROM TGVTRANSACTION")

Do While Not Rs.EOF
    cboInDateTime.AddItem Rs(0)

    Rs.MoveNext
Loop
 
   after i get the data, i need to sorted it.

ryancys,

  not working, the output of the combo box is empty

What is the RecordCount Number?

ryancys,

  not working, the output of the combo box is empty

Hi,

What is the output of:

rs.RecordCount ?

ryancys,

   WHEN run on teh SQL statement is straight away jump out. something wrong of the SQL Statement.

   can't get any recordcount...

Check ADO errors.  Also, if you are going to keep getting help on this question, I suggest you reject the proposed answer.  It also wouldn't hurt you to maintain the questions you've asked in the past few months.

If you are using SQL Server then try the following:

"SELECT DISTINCT Convert(varchar,IN_DATETIME,106) FROM TGVTRANSACTION ORDER BY IN_DATETIME"

Is TO_CHAR only used on Oracle, Tim?

Paul,

Correct, TO_CHAR is an Oracle function in SQL Server the equivalent is Str or as Tim has used Convert.

Anthony

TimCottee,

I try on this SQL but not working:
 
"SELECT DISTINCT Convert(varchar,IN_DATETIME,106) FROM TGVTRANSACTION ORDER BY IN_DATETIME"


How do i sort the DATE ascending.....

TimCottee,

I try on this SQL but not working:
 
"SELECT DISTINCT Convert(varchar,IN_DATETIME,106) FROM TGVTRANSACTION ORDER BY IN_DATETIME"


How do i sort the DATE ascending.....

I realised later on after posting that this might not work as expected, try this instead:

"SELECT DISTINCT Convert(varchar,IN_DATETIME,106) As TextIN_Date, Convert(varchar,IN_DATETIME,101) As OrderByDate FROM TGVTRANSACTION ORDER BY Convert(varchar,IN_DATETIME,101) Asc"

This gets the date field twice, once in the desired text format and the other in an appropriate format for sorting. It is required because you cannot use another field to order by if using Distinct so we need to return both fields however you will only used one in your application so the second is redundant in that sense.

Keeping it simple...

<----- Code Begin ----->

Dim strSQL As String
strSQL = "SELECT DISTINCT IN_DateTime " _
       & "  FROM TGVTRANSACTION " _
       & " ORDER BY IN_DateTime "

Set Rs = DBConn.Execute(strSQL)
With Rs
   cboInDateTime.Clear
   Do While NOT .EOF
      cboInDateTime.AddItem Format(!IN_Date, "dd-mmm-yy")
      .MoveNext
   Loop
End With

<----- Code End ----->

wsh2,

  the code is working but the output is not what i want. the date is sort by ascending by day. but what i want is sort by day, month and year. example:-

 1-Jan-01
 2-Jan-01
 3-Jan-01
 1-Feb-01

  currently, your code output is working as below:- (using above as example)

 1-Jan-01
 1-Feb-01
 2-Jan-01
 3-Jan-01

 as you can see the output, is sort by day and not by month by month and year by year...

Here's a sample using my proposed sub.
It'll sort day, month and year in ascending order:

----------------------- CODE SNIPPET ---------------------
Dim sql As String, rs As Recordset, con As New Connection
con.Open ".......
sql = "SELECT DISTINCT IN_DateTime FROM TGVTRANSACTION"

Set rs = con.Execute(sql)
inDateTimeCombo.Clear
With rs
  Do While not .EOF
     inDateTimeCombo.AddItem .Fields("IN_DateTime").Value
     .MoveNext
  Loop
End With

SortComboDates inDateTimeCombo, True


Private Sub SortComboDates(Combo As ComboBox, Optional Ascending As Boolean = True)
 'assuming the strings are formatted correctly,
 'bubble-sort the date strings
 Dim lb&, ub&, i&, d1#, d2#, s1$, s2$, anybubbles As Boolean
 ub = Combo.ListCount - 1
 lb = 0
 anybubbles = True
 Do While anybubbles
   anybubbles = False
   For i = lb To ub
     If (i + 1) > ub Then Exit For
     s1 = Combo.List(i)
     s2 = Combo.List(i + 1)
     d1 = CDbl(CDate(s1))
     d2 = CDbl(CDate(s2))
     If (Ascending And (d2 < d1)) Or ((Not Ascending) And (d2 > d1)) Then
       anybubbles = True
       Combo.List(i) = s2
       Combo.List(i + 1) = s1
     End If
   Next
 Loop
End Sub

--------------------- EOF CODE SNIPPET --------------------

rondi

<< the code is working but the output is not what i want. the date is sort by ascending by day. but what
i want is sort by day, month and year >>

Change the cboInDateTime Sorted property to False.. <smile>

Rejecting proposed answer, it obviously is out of scope since a lot of comments ago.

costello
Community Support Moderator @ EE

cyseng10:
I suspect that the simplified code provided and the unflagging of the Sorted property solved your problem.
PLEASE come back to YOUR question and let us know where you are at.. <smile>

   

ADMINISTRATION WILL BE CONTACTING YOU SHORTLY.  Moderators Computer101, Netminder or Mindphaser will return to finalize these if they are still open in 7 days.  Experts, please post closing recommendations before that time.

Below are your open questions as of today.  Questions which have been inactive for 21 days or longer are considered to be abandoned and for those, your options are:
1. Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you, but may help others. You must tell the participants why you wish to do this, and allow for Expert response.  This choice will include a refund to you, and will move this question to our PAQ (Previously Asked Question) database.  If you found information outside this question thread, please add it.
3. Ask Community Support to help split points between participating experts, or just comment here with details and we'll respond with the process.
4. Delete the question (if it has no potential value for others).
   --> Post comments for expert of your intention to delete and why
   --> YOU CANNOT DELETE A QUESTION with comments; special handling by a Moderator is required.

For special handling needs, please post a zero point question in the link below and include the URL (question QID/link) that it regards with details.
https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
 
Please click this link for Help Desk, Guidelines/Member Agreement and the Question/Answer process.  https://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp

Click you Member Profile to view your question history and please keep them updated. If you are a KnowledgePro user, use the Power Search option to find them.  

Questions which are LOCKED with a Proposed Answer but do not help you, should be rejected with comments added.  When you grade the question less than an A, please comment as to why.  This helps all involved, as well as others who may access this item in the future.  PLEASE DO NOT AWARD POINTS TO ME.

To view your open questions, please click the following link(s) and keep them all current with updates.
https://www.experts-exchange.com/questions/Q.20118966.html
https://www.experts-exchange.com/questions/Q.20119451.html
https://www.experts-exchange.com/questions/Q.20120253.html
https://www.experts-exchange.com/questions/Q.20140923.html
https://www.experts-exchange.com/questions/Q.20149515.html
https://www.experts-exchange.com/questions/Q.20155217.html
https://www.experts-exchange.com/questions/Q.20163789.html
https://www.experts-exchange.com/questions/Q.20299917.html
https://www.experts-exchange.com/questions/Q.20256679.html



*****  E X P E R T S    P L E A S E  ******  Leave your closing recommendations.
If you are interested in the cleanup effort, please click this link
https://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=commspt&qid=20274643 
POINTS FOR EXPERTS awaiting comments are listed in the link below
https://www.experts-exchange.com/commspt/Q.20277028.html
 
Moderators will finalize this question if in @7 days Asker has not responded.  This will be moved to the PAQ (Previously Asked Questions) at zero points, deleted or awarded.
 
Thanks everyone.
Moondancer
Moderator @ Experts Exchange