Member Avatar for hallianonline

Hello Friends I am facing a little problem that I want to sort my records according to date I mean to say
I want to show the upcoming records based on dates on the top in ascending order I have a complete working code to fetch from the database but I don’t know that why its not showing up according to my requirements here is my screenshot and database query.
I am using MS Access, VB6 and Listview for displaying all records

If rs.State = 1 Then rs.Close
sql = "SELECT * FROM tblHallBook WHERE book_complete='0' AND book_status='1' ORDER BY book_date ASC"
rs.Open sql, cn
ListView3.ListItems.clear
  • 2 Contributors
  • 1 Reply
  • 255 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by Smith5646

Dani AI

Generated

— building on 's on-target questions, the problem is almost always one of three things: the date column is not a true Date/Time (so ORDER BY sorts text lexically), the ListView is re-sorting items after insertion, or past dates are included when the goal is "upcoming" only. The reliable fixes are (1) store dates as Date/Time, (2) filter to future dates, and (3) turn off any automatic ListView sorting while populating.

If the field is a real Date/Time column, use a WHERE clause to keep only upcoming rows and order by the date value:

SELECT *
FROM tblHallBook
WHERE book_complete = 0
  AND book_status = 1
  AND book_date >= Date()
ORDER BY book_date ASC;

If the field is text (mixed or wrong format), either convert the column to Date/Time in the table, or guard and convert at query time. The IsDate/CDate pattern prevents conversion errors:

SELECT *
FROM tblHallBook
WHERE book_complete = '0'
  AND book_status = '1'
  AND IsDate([book_date]) = True
  AND CDate([book_date]) >= Date()
ORDER BY CDate([book_date]) ASC;

Practical VB6/ListView tips: set ListView3.Sorted = False before adding items so the control preserves the incoming order, populate in recordset order, then leave sorting off (or implement a manual sort). To debug, run the SQL in Access directly or iterate the recordset and Debug.Print the date values to confirm the order coming from the database. If times are stored and only the date part matters, use DateValue (or CDate + DateValue) when comparing or ordering. Long-term, normalize flags (book_complete, book_status) as numeric fields and convert book_date to Date/Time in table design to avoid recurring sorting surprises.

Member Avatar for Smith5646

I don't see a screen print and don't know what you database looks like so maybe some stupid questions but...
1) is book_date a true date field or something else (char or number) with what something that looks like a date?
2) is there a sort defined on the list view that is resequencing the list view after loading?
3) have you debugged to see that you are indeed getting them in the wrong sequence vs something sorting them after the fact?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.