Access 97 question 3

[tdpman]

I have a Access table that contains many duplicate records. One of the fields within this table is a date field. I am needing some code (or something) that will delete all the duplicate records except the one with the earliest date. Any ideas? THX!

Examples:

SO NUMBER LOA LOC SO VERSION LOA OUTPUT CODE DATE LOA SENT
1959066 WNCH.CPT 99 F 1/7/02
1959066 WNCH.CPT 98 F 1/7/02
1959066 WNCH.CPT 99 F 1/7/02
1959066 WNCH.CPT 98 F 1/7/02
1959066 WNCH.CPT 98 F 1/7/02
1959066 WNCH.CPT 99 F 1/7/02
1959066 WNCH.CPT 99 F 1/10/02
1959066 MGTW.CPT 98 F 1/10/02

 

[DanJR]

Thanks cmmrfrds for the correct sql statement, i'm not sure why i went of on such a tangent....

Cheers,
Dan

 

[tdpman]

OK, the code worked for me. Took a while to run, but it eventually worked. Thanks a bunch!!!

One last question, how can I modify the code to where it doesn't pull over any records where there is not a date in the "DATE LOA SENT" field? Not a big deal, I can work around it if it's too big of a deal.

tp

 

[SgtJarrow]

You should be able to mdify the following line:

If rsTable.Fields(&quot;DATE LOA SENT&quot <> .Fields(&quot;MinOfDATE LOA SENT&quot Then

to:

If rsTable.Fields(&quot;DATE LOA SENT&quot <> .Fields(&quot;MinOfDATE LOA SENT&quot Or IsNull(rsTable.Fields(&quot;DATE LOA SENT&quot) Then

to get what you want.....
Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com