SQL returning table in wrong order.

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,296
Office Version
  1. 365
Platform
  1. Windows
Not sure how to explain this one.

I have a table with 184 records in it.
Code:
SELECT *FROM tbl_NewInstructions
returns every record in the same order as the table.
Code:
SELECT sAddress
FROM tbl_NewInstructions
does the same, but brings back only the sAddress field.

But if I ask it to look at the bCleansed boolean field where all values are FALSE it returns the full recordset again, but starts at record 10 and loops around so the first 10 records are attached to the bottom. I've tried here - NOT bCleansed and bCleansed = FALSE and bCleansed = 0 - but they all return the same.
Code:
SELECT sAddress
FROM tbl_NewInstructions
WHERE NOT bCleansed
Not a bad thing, after all the position of data in a table isn't relevant.

But if I use the query in the code below it misses records - I've stepped through the code, and although it's not quite perfect - the last record in a batch is also the first record in the next batch - it seems to suddenly miss a bunch of records for no reason that I can see.

Does anyone have any ideas as to why this behaviour occurs?

Code:
Private Const BATCH_SIZE As Long = 10

Public Sub CleanseRecords()

    Dim db As dao.Database
    Dim rs1 As dao.Recordset
    Dim rs2 As Variant
    Dim sBatch As String
    Dim lRecordCount As Long
    Dim lSeekRecord As Long
    Dim x As Long
    
    Set db = CurrentDb
    
    Set rs1 = _
        db.OpenRecordset("SELECT * FROM tbl_NewInstructions WHERE NOT bCleansed", dbOpenDynaset)
    
    lRecordCount = 0
    With rs1
        If Not .EOF Then
            .MoveLast
            .MoveFirst
            Do While Not .EOF
                lRecordCount = lRecordCount + 1
                
                ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                'If the record count has not reached the batch size or end of file then  '
                'add the address to the batch string.                                    '
                ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                If lRecordCount <= BATCH_SIZE Or .EOF Then
                    sBatch = sBatch & """" & rs1!sAddress & ", " & rs1!sPostCode & ""","
                End If
                
                ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                'If the record count is equal to the batch size or end of file then  '
                'process the addresses within the batch string.                      '
                ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                If lRecordCount = BATCH_SIZE Or .EOF Then
                    sBatch = left(sBatch, Len(sBatch) - 1)
                    Debug.Print sBatch
                    UpdateSwitchboardProgress ("Cleaning records " & .AbsolutePosition + 1 - BATCH_SIZE & _
                            " to " & .AbsolutePosition + 1 & " of " & .RecordCount)
                    DoEvents
                    Set rs2 = CleansePlus_Batch_Cleanse_v1_00(LICENSE_KEY, sBatch, "StrictProperty", 5, True, False)
                    
                    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                    'As we have been stepping through the recordset we need to move '
                    'back by the same amount of steps to the start of this batch.   '
                    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                    For x = BATCH_SIZE - 1 To 1 Step -1
                        rs1.MovePrevious
                    Next x
                    
                    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                    'Now step through the cleansed address recordset and the           '
                    'database recordset placing clean addresses in the correct record. '
                    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                    For x = 1 To BATCH_SIZE - 1
                        .Edit
                        rs1!sLine1 = IIf(rs2.Fields("Line1") = "", Null, rs2.Fields("Line1"))
                        rs1!sLine2 = IIf(rs2.Fields("Line2") = "", Null, rs2.Fields("Line2"))
                        rs1!sLine3 = IIf(rs2.Fields("Line3") = "", Null, rs2.Fields("Line3"))
                        rs1!sLine4 = IIf(rs2.Fields("Line4") = "", Null, rs2.Fields("Line4"))
                        rs1!sLine5 = IIf(rs2.Fields("Line5") = "", Null, rs2.Fields("Line5"))
                        rs1!sCleanTown = IIf(rs2.Fields("PostTown") = "", Null, rs2.Fields("PostTown"))
                        rs1!sCleanCounty = IIf(rs2.Fields("County") = "", Null, rs2.Fields("County"))
                        rs1!sCleanPostCode = IIf(rs2.Fields("Postcode") = "", Null, rs2.Fields("Postcode"))
                        rs1!bCleansed = True
                        .Update
                        rs1.MoveNext
                        rs2.MoveNext
                    Next x
                    
                    ''''''''''''''''''''''''''''''''''''''''''''''
                    'Reset the counter and batch address string. '
                    ''''''''''''''''''''''''''''''''''''''''''''''
                    If lRecordCount = BATCH_SIZE Then
                        lRecordCount = 0
                        sBatch = ""
                    End If
                    
                End If
                If lRecordCount <> 0 Then
                    .MoveNext
                End If
            Loop
        End If
    End With
    
    UpdateSwitchboardProgress ("Removing invalid addresses.")
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "DDL_Delete_NoCleanAddress"
    DoCmd.SetWarnings True


    UpdateSwitchboardProgress ("")


End Sub
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
What does CleansePlus_Batch_Cleanse_v1_00() do?

Do you have to do all this batching and moving back and forth in the recordset? Seems like it would be much simpler to just go from the first to the last in order.
 
Upvote 0
Thanks for the reply - CleansePlus_Batch_Cleanse_v1_00() comes from http://www.postcodeanywhere.co.uk/support/webservices/CleansePlus/Batch/Cleanse/v1/default.aspx

It's supposed to clean addresses - give it a badly formatted UK address and it looks up on the PAF (Postcode Address File) and returns the officially formatted address. The code for this is below (Key is the license key - have to pay for this stuff):
Code:
'----------------------------------------------------------------------------------
' Procedure : CleansePlus_Batch_Cleanse_v1_00
' Author    :
' Date      : 07/04/2014
' Purpose   : http://www.postcodeanywhere.co.uk/support/webservices/CleansePlus/Batch/Cleanse/v1/default.aspx
'-----------------------------------------------------------------------------------
Function CleansePlus_Batch_Cleanse_v1_00(Key, Addresses, MatchLevel, Lines, SeparateOutCompanyAndDepartment, SeparateOutTownCountyPostcode)


      Dim strUrl
      Dim rst


      'Build the url
      strUrl = "http://services.postcodeanywhere.co.uk/CleansePlus/Batch/Cleanse/v1.00/recordset.ws?"
      strUrl = strUrl & "&Key=" & (Key)
      strUrl = strUrl & "&Addresses=" & (Addresses)
      strUrl = strUrl & "&MatchLevel=" & (MatchLevel)
      strUrl = strUrl & "&Lines=" & (Lines)
      strUrl = strUrl & "&SeparateOutCompanyAndDepartment=" & (SeparateOutCompanyAndDepartment)
      strUrl = strUrl & "&SeparateOutTownCountyPostcode=" & (SeparateOutTownCountyPostcode)


      'Create the recordset
      Set rst = CreateObject("ADODB.Recordset")
      rst.Open strUrl


      'Check for an error
      If rst.Fields.Count = 4 Then
         If rst.Fields(0).Name = "Error" Then
           Err.Raise rst.Fields(0), "Webservice Error", rst.Fields(1)
        End If
      End If


      'Return the recordset
      Set CleansePlus_Batch_Cleanse_v1_00 = rst


      'FYI: The recordset returns the following columns:
      'Udprn
      'Company
      'Department
      'Line1
      'Line2
      'Line3
      'Line4
      'Line5
      'PostTown
      'County
      'Postcode
      'Barcode
      'Type
      'DeliveryPointSuffix
      'SubBuilding
      'BuildingName
      'BuildingNumber
      'PrimaryStreet
      'SecondaryStreet
      'DoubleDependentLocality
      'DependentLocality
      'PoBox
      'PrimaryStreetName
      'PrimaryStreetType
      'SecondaryStreetName
      'SecondaryStreetType
      'Outcome
End Function

I'm only batching it up as Postcode anywhere can deal with multiple addresses and it makes it a bit quicker if it doesn't have to keep passing addresses to the website. But yes, it would be much simpler to do it one at a time - the code works then!

I've updated the original code, but still not working with batches (i.e. if BATCH_SIZE is set to anything but 1):
Code:
Public Sub CleanseRecords()


    Dim db As dao.Database
    Dim rs1 As dao.Recordset
    Dim rs2 As Variant
    Dim sBatch As String
    Dim lRecordCount As Long
    Dim lSeekRecord As Long
    Dim x As Long, lBatchSize As Long
    Dim sAdd As String
    
    Set db = CurrentDb
    lBatchSize = BATCH_SIZE
    
    Set rs1 = _
        db.OpenRecordset("SELECT * FROM tbl_NewInstructions WHERE NOT bCleansed ORDER BY ListingKey", dbOpenDynaset)
        
    With rs1
        If Not .EOF Then
            ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
            'Move to the end of the recordset and back again to gain the record count. '
            ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
            .MoveLast
            .MoveFirst
            Do While Not .EOF
                ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                'Grab a number of records equal to lBatchSize and concatenate the addresses. '
                ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                For x = 0 To lBatchSize
                    If .EOF Then
                        lBatchSize = x
                        Exit For
                    End If
                    sAdd = PreCleanAddress(rs1!sAddress, rs1!sPostCode)
                    sBatch = sBatch & """" & sAdd & ", " & rs1!sPostCode & ""","
                    WriteLog x & " : " & rs1.AbsolutePosition & " : " & sBatch, "S:\Bartrup-CookD\DB Solution\Code.txt"
                    .MoveNext
                Next x
                sBatch = left(sBatch, Len(sBatch) - 1)
                
                Set rs2 = CleansePlus_Batch_Cleanse_v1_00(LICENSE_KEY, sBatch, "StrictProperty", 5, False, True)
                
                '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                'As we have been stepping through the recordset we need to move '
                'back by the same amount of steps to the start of this batch.   '
                '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                For x = lBatchSize To 0 Step -1
                    .MovePrevious
                Next x
                
                ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                'Now step through the cleansed address recordset and the           '
                'database recordset placing clean addresses in the correct record. '
                ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                rs2.MoveLast
                rs2.MoveFirst
                If rs2.RecordCount - 1 < BATCH_SIZE Then Debug.Assert False
                For x = 0 To rs2.RecordCount - 1
                    .Edit
                    rs1!sLine1 = IIf(rs2.Fields("Line1") = "", Null, rs2.Fields("Line1"))
                    rs1!sLine2 = IIf(rs2.Fields("Line2") = "", Null, rs2.Fields("Line2"))
                    rs1!sLine3 = IIf(rs2.Fields("Line3") = "", Null, rs2.Fields("Line3"))
                    rs1!sLine4 = IIf(rs2.Fields("Line4") = "", Null, rs2.Fields("Line4"))
                    rs1!sLine5 = IIf(rs2.Fields("Line5") = "", Null, rs2.Fields("Line5"))
                    rs1!sCleanTown = IIf(rs2.Fields("PostTown") = "", Null, rs2.Fields("PostTown"))
                    rs1!sCleanCounty = IIf(rs2.Fields("County") = "", Null, rs2.Fields("County"))
                    rs1!sCleanPostCode = IIf(rs2.Fields("Postcode") = "", Null, rs2.Fields("Postcode"))
                    rs1!bCleansed = True
                    .Update
                    .MoveNext
                    rs2.MoveNext
                Next x
                
                sBatch = ""
                
            Loop
        End If
    End With
    
'    UpdateSwitchboardProgress ("Removing invalid addresses.")
'    DoCmd.SetWarnings False
'    DoCmd.OpenQuery "DDL_Delete_NoCleanAddress"
'    DoCmd.SetWarnings True
'
'    UpdateSwitchboardProgress ("")


End Sub

'---------------------------------------------------------------------------------------
' Procedure : WriteChangeLog
' Author    : Darren Bartrup-Cook
' Date      : 25/10/2013
' Purpose   :
' To Use    :
'---------------------------------------------------------------------------------------
Public Sub WriteLog(LogMessage As String, LogFileName As String)
    Dim FileNum As Integer
    FileNum = FreeFile ' next file number
    Open LogFileName For Append As #FileNum ' creates the file if it doesn't exist
    Print #FileNum, LogMessage ' write information at the end of the text file
    Close #FileNum ' close the file
End Sub




Private Function PreCleanAddress(sAddress As String, sPostCode) As String
    Dim sPostCodeIn As String, sPostCodeOut As String
    
    sPostCodeOut = Trim(left(sPostCode, InStr(sPostCode, " ")))
    sPostCodeIn = Mid(sPostCode, InStr(sPostCode, " ") + 1, Len(sPostCode))
    
    sAddress = Replace(sAddress, sPostCodeOut, "")
    sAddress = Replace(sAddress, sPostCodeIn, "")
    
    PreCleanAddress = sAddress
    
End Function

Just realised to that I changed the SQL to "SELECT * FROM tbl_NewInstructions WHERE NOT bCleansed ORDER BY ListingKey". The ORDER BY now returns the whole dataset in the correct order, but the stepping through in batches misses some records.
 
Last edited:
Upvote 0
This is my effort at a rewrite. Completely untested so take with a grain of salt and step through it.

Basically:
1) using a bookmark to mark the record you want to return to (the first record in the batch). This should be the safest way for that.
2) I also edited the batch size counting - you want to count from 1 when counting and using sizes for batches (i.e., I mean that counts are always one-based, like your ADO recordCount property, and most other types of counts such as collection counts - so the code is easier to understand if you stay with that convention). Also the for loop increments one past the number you set as the max, and that can be confusing, so I used a do loop instead, and captured the final value for the number of records in the batch (which will now be always the batch size, except the last batch which may be less than the batch size).
Code:
[COLOR="Navy"]Public[/COLOR] [COLOR="Navy"]Sub[/COLOR] CleanseRecords()


    [COLOR="Navy"]Dim[/COLOR] db [COLOR="Navy"]As[/COLOR] dao.Database
    [COLOR="Navy"]Dim[/COLOR] rs1 [COLOR="Navy"]As[/COLOR] dao.Recordset
    [COLOR="Navy"]Dim[/COLOR] rs2 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Variant[/COLOR]
    [COLOR="Navy"]Dim[/COLOR] sBatch [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
    [COLOR="Navy"]Dim[/COLOR] lRecordCount [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
    [COLOR="Navy"]Dim[/COLOR] lSeekRecord [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
    [COLOR="Navy"]Dim[/COLOR] x [COLOR="Navy"]As[/COLOR] Long, lThisBatchSize [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
    [COLOR="Navy"]Dim[/COLOR] sAdd [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
    [COLOR="Navy"]Dim[/COLOR] varFirstRecordInBatchBookmark
    
    [COLOR="Navy"]Set[/COLOR] db = CurrentDb
    
    [COLOR="Navy"]Set[/COLOR] rs1 = _
        db.OpenRecordset("SELECT * FROM tbl_NewInstructions WHERE NOT bCleansed ORDER BY ListingKey", dbOpenDynaset)
        
    [COLOR="Navy"]With[/COLOR] rs1
        [COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] .EOF [COLOR="Navy"]Then[/COLOR]

            [COLOR="Navy"]Do[/COLOR] [COLOR="Navy"]While[/COLOR] [COLOR="Navy"]Not[/COLOR] .EOF
                [COLOR="SeaGreen"]''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''[/COLOR]
                [COLOR="SeaGreen"]'Grab a number of records equal to lBatchSize and concatenate the addresses. '[/COLOR]
                [COLOR="SeaGreen"]''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''[/COLOR]
                
                varFirstRecordInBatchBookmark = .Bookmark
                x = 1
                [COLOR="Navy"]Do[/COLOR]
                    sAdd = PreCleanAddress(rs1!sAddress, rs1!sPostCode)
                    sBatch = sBatch & """" & sAdd & ", " & rs1!sPostCode & ""","
                    WriteLog x & " : " & rs1.AbsolutePosition & " : " & sBatch, "S:\Bartrup-CookD\DB Solution\Code.txt"
                    x = x + 1
                    .MoveNext
                [COLOR="Navy"]Loop[/COLOR] [COLOR="Navy"]While[/COLOR] (x <= BATCH_SIZE) [COLOR="Navy"]And[/COLOR] (Not .EOF)
                lThisBatchSize = x - 1
                
                sBatch = Left(sBatch, Len(sBatch) - 1)
                [COLOR="Navy"]Set[/COLOR] rs2 = CleansePlus_Batch_Cleanse_v1_00(LICENSE_KEY, sBatch, "StrictProperty", 5, False, True)
                
                [COLOR="SeaGreen"]'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''[/COLOR]
                [COLOR="SeaGreen"]'As we have been stepping through the recordset we need to move '[/COLOR]
                [COLOR="SeaGreen"]'back to the start of this batch.   '[/COLOR]
                [COLOR="SeaGreen"]'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''[/COLOR]
                .Bookmark = varFirstRecordInBatchBookmark
                                
                [COLOR="SeaGreen"]''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''[/COLOR]
                [COLOR="SeaGreen"]'Now step through the cleansed address recordset and the           '[/COLOR]
                [COLOR="SeaGreen"]'database recordset placing clean addresses in the correct record. '[/COLOR]
                [COLOR="SeaGreen"]''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''[/COLOR]
                rs2.MoveFirst
                Debug.Assert rs2.RecordCount = lThisBatchSize
                [COLOR="Navy"]For[/COLOR] x = 0 [COLOR="Navy"]To[/COLOR] rs2.RecordCount - 1
                    .Edit
                    rs1!sLine1 = IIf(rs2.Fields("Line1") = "", Null, rs2.Fields("Line1"))
                    rs1!sLine2 = IIf(rs2.Fields("Line2") = "", Null, rs2.Fields("Line2"))
                    rs1!sLine3 = IIf(rs2.Fields("Line3") = "", Null, rs2.Fields("Line3"))
                    rs1!sLine4 = IIf(rs2.Fields("Line4") = "", Null, rs2.Fields("Line4"))
                    rs1!sLine5 = IIf(rs2.Fields("Line5") = "", Null, rs2.Fields("Line5"))
                    rs1!sCleanTown = IIf(rs2.Fields("PostTown") = "", Null, rs2.Fields("PostTown"))
                    rs1!sCleanCounty = IIf(rs2.Fields("County") = "", Null, rs2.Fields("County"))
                    rs1!sCleanPostCode = IIf(rs2.Fields("Postcode") = "", Null, rs2.Fields("Postcode"))
                    rs1!bCleansed = True
                    .Update
                    .MoveNext
                    rs2.MoveNext
                [COLOR="Navy"]Next[/COLOR] x
                
                sBatch = ""
                
            [COLOR="Navy"]Loop[/COLOR]
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
    
[COLOR="SeaGreen"]'    UpdateSwitchboardProgress ("Removing invalid addresses.")[/COLOR]
[COLOR="SeaGreen"]'    DoCmd.SetWarnings False[/COLOR]
[COLOR="SeaGreen"]'    DoCmd.OpenQuery "DDL_Delete_NoCleanAddress"[/COLOR]
[COLOR="SeaGreen"]'    DoCmd.SetWarnings True[/COLOR]
[COLOR="SeaGreen"]'[/COLOR]
[COLOR="SeaGreen"]'    UpdateSwitchboardProgress ("")[/COLOR]


[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
 
Last edited:
Upvote 0
Thank you Xenou. I'm off home now, but will test in the morning and let you know. I've not used bookmark before in a recordset - makes sense though.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top