VBA Sorting data using several rules questions

[thape]

Dear all,



I want to sort a table from a database. The table have about 130 record (will be growing larger). Each record have 40 column(field). I want to sort the data using 6 fields values. These value have priority: Date>OD(Outside Diameter)>WT(wall thickness)>Steel Grade>Length

I figured out a way to do these. I am using Bubble Sort against the recordset and a comparing method to determine the two adjacent rows is larger or smaller than the previous row. Each record could have same OD, Date, WT, Steel Grade, or null in Steel Grade or Length.
Here is my code.

The problem I have: 1. Sometime , the function run into error at rs1.edit sometimes not????????
2. Any suggestion on this Bubble Sort idea..or some other way to achieve the task??????
3. Sometimes my code works, sometime not ??????


Thank you
Wayne

Option Compare Database
Option Explicit

Function modCreateMillTable()
  ' This function call the appending query function
  ' Then call the S20 filter function
  ' Then call the loopThroughPending to perform compare and swap
  ' Which then sort the output to desired output
  
 On Error GoTo createMillTable_Err
 
 
'Call appendS20ToPendingOrder
'call sortPendingTemp
Call loopPendingOrders
Call insideGrouping
'Call S20Filter
createMillTable_Err:
  Resume Next
   
End Function

Function loopPendingOrders()
  Dim db As Database
  Dim rs1 As DAO.Recordset
  Dim rs2 As DAO.Recordset
  Dim loopCount As Integer
  Dim i As Integer
  Dim count As Integer
  Dim outterLoopCount As Integer
  Dim tempRecord() As Variant
  
  Set db = CurrentDb
  Set rs1 = db.OpenRecordset("Pending_Temp")
  Set rs2 = rs1.Clone
  
    rs1.MoveLast
    rs1.MoveFirst
    
    rs2.MoveLast
    rs2.MoveFirst
    rs2.MoveNext
    
    loopCount = rs1.RecordCount
    outterLoopCount = rs1.RecordCount
    ReDim tempRecord(0 To rs1.Fields.count - 1) As Variant
    
    Do While outterLoopCount >= 1
    
    For i = 1 To loopCount - 1
            If (compareRecord(i - 1, i) = 2) Then  'choose minus 1 because move function decide move how many rows down
              rs1.Edit
              rs2.Edit
              For count = 0 To rs1.Fields.count - 1
                tempRecord(count) = rs1.Fields(count).Value
                rs1.Fields(count).Value = rs2.Fields(count).Value
                rs2.Fields(count).Value = tempRecord(count)
                tempRecord(count) = Null
              Next count
              rs1.Update
              rs2.Update
            End If
     rs1.MoveNext
     rs2.MoveNext
    Next i
   rs1.MoveLast
   rs1.MoveFirst
   rs2.MoveLast
   rs2.MoveFirst
   rs2.MoveNext
   
   outterLoopCount = outterLoopCount - 1
   loopCount = loopCount - 1
 Loop
 
 rs1.Close
 rs2.Close
 db.Close
 Set db = Nothing
 Set rs1 = Nothing
 Set rs2 = Nothing
 loopCount = 0
 outterLoopCount = 0
End Function
Function compareRecord(rs1Position As Integer, rs2Position As Integer) As Integer
' This function return a integer 1 or 2,
' 1 means rs1 place first, 2 means rs1 place second
'This variable is defiened with respect to rs1
Dim db As Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim rs3 As DAO.Recordset
Dim dateDifference As Integer
Dim lengthCompare As Integer
Dim steelGradeCompare As Integer
Dim lengthValue1 As String
Dim lengthValue2 As String
Dim founded As Boolean
Dim od1 As String
Dim od2 As String
Set db = CurrentDb
Set rs1 = db.OpenRecordset("Pending_Temp", 2)
Set rs2 = rs1.Clone
Set rs3 = rs1.Clone

rs1.MoveFirst
rs2.MoveFirst
rs1.Move (rs1Position)
rs2.Move (rs2Position)
founded = False
od1 = CStr(rs1.Fields("ODmm").Value)
od2 = CStr(rs2.Fields("ODmm").Value)
If (rs1.Fields("ODmm").Value = rs2.Fields("ODmm").Value) Then
   rs3.FindFirst "[ODmm] =" & od1
   Do While founded = False
      If Abs(rs1.Fields("RFS_required").Value - rs3.Fields("RFS_required").Value) <= 30 Then
         founded = True
         If (Abs(rs1.Fields("RFS_required").Value - rs3.Fields("RFS_required").Value) < Abs(rs2.Fields("RFS_required").Value - rs3.Fields("RFS_required").Value)) Then
             dateDifference = 31
         ElseIf (Abs(rs1.Fields("RFS_required").Value - rs3.Fields("RFS_required").Value) < Abs(rs2.Fields("RFS_required").Value - rs3.Fields("RFS_required").Value)) Then
             dateDifference = 1
         End If
      Else
         rs3.FindNext "ODmm = " & od1
      End If
   Loop
Else
     dateDifference = Abs(rs1.Fields("RFS_required").Value - rs2.Fields("RFS_required").Value)
End If
Debug.Print dateDifference
Select Case StrComp(rs1.Fields("Steel_Grade").Value, rs2.Fields("Steel_Grade").Value)
  Case 0
     steelGradeCompare = 0
  Case 1
     steelGradeCompare = 1
  Case -1
     steelGradeCompare = 2
End Select
If (IsNull(rs1.Fields("Length").Value)) Then
   lengthValue1 = ""
   If (IsNull(rs2.Fields("Length").Value)) Then
     lengthValue2 = ""
   Else
     lengthValue2 = rs2.Fields("Length").Value
   End If
Else
   lengthValue2 = rs1.Fields("Length").Value
End If

lengthCompare = lengthCompareMethod(lengthValue1, lengthValue2)
If (dateDifference > 30) And (rs1.Fields("RFS_required").Value > rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value > rs2.Fields("ODmm").Value) Then
    compareRecord = 2  '1
ElseIf (dateDifference > 30) And (rs1.Fields("RFS_required").Value < rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value > rs2.Fields("ODmm").Value) Then
    compareRecord = 1  '2
ElseIf (dateDifference > 30) And (rs1.Fields("RFS_required").Value = rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value > rs2.Fields("ODmm").Value) Then
    compareRecord = 2  '3
ElseIf (dateDifference > 30) And (rs1.Fields("RFS_required").Value > rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value < rs2.Fields("ODmm").Value) Then
    compareRecord = 2  '4
ElseIf (dateDifference > 30) And (rs1.Fields("RFS_required").Value < rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value < rs2.Fields("ODmm").Value) Then
    compareRecord = 1  '5
ElseIf (dateDifference > 30) And (rs1.Fields("RFS_required").Value = rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value < rs2.Fields("ODmm").Value) Then
    compareRecord = 1  '6
ElseIf (dateDifference <= 30) And (rs1.Fields("RFS_required").Value > rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value > rs2.Fields("ODmm").Value) Then
    compareRecord = 2
ElseIf (dateDifference <= 30) And (rs1.Fields("RFS_required").Value < rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value > rs2.Fields("ODmm").Value) Then
    compareRecord = 1
ElseIf (dateDifference <= 30) And (rs1.Fields("RFS_required").Value = rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value > rs2.Fields("ODmm").Value) Then
    compareRecord = 2
ElseIf (dateDifference <= 30) And (rs1.Fields("RFS_required").Value > rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value < rs2.Fields("ODmm").Value) Then
    compareRecord = 2
ElseIf (dateDifference <= 30) And (rs1.Fields("RFS_required").Value < rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value < rs2.Fields("ODmm").Value) Then
    compareRecord = 1
ElseIf (dateDifference <= 30) And (rs1.Fields("RFS_required").Value = rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value < rs2.Fields("ODmm").Value) Then
    compareRecord = 1
ElseIf (dateDifference > 30) And (rs1.Fields("RFS_required").Value > rs2.Fields("RFS_required").Value) And ((rs1.Fields("ODmm").Value) = (rs2.Fields("ODmm").Value)) Then
    compareRecord = 2
ElseIf (dateDifference > 30) And (rs1.Fields("RFS_required").Value < rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value = rs2.Fields("ODmm").Value) Then
    compareRecord = 1
ElseIf (dateDifference <= 30) And (rs1.Fields("RFS_required").Value < rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value = rs2.Fields("ODmm").Value) Then
    compareRecord = 1
ElseIf (dateDifference <= 30) And (rs1.Fields("RFS_required").Value > rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value = rs2.Fields("ODmm").Value) Then
    compareRecord = 2
ElseIf (dateDifference <= 30) And (rs1.Fields("RFS_required").Value = rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value = rs2.Fields("ODmm").Value) And _
       (rs1.Fields("WTmm").Value > rs2.Fields("RFS_required").Value) And (steelGradeCompare = 0) And (lengthCompare = 1) Then
    compareRecord = 2
ElseIf (dateDifference <= 30) And (rs1.Fields("RFS_required").Value = rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value = rs2.Fields("ODmm").Value) And _
       (rs1.Fields("WTmm").Value > rs2.Fields("RFS_required").Value) And (steelGradeCompare = 0) And (lengthCompare = 2) Then
    compareRecord = 2
ElseIf (dateDifference <= 30) And (rs1.Fields("RFS_required").Value = rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value = rs2.Fields("ODmm").Value) And _
       (rs1.Fields("WTmm").Value > rs2.Fields("RFS_required").Value) And (steelGradeCompare = 1) And (lengthCompare = 1) Then
    compareRecord = 2
ElseIf (dateDifference <= 30) And (rs1.Fields("RFS_required").Value = rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value = rs2.Fields("ODmm").Value) And _
       (rs1.Fields("WTmm").Value > rs2.Fields("RFS_required").Value) And (steelGradeCompare = 1) And (lengthCompare = 2) Then
    compareRecord = 2
ElseIf (dateDifference <= 30) And (rs1.Fields("RFS_required").Value = rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value = rs2.Fields("ODmm").Value) And _
       (rs1.Fields("WTmm").Value > rs2.Fields("RFS_required").Value) And (steelGradeCompare = 2) And (lengthCompare = 1) Then
    compareRecord = 2
ElseIf (dateDifference <= 30) And (rs1.Fields("RFS_required").Value = rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value = rs2.Fields("ODmm").Value) And _
       (rs1.Fields("WTmm").Value > rs2.Fields("RFS_required").Value) And (steelGradeCompare = 2) And (lengthCompare = 2) Then
    compareRecord = 2
ElseIf (dateDifference <= 30) And (rs1.Fields("RFS_required").Value = rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value = rs2.Fields("ODmm").Value) And _
       (rs1.Fields("WTmm").Value < rs2.Fields("RFS_required").Value) And (steelGradeCompare = 0) And (lengthCompare = 1) Then
    compareRecord = 1
ElseIf (dateDifference <= 30) And (rs1.Fields("RFS_required").Value = rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value = rs2.Fields("ODmm").Value) And _
       (rs1.Fields("WTmm").Value < rs2.Fields("RFS_required").Value) And (steelGradeCompare = 0) And (lengthCompare = 2) Then
    compareRecord = 1
ElseIf (dateDifference <= 30) And (rs1.Fields("RFS_required").Value = rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value = rs2.Fields("ODmm").Value) And _
       (rs1.Fields("WTmm").Value < rs2.Fields("RFS_required").Value) And (steelGradeCompare = 1) And (lengthCompare = 1) Then
    compareRecord = 1
ElseIf (dateDifference <= 30) And (rs1.Fields("RFS_required").Value = rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value = rs2.Fields("ODmm").Value) And _
       (rs1.Fields("WTmm").Value < rs2.Fields("RFS_required").Value) And (steelGradeCompare = 1) And (lengthCompare = 2) Then
    compareRecord = 1
ElseIf (dateDifference <= 30) And (rs1.Fields("RFS_required").Value = rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value = rs2.Fields("ODmm").Value) And _
       (rs1.Fields("WTmm").Value < rs2.Fields("RFS_required").Value) And (steelGradeCompare = 2) And (lengthCompare = 1) Then
    compareRecord = 1
ElseIf (dateDifference <= 30) And (rs1.Fields("RFS_required").Value = rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value = rs2.Fields("ODmm").Value) And _
       (rs1.Fields("WTmm").Value < rs2.Fields("RFS_required").Value) And (steelGradeCompare = 2) And (lengthCompare = 2) Then
    compareRecord = 1
ElseIf (dateDifference <= 30) And (rs1.Fields("RFS_required").Value = rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value = rs2.Fields("ODmm").Value) And _
       (rs1.Fields("WTmm").Value = rs2.Fields("RFS_required").Value) And (steelGradeCompare = 0) And (lengthCompare = 1) Then
    compareRecord = 1
ElseIf (dateDifference <= 30) And (rs1.Fields("RFS_required").Value = rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value = rs2.Fields("ODmm").Value) And _
       (rs1.Fields("WTmm").Value = rs2.Fields("RFS_required").Value) And (steelGradeCompare = 0) And (lengthCompare = 2) Then
    compareRecord = 2
ElseIf (dateDifference <= 30) And (rs1.Fields("RFS_required").Value = rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value = rs2.Fields("ODmm").Value) And _
       (rs1.Fields("WTmm").Value = rs2.Fields("RFS_required").Value) And (steelGradeCompare = 1) And (lengthCompare = 1) Then
    compareRecord = 2
ElseIf (dateDifference <= 30) And (rs1.Fields("RFS_required").Value = rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value = rs2.Fields("ODmm").Value) And _
       (rs1.Fields("WTmm").Value = rs2.Fields("RFS_required").Value) And (steelGradeCompare = 1) And (lengthCompare = 2) Then
    compareRecord = 2
ElseIf (dateDifference <= 30) And (rs1.Fields("RFS_required").Value = rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value = rs2.Fields("ODmm").Value) And _
       (rs1.Fields("WTmm").Value = rs2.Fields("RFS_required").Value) And (steelGradeCompare = 2) And (lengthCompare = 1) Then
    compareRecord = 1
ElseIf (dateDifference <= 30) And (rs1.Fields("RFS_required").Value = rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value = rs2.Fields("ODmm").Value) And _
       (rs1.Fields("WTmm").Value = rs2.Fields("RFS_required").Value) And (steelGradeCompare = 2) And (lengthCompare = 2) Then
    compareRecord = 1
ElseIf (dateDifference > 30) And (rs1.Fields("RFS_required").Value = rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value = rs2.Fields("ODmm").Value) And _
       (rs1.Fields("WTmm").Value = rs2.Fields("RFS_required").Value) And (steelGradeCompare = 0) And (lengthCompare = 1) Then
    compareRecord = 1
ElseIf (dateDifference > 30) And (rs1.Fields("RFS_required").Value = rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value = rs2.Fields("ODmm").Value) And _
       (rs1.Fields("WTmm").Value = rs2.Fields("RFS_required").Value) And (steelGradeCompare = 0) And (lengthCompare = 2) Then
    compareRecord = 2
ElseIf (dateDifference > 30) And (rs1.Fields("RFS_required").Value = rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value = rs2.Fields("ODmm").Value) And _
       (rs1.Fields("WTmm").Value = rs2.Fields("RFS_required").Value) And (steelGradeCompare = 1) And (lengthCompare = 1) Then
    compareRecord = 2
ElseIf (dateDifference > 30) And (rs1.Fields("RFS_required").Value = rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value = rs2.Fields("ODmm").Value) And _
       (rs1.Fields("WTmm").Value = rs2.Fields("RFS_required").Value) And (steelGradeCompare = 1) And (lengthCompare = 2) Then
    compareRecord = 2
ElseIf (dateDifference > 30) And (rs1.Fields("RFS_required").Value = rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value = rs2.Fields("ODmm").Value) And _
       (rs1.Fields("WTmm").Value = rs2.Fields("RFS_required").Value) And (steelGradeCompare = 2) And (lengthCompare = 1) Then
    compareRecord = 1
ElseIf (dateDifference > 30) And (rs1.Fields("RFS_required").Value = rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value = rs2.Fields("ODmm").Value) And _
       (rs1.Fields("WTmm").Value = rs2.Fields("RFS_required").Value) And (steelGradeCompare = 2) And (lengthCompare = 2) Then
    compareRecord = 1
ElseIf (dateDifference > 30) And (rs1.Fields("RFS_required").Value = rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value = rs2.Fields("ODmm").Value) And _
       (rs1.Fields("WTmm").Value < rs2.Fields("RFS_required").Value) And (steelGradeCompare = 0) And (lengthCompare = 1) Then
    compareRecord = 1
ElseIf (dateDifference > 30) And (rs1.Fields("RFS_required").Value = rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value = rs2.Fields("ODmm").Value) And _
       (rs1.Fields("WTmm").Value < rs2.Fields("RFS_required").Value) And (steelGradeCompare = 0) And (lengthCompare = 2) Then
    compareRecord = 1
ElseIf (dateDifference > 30) And (rs1.Fields("RFS_required").Value = rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value = rs2.Fields("ODmm").Value) And _
       (rs1.Fields("WTmm").Value < rs2.Fields("RFS_required").Value) And (steelGradeCompare = 1) And (lengthCompare = 1) Then
    compareRecord = 1
ElseIf (dateDifference > 30) And (rs1.Fields("RFS_required").Value = rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value = rs2.Fields("ODmm").Value) And _
       (rs1.Fields("WTmm").Value < rs2.Fields("RFS_required").Value) And (steelGradeCompare = 1) And (lengthCompare = 2) Then
    compareRecord = 1
ElseIf (dateDifference > 30) And (rs1.Fields("RFS_required").Value = rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value = rs2.Fields("ODmm").Value) And _
       (rs1.Fields("WTmm").Value < rs2.Fields("RFS_required").Value) And (steelGradeCompare = 2) And (lengthCompare = 1) Then
    compareRecord = 1
ElseIf (dateDifference > 30) And (rs1.Fields("RFS_required").Value = rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value = rs2.Fields("ODmm").Value) And _
       (rs1.Fields("WTmm").Value < rs2.Fields("RFS_required").Value) And (steelGradeCompare = 2) And (lengthCompare = 2) Then
    compareRecord = 1
ElseIf (dateDifference > 30) And (rs1.Fields("RFS_required").Value = rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value = rs2.Fields("ODmm").Value) And _
       (rs1.Fields("WTmm").Value > rs2.Fields("RFS_required").Value) And (steelGradeCompare = 0) And (lengthCompare = 1) Then
    compareRecord = 2
ElseIf (dateDifference > 30) And (rs1.Fields("RFS_required").Value = rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value = rs2.Fields("ODmm").Value) And _
       (rs1.Fields("WTmm").Value > rs2.Fields("RFS_required").Value) And (steelGradeCompare = 0) And (lengthCompare = 2) Then
    compareRecord = 2
ElseIf (dateDifference > 30) And (rs1.Fields("RFS_required").Value = rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value = rs2.Fields("ODmm").Value) And _
       (rs1.Fields("WTmm").Value > rs2.Fields("RFS_required").Value) And (steelGradeCompare = 1) And (lengthCompare = 1) Then
    compareRecord = 2
ElseIf (dateDifference > 30) And (rs1.Fields("RFS_required").Value = rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value = rs2.Fields("ODmm").Value) And _
       (rs1.Fields("WTmm").Value > rs2.Fields("RFS_required").Value) And (steelGradeCompare = 1) And (lengthCompare = 2) Then
    compareRecord = 2
ElseIf (dateDifference > 30) And (rs1.Fields("RFS_required").Value = rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value = rs2.Fields("ODmm").Value) And _
       (rs1.Fields("WTmm").Value > rs2.Fields("RFS_required").Value) And (steelGradeCompare = 2) And (lengthCompare = 1) Then
    compareRecord = 2
ElseIf (dateDifference > 30) And (rs1.Fields("RFS_required").Value = rs2.Fields("RFS_required").Value) And (rs1.Fields("ODmm").Value = rs2.Fields("ODmm").Value) And _
       (rs1.Fields("WTmm").Value > rs2.Fields("RFS_required").Value) And (steelGradeCompare = 2) And (lengthCompare = 2) Then
    compareRecord = 2
End If
rs1.Close
rs2.Close
db.Close
Set db = Nothing
Set rs1 = Nothing
Set rs2 = Nothing
dateDifference = 0
steelGradeCompare = 0
lengthCompare = 0
End Function
Function lengthCompareMethod(firstLength As String, secondLength As String) As Integer
 'Function return 1 as rs1 should place first
 'Function return 2 as rs1 should place second
If (firstLength <> "QRL") And (firstLength <> "TRL") And (firstLength <> "DRL") And (firstLength <> "SRL") And _
   (firstLength <> "R3") And (firstLength <> "R2") And (firstLength <> "R1") And (firstLength <> "") Then
    lengthCompareMethod = 2   ' when rs1 is rare length
ElseIf (secondLength <> "QRL") And (secondLength <> "TRL") And (secondLength <> "DRL") And (secondLength <> "SRL") And _
   (secondLength <> "R3") And (secondLength <> "R2") And (secondLength <> "R1") And (secondLength <> "") Then
     lengthCompareMethod = 1  ' when rs2 is rare length
Else  ' Other than rare length
    If (firstLength = "QRL") And ((secondLength = "TRL") Or (secondLength = "DRL") Or (secondLength = "SRL") Or (secondLength = "")) Then
       lengthCompareMethod = 2  'since rs1 have longer length line pipe      (1)
    ElseIf (firstLength = "TRL") And ((secondLength = "DRL") Or (secondLength = "SRL") Or (secondLength = "")) Then
       lengthCompareMethod = 2            '2
    ElseIf (firstLength = "DRL") And ((secondLength = "SRL") Or (secondLength = "")) Then
       lengthCompareMethod = 2            '3
    ElseIf (firstLength = "SRL") And (secondLength = "") Then
       lengthCompareMethod = 2           '4
    ElseIf (firstLength = "R3") And ((secondLength = "R2") Or (secondLength = "R1") Or (secondLength = "")) Then
       lengthCompareMethod = 2           '5
    ElseIf (firstLength = "R2") And ((secondLength = "R1") Or (secondLength = "")) Then
       lengthCompareMethod = 2            '6
    ElseIf (firstLength = "R1") And (secondLength = "") Then
       lengthCompareMethod = 2            '7
    ElseIf (firstLength = "") And (secondLength = "") Then
       lengthCompareMethod = 1            '8
    ElseIf (secondLength = "QRL") And ((firstLength = "TRL") Or (firstLength = "DRL") Or (firstLength = "SRL") Or (firstLength = "")) Then
       lengthCompareMethod = 1            '9
    ElseIf (secondLength = "TRL") And ((firstLength = "DRL") Or (firstLength = "SRL") Or (firstLength = "")) Then
       lengthCompareMethod = 1             '10
    ElseIf (secondLength = "DRL") And ((firstLength = "SRL") Or (firstLength = "")) Then
       lengthCompareMethod = 1             '11
    ElseIf (secondLength = "SRL") And (firstLength = "") Then
       lengthCompareMethod = 1             '12
    ElseIf (secondLength = "R3") And ((firstLength = "R2") Or (firstLength = "R1") Or (firstLength = "")) Then
       lengthCompareMethod = 1             '13
    ElseIf (secondLength = "R2") And ((firstLength = "R1") Or (firstLength = "")) Then
       lengthCompareMethod = 1             '14
    ElseIf (secondLength = "R1") And (firstLength = "") Then
       lengthCompareMethod = 1             '15
    End If
End If
End Function
Function sortPendingByDate()
 Dim strSQL As String
 
 strSQL = "SELECT * FROM Pending_Temp ORDER BY RFS_required;"
 
 
 DoCmd.SetWarnings False
 DoCmd.RunSQL strSQL
 DoCmd.SetWarnings True
 
 strSQL = ""
 
End Function
Function appendS20ToPendingOrder()
' This function append all the data from S20 to Pending Orders table
' Don't care the missing field variable from S20 table
Dim strSQL As String
strSQL = "INSERT INTO Pending_Temp (ODmm, WTmm, Steel_Grade, End, Total_Meters, Total_Tons, RFS_required )" & _
         " SELECT [tblS20].[ODmm], [tblS20].[WTmm], [tblS20].[Steel_Grade], [tblS20].[End], [tblS20].[Total_Meters]," & _
         " [tblS20].[Total_Tons], [tblS20].[RFS_required]" & _
         " FROM tblS20;"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
strSQL = ""
DoCmd.RefreshRecord
End Function
Function S20Filter()
'This function will filter all the date requested in the S20 file then delete
'all date which is not latest date
Dim strSQL As String
Dim latestDate As Date
Dim db As Database
Dim rsS20 As Recordset
Set db = CurrentDb
Set rsS20 = db.OpenRecordset("Query28")
latestDate = #1/1/1950#
       
DoCmd.OpenQuery "Query28", acViewNormal, acEdit
'May not need to run Query28 later, keep it for now
rsS20.MoveFirst
Do While Not rsS20.EOF
    If (Date - rsS20.Fields(0).Value) <= (Date - latestDate) Then
       latestDate = rsS20.Fields(0).Value
    End If
    
    rsS20.MoveNext
Loop
strSQL = ""
strSQL = "DELETE [Query28].[Date Requested]" & _
       " FROM [Query28]" & _
       " WHERE [Query28].[Date Requested] <> #" & latestDate & "#;"
       
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
db.Close
Set rsS20 = Nothing
Set db = Nothing
latestDate = #1/1/1950#
strSQL = ""
DoCmd.RefreshRecord
End Function

[Perceptus]

What error do you get for Rs1.edit?

I am guessing multiple columns comma seperated in the order by statement doesn't work?

[thape]

The error I got is Invalid Argument something in the Function loopPendingOrders.
Thanks

What error do you get for Rs1.edit?

I am guessing multiple columns comma seperated in the order by statement doesn't work?

[Perceptus]

Using Edit produces an error if:
There is no current record.
The Connection, Database, or Recordset object was opened as read-only.
No fields in the record are updatable.
The Database or Recordset was opened for exclusive use by another user (Microsoft Access workspace).
Another user has locked the page containing your record (Microsoft Access workspace).

[thape]

Thanks,
May be I didn't specify dbdynaset in the openrecordset part.
Any idea on sorting against recordset in vba Access???

Using Edit produces an error if:
There is no current record.
The Connection, Database, or Recordset object was opened as read-only.
No fields in the record are updatable.
The Database or Recordset was opened for exclusive use by another user (Microsoft Access workspace).
Another user has locked the page containing your record (Microsoft Access workspace).

[ssanfu]

I am confused on what you are trying to accomplish.

It seems like you are trying to sort a table, but Tables have no order. They are a "bit bucket".
To sort (have an order on) a table, create/open a query and set the sort row in the design grid to Ascending or Descending.



Other things..
----------------------------------------------------------------

Function sortPendingByDate()
 Dim strSQL As String
 
 strSQL = "SELECT * FROM Pending_Temp ORDER BY RFS_required;"
  
 DoCmd.SetWarnings False
 DoCmd.RunSQL strSQL
 DoCmd.SetWarnings True
 
 strSQL = ""
 
End Function

"RunSQL" is only effective on action queries. You cannot use it on a Select query.

In "Function appendS20ToPendingOrder()", at the end:

.
.
db.Close   'The rule is: you didn't open it, so don't close it. You don't want to close the "Currentdb". I made that mistake....once :)
rsS20.Close   '<<- missing recordset close statement
Set rsS20 = Nothing
Set db = Nothing

latestDate = #1/1/1950#  '<<- Doesn't matter/not needed. Will be set the next time the function is executed
strSQL = ""    '<<- Doesn't matter/not needed. Will be set the next time the function is executed

[thape]

Hi, Steve
Thanks for the tip on runSQL, I will change that.
The thing I am trying to do is sorting a table( I think table as an array of record) using VBA. The sorting rule is not just ascending/descending and it's based on mutilple field value. Each record is different from another. And the sorting rule have priority. If Level 1(Date) prority met, then compare the next Level(OD) then keep going ...

Hope this is clear
Thanks

I am confused on what you are trying to accomplish.

It seems like you are trying to sort a table, but Tables have no order. They are a "bit bucket".
To sort (have an order on) a table, create/open a query and set the sort row in the design grid to Ascending or Descending.



Other things..
----------------------------------------------------------------

Function sortPendingByDate()
 Dim strSQL As String
 
 strSQL = "SELECT * FROM Pending_Temp ORDER BY RFS_required;"
  
 DoCmd.SetWarnings False
 DoCmd.RunSQL strSQL
 DoCmd.SetWarnings True
 
 strSQL = ""
 
End Function

"RunSQL" is only effective on action queries. You cannot use it on a Select query.

In "Function appendS20ToPendingOrder()", at the end:

.
.
db.Close   'The rule is: you didn't open it, so don't close it. You don't want to close the "Currentdb". I made that mistake....once :)
rsS20.Close  '<<- missing recordset close statement
Set rsS20 = Nothing
Set db = Nothing

latestDate = #1/1/1950#  '<<- Doesn't matter/not needed. Will be set the next time the function is executed
strSQL = ""    '<<- Doesn't matter/not needed. Will be set the next time the function is executed

[orange]

Suggest you try query(ies) as Steve pointed out.

Should be something like this:

Select * from yourTable
Where yourCriteria
Order By field1 asc, field2 desc,....field6 asc;

Also, a table with 40 fields may not be Normalized. 40 seems a high number, but you know your data and we don't.

[thape]

Hi, Orange
I try query before, but didn't work at the end or I don't know how.
My data are like these:
Date- as date from 1/1/2012 to 9/31/2013
OD- as double from 80 to 200
WT- as double from 80 to 200
SteelGrade as string
Length - as string

for steelgrade and length value could be null
My problem is how you arrange the filed1,field2 ....value in priority using the rule given.

thanks

Suggest you try query(ies) as Steve pointed out.

Should be something like this:

Select * from yourTable
Where yourCriteria
Order By field1 asc, field2 desc,....field6 asc;

Also, a table with 40 fields may not be Normalized. 40 seems a high number, but you know your data and we don't.

[ssanfu]

Still clear as mud. That is a lot of code to look at.

The thing I am trying to do is sorting a table( I think table as an array of record)

I have to make this point again.. There is no inherent record order in an Access table!!


OK, so this is what I know:
1) You have a temp table that has approx 40 fields
2) You are only concerned with 6 fields - sorting on those fields.
3) You have a *lot* of code

To be able to get the data from the temp table in the order you require (using a query), you would have to add another field (if you don't already have it) to hold a "Sort order" or "record order". Lets call that field "lngSortOrder".

Open a recordset on the temp table. Run VBA code to loop through the recordset, updating the field "lngSortOrder", incrementing by one.

Now you can do the bubble sort or however your code functions (haven't waded through it) to "sort" the records.
Open a record set ordered by the field "lngSortOrder". Ta-da, the records are sorted!!


It is hard to evaluate the code without data. Would you be able to provide a copy of your dB with 10 or 15 records?

[thape]

Hi, Steve
Thank you so much.
I am new to Access. Learn SQL couple weeks ago have some exprience in VB, no exprience in database.

My module work like this:
Function createMillTable()
--- call some short sub that run SQL to get the final table I want for example function sortPendingTable(),
function appendS20()
function s20filter()

---- call function loopThrough() this will loop through the table above
inside loopThrough will call
compareRecord(firstRecord,secondRecord)
compareRecord() will compare two record base on the field values and using the rule. compareRecord() will return 1 or 2 ,which 1 means first record place first. 2 means first record place 2nd.
Bubble Sort work like this start with 1st data, compare two adajacent data at a time, the highest data goes to the end. Continue this compare to the end of the data. Now the highest data is at the end. Then start with 1st data again, then 2nd highest data place before the highes data.)

5

2

4

3

1

Original Data

2

4

3

1

5

After first loop

2

3

1

4

5

after 2nd loop

And my data is like this:
Original data

OD	WT	SteelGrade	Date	Length
88.9	4	G5	1/1/2012	T
88.9	4.65	J5	2/1/2012	D
114.3	6.35	J5	6/7/2013	D
114.3	6.35	G5	6/7/2013	S
114.3	5.69	J5	6/7/2013	D
219.1	5.59	J5	7/23/2013	null
219.1	6.45	J5	7/24/2013	null
219.1	6.45	J5	8/22/2013	null
177.8	5.69	G5	7/21/2013	3
177.8	5.69	null	7/27/2013	2
177.8	6.45	null	9/21/2013	1

The output should base on the rules.
Overall sorted output should in ascending order base on OD and Date. If any two record with same OD and within 30 days difference should be grouping together.

thanks
my writing is bad,,lol

[orange]

Quite honestly I'd like to know what the criteria are in plain English before attempting to adjust/advise on code.

If this is your input

And my data is like this:
Original data

OD	WT	SteelGrade	Date	Length
88.9	4	G5	1/1/2012	T
88.9	4.65	J5	2/1/2012	D
114.3	6.35	J5	6/7/2013	D
114.3	6.35	G5	6/7/2013	S
114.3	5.69	J5	6/7/2013	D
219.1	5.59	J5	7/23/2013	null
219.1	6.45	J5	7/24/2013	null
219.1	6.45	J5	8/22/2013	null
177.8	5.69	G5	7/21/2013	3
177.8	5.69	null	7/27/2013	2

Can you show us what your expected output is?

What exactly does this mean

If any two record with same OD and within 30 days difference should be grouping together.

[thape]

Hi, Steve
Thanks again

My desired output is this

OD	WT	SteelGrade	Date	Length
88.9	4	G5	1/1/2012	T
88.9	4.65	J5	2/1/2012	D
114.3	5.69	J5	6/7/2013	D
114.3	6.35	J5	6/7/2013	D
114.3	6.35	G5	6/7/2013	S
177.8	5.69	G5	7/21/2013	3
177.8	5.69	null	7/27/2013	2
219.1	5.69	J5	7/23/2013	null
219.1	6.45	J5	7/24/2013	null
219.1	6.45	J5	8/22/2013	null

For OD=Outside Diameter, WT= wall thickness, Length can be Double, Single, Tripple Length or length 3 length 2

The sentence mean if any two record have same OD and the difference between the date is <= 30 days (there are other record with different OD in between these two record), then the 2nd record should place right after the first record.

114.3	4.65	J5	1/1/2013	T
119.5	4.65	J5	1/5/2013	T
114.3	4.65	J5	1/31/2013	T

The last record(red) should place 2nd.


Thanks

[ssanfu]

The previous post was from Orange, not me. I am still working my way through your code.

I can see where you are comparing the values on two records of two recordsets (from the same table), then swapping the values of the fields to "sort" the two records (the bubble sort). This will work in Excel or a text field or one or the X-base files. But rearranging (sorting) the records in an Access table is a waste of time!!!
See this page about record order: http://www.utteraccess.com/wiki/index.php/Record_Order

It would be so much faster and easier if you had a field to indicate the "sort order".

I would be interested in seeing the table structure of the table "Pending_Temp".

[orange]

Hi Steve.
I haven't looked at any of the code. I didn't understand why the bubble sort, but your description confirms my "fear".
I still don't understand the underlying issue. I see a request for some sorting and possible rearrangement, but have no idea why -- if something with the same OD exists within 30 days of a given record, it should be grouped with that record(s).

Sounds like a managerial report request, not a business fact/need per se.

Any way, glad you're looking at the code. Good luck.