Member Avatar for mavtcr

Friends I met with a problem...Please help

I have a table 'Tran' in Access Database which has several fields of which one is 'Date'
I added several records through programm on different dates.Everything OK. All the records appeared in a chronological way ie.Earlier to Later order . Later I deleted some earlier records randomly.(Not through programm but manually).Now when I try to add record through programm the 'Date' order is not retained. I think the new added record takes the place of the deleted record.It will affect the output of the programme.Is there any way to maintain the chronological order in the records of the table. My relevant code is given below.

     TRS.AddNew
     TRS("Ac") = TxtAc.Text
     TRS("Acno") = TxtAcno.Text
     TRS("Name") = TxtName.Text
     TRS("Date") = TxtDate.Text
     TRS("Tramt") = TxtTramt.Text
     TRS("Type") = TxtType.Text
     TRS("Details") = TxtDetails.Text
     TRS("Vdate") = TxtVdate.Text
     TRS("Ac") = TxtAc.Text
     TRS("Cq") = TxtCq.Text
     TRS("Part") = "."
     TRS("Uid") = UID
     TRS("Mid") = UID
     TRS("Edt") = Format$(Date, "dd/mm/yyyy")
     TRS("Mdt") = Format$(Date, "dd/mm/yyyy")
     TRS("Trnno") = TN

        If TxtType.Text = "C" Then
      TRS("Credit") = TxtTramt.Text
      TRS("Debit") = 0
      TRS("Dc") = "CR"
          Else
       If TxtType.Text = "D" Then
      TRS("Debit") = TxtTramt.Text
      TRS("Credit") = 0
      TRS("Dc") = "DR"
          End If
          End If

       If LblCd = "CR" And TRS("Dc") = "CR" Then
     TRS("Balance") = PB + Val(TxtTramt.Text)
          Else
        If LblCd = "CR" And TRS("Dc") = "DR" Then
     TRS("Balance") = PB - Val(TxtTramt.Text)
          Else
       If LblCd = "DR" And TRS("Dc") = "DR" Then
     TRS("Balance") = PB - Val(TxtTramt.Text)
          Else
        If LblCd = "DR" And TRS("Dc") = "CR" Then
     TRS("Balance") = PB + Val(TxtTramt.Text)
        End If
         End If
          End If
           End If

      If LblCd = "CR" And TRS("Balance") >= 0 Then
      TRS("Cd") = "CR"
        Else
     If LblCd = "CR" And TRS("Balance") < 0 Then
       TRS("Cd") = "DR"
       Else
     If LblCd = "DR" And TRS("Balance") >= 0 Then
      TRS("Cd") = "CR"
        Else
     If LblCd = "DR" And TRS("Balance") < 0 Then
       TRS("Cd") = "DR"
        End If
         End If
          End If
           End If
     LblEuid = UID
     LblMuid = UID
     LblEdt = Format$(Date, "dd/mm/yyyy")
     LblMdt = Format$(Date, "dd/mm/yyyy")
     LblDC = TRS("Cd")
     LblBalance = TRS("Balance")
     TxtTrno = TRS("Trnno")
    TRS.Update
    TRS.Requery

I want always the records in a date wise order- earlier to later

Edited by mavtcr
  • 2 Contributors
  • 2 Replies
  • 365 Views
  • 23 Hours Discussion Span
  • Latest Post Latest Post by mavtcr

Dani AI

Generated

is right that you must explicitly request a sort when you display or fetch rows. Access tables do not guarantee a physical row order — deletes can leave space that later inserts reuse — so relying on the table’s visible order will produce intermittent results.

First, fix storage: make the date column a Date/Time data type (avoid storing formatted strings). Avoid naming a field Date (it’s a reserved word); rename it (for example, TransDate) or always bracket it as [Date]. When inserting, convert the user input to a real Date value instead of saving a formatted string. For example:

' convert user text to a Date before storing
rs("TransDate") = CDate(TxtDate.Text)

Always sort on retrieval and use a stable tie-breaker for same-day entries (an AutoNumber or transaction number). Example SQL you can use to build the recordset or query:

SELECT * FROM Tran ORDER BY TransDate ASC, TrnNo ASC;

If you show records in a form, set the form’s OrderBy and requery so the UI respects the sort:

Me.OrderBy = "TransDate, TrnNo"
Me.OrderByOn = True
Me.Requery

Quick troubleshooting checklist:

  • Confirm the date column type in Table Design and rename if needed.
  • If dates were stored as text, add a new Date/Time column and convert values with CDate/DateValue, verify, then remove the text column.
  • Add an index on TransDate (and TrnNo) for performance on large tables.

Applying these fixes will make chronological ordering reliable regardless of deletes or how Access physically stores rows.

Recommended Answers

All 2 Replies

Member Avatar for Reverend Jim

You can specify the sort order when you retrieve the records by using the ORDER BY clause in the query.

SELECT * FROM mytable ORDER BY fieldname
Member Avatar for mavtcr

OK thank You

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.