@izharmel
If you are working in tables let us assume your existing tablename called
T_Table1 that has a field called
'Time' (
not a good idea to name fields as internal functions or reserved words by the way)
If you have a time value duplicated how many times is 'each' date value actually duplicated? If it was just once then ok you could do a quick 'move next' , do your amendments and 'move back' and keep doing that as you traverse a single recordset to the EOF.
But working on the logic that you have no idea how many occurrences there are then sorting your recordset by the time column at least groups the 'identicals' together to reflect a visual on this.
The following recordset code logic opens TWO recordsets. The first recordset basically grouping by the Time column and 'counting' the instances of each individual time returning ONLY those times where the count is greater than ONE......this identifies your duplicates.
The idea then is to trundle down this recordset in a loop and open a 'second' recordset that relates only to the individual Time value encountered. It is in this second recordset that any 'DateAdd' function field editing logic takes place to increment or change the time value to suit yourself (ie: rs.edit.....rs.Update and so on). For each of these individual recordsets you can amend the time value for each and every row encountered.
I am sure you will get the logic looking at the recordset code below. You can safely run it in the immediate window to see how it reflects your data as it is, It is heavy on the Debug.Print statements just to illustrate the flow. The SQL should work hopefully given I have taken your existing naming convention
I havent gone into your 'DateAdd' logic in this post, as I feel you are well familiar with that and what it is you need to do there
Post the following code into a new module and save it. Then type
DoMyDates in the immediate window and hit the enter key.
- Function DoMyDates()
-
Dim db As DAO.Database
-
Dim rsDup As DAO.Recordset
-
Dim rs As DAO.Recordset
-
Set db = CurrentDb
-
SQLDup = "SELECT T_Table1.Time, Count(T_Table1.Time) AS TotalOf "
-
SQLDup = SQLDup & "FROM T_Table1 GROUP BY T_Table1.Time HAVING (((Count(T_Table1.Time))>1));"
-
Set rsDup = db.OpenRecordset(SQLDup, dbOpenSnapshot)
-
rsDup.MoveFirst
-
lc = 1
-
Do While Not rsDup.EOF
-
Debug.Print "--------------------------------------------------------------------------"
-
Debug.Print "THIS IS THE DUPLICATED OUTER LOOP TIME VALUE NO(" & lc & ") " & rsDup!Time
-
Debug.Print "--------------------------------------------------------------------------"
-
Set rs = db.OpenRecordset("SELECT * FROM T_Table1 WHERE [Time]=#" & rsDup!Time & "#", dbOpenDynaset)
-
rcnt = 1
-
Do While Not rs.EOF
-
If rcnt = 1 Then
-
Debug.Print "We are going to skip the first row (" & rcnt & ") time value " & rs!Time
-
Else
-
'This is where you do you DateAdd logic each time in the loop to suit you
-
Debug.Print "...and amend this inner loop row (" & rcnt & ") " & rs!Time 'do the dateadd logic
-
-
End If
-
rcnt = rcnt + 1
-
rs.MoveNext
-
Loop
-
rs.Close
-
rsDup.MoveNext
-
lc = lc + 1
-
Loop
-
rsDup.Close
-
db.Close
-
-
Set rs = Nothing
-
Set rsDup = Nothing
-
Set db = Nothing
-
End Function
27
