By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,455 Members | 1,979 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,455 IT Pros & Developers. It's quick & easy.

How to delete rows based on 2 criteria using a recordset

100+
P: 547
I am stuck.
I have a table called RacetimingT and would like to delete duplicate "Racenumbers" added within 3 min of each other, using the "RaceFinishTime" field in this same table,(General Date field type )as criteria.
The "Id" field is called "racetimingId"
I also made a duplicate table of the "RacetimingT" table called "RaceTimingTold" just in case

The important issue is that the original Racenumber and RaceFinishTime records must not be deleted pls. Only subsequent additions within 3 min.

Expand|Select|Wrap|Line Numbers
  1. Dim rs1 As Recordset
  2. Dim myRaceFinishTime As Date, myRaceNumber As String, x As Long
  3.  
  4. DoCmd.DeleteObject acTable, "RaceTimingTold"
  5. DoCmd.CopyObject , "RaceTimingTold", acTable, "RaceTimingT"
  6.  
  7. RefreshDatabaseWindow
  8.  
  9. Set rs1 = CurrentDb.OpenRecordset("RaceTimingT", dbOpenDynaset)
  10. x = 0
  11. rs1.MoveFirst
  12. With rs1
  13.     Do Until .EOF
  14.         myRaceNumber = !RaceNumber   'racenumber is a numberfield type
  15.         myRaceFinishTime = !RaceFinishTime
  16.         .MoveNext
  17.             Do Until .EOF 'CHECK ALL RECORDS FOR THE SAME RACENUMBER AND
  18.                                ' TIMES WITHIN 3 MINUTES OF THE ORIGINAL RECORD
  19.                     If (!RaceNumber = myeRaceNumber) And _
  20.                         (DateDiff("n", myRaceFinishTime, !RaceFinishTime) > -3 And _
  21.                         DateDiff("n", myRaceFinishTime, !RaceFinishTime) < 3) Then
  22.                             .delete 'DELETE THE DUPLICATE RECORD IF THE TWO
  23.                                     ' TIMES ARE LESS THAN 3 MINUTES APART
  24.                     End If
  25.                         .MoveNext
  26.             Loop
  27.                 .MoveFirst
  28.                 x = x + 1
  29.                 .Move x 'MOVE TO THE NEXT RECORD IN LINE FOR CHECKING
  30.     Loop
  31. End With
  32.  
  33. rs1.close
  34. Set rs1 = Nothing
Anybody knowing about recordsets that can please assist me?
Jun 2 '12 #1
Share this Question
Share on Google+
4 Replies


100+
P: 547
Sorry i found my error -- row 19 but
Perhaps this code shown above will also help somebody else to delete records added within a specific time.

Expand|Select|Wrap|Line Numbers
  1. (!RaceNumber = myeRaceNumber)
should have been
Expand|Select|Wrap|Line Numbers
  1. (!RaceNumber = myRaceNumber)
Jun 2 '12 #2

100+
P: 759
If you use the Option Explicit you will avoid in the future this type of mistakes.

Cheers !
Jun 4 '12 #3

100+
P: 547
Thx Mihail for the assistance
Jun 4 '12 #4

NeoPa
Expert Mod 15k+
P: 31,186
Neels, I've put together some instructions on certain things to do and avoid for posting here (Before Posting (VBA or SQL) Code). It's not just to tell people how they should be posting. It can also be very helpful for avoiding some of the more common errors when working with code. You will certainly benefit if you follow the guidelines therein.
Jun 4 '12 #5

Post your reply

Sign in to post your reply or Sign up for a free account.