VBA to Query

[edojan]

I need to convert this into query:
The code loops through the recordset and checks to see if the current records NDC is equal to the value of the previous record, If it is set the date to that record to 1 day less than the previous record, if it's not set it to 12/31/9999

The first date it uses (for the first record) is 01/01/1901

   Dim rst As Recordset
    Dim strSQL As String
    Dim tempNDC As String
    Dim tempDATE As Date
    Dim TotalCount As Double
    
    DoEvents
    strSQL = "SELECT NDC, AWPDate, AWPExp FROM AWP ORDER BY NDC, AWPDate DESC;"

    Set db = DBEngine.Workspaces(0).Databases(0)
    Set rst = db.OpenRecordset(strSQL)
    
    If rst.RecordCount = 0 Then
        MsgBox "No record found"
        Exit Function
    End If
    
    TotalCount = rst.RecordCount
    rst.MoveFirst
    tempNDC = ""
    tempDATE = #1/1/1901#
    
    Do While Not rst.EOF
        'Me.Caption = " processing....." & (Int((rst.AbsolutePosition / TotalCount) * 90) + 10)
        rst.Edit
        If tempNDC = rst("NDC") Then
            rst("AWPexp") = tempDATE - 1
        Else
            rst("AWPexp") = #12/31/9999#
        End If
        rst.Update
        tempNDC = rst("NDC")
        tempDATE = rst("AWPdate")
        rst.MoveNext
        
    Loop
        'Me.Caption = " processing.....100%"

 

[Moniker]

That won't readily convert to a query because you can't carry a variable when switching records. In other words, you can't say, "If this date equals the previous record's date, then change it to X, otherwise change it to Y."

The code you have is the way to do it, but you're going down a slippery slope with that sort of structure.

If you really want to use that structure for whatever reason, then you'll have to write a few update queries that put the previous record's date value into a new field on the current record. You can only compare fields from the current record against each other. Again, though, this is a sloppy way to go about it because it's just asking to be broken.

It's my suggestion that you rethink the structure as it's too easy to break the current one. For example, every time you have to edit an added date, that potentially breaks everything that comes directly before it and after it. There's no way to track changes in that structure either.

Think if you were writing this on paper. If you had a list of 200 dates on paper and you changed date 100, how would that affect every date before and after it, adversely or otherwise? It quickly becomes a nightmare, although you might keep the pencil eraser company in business.