VB Program

[Nick]

I've got a database in Access 97. It is a database with employee names, their employee numbers and their first day and last day worked until 1994. Then data was appended from 1994 to date. This in effect has duplicated some of the serial numbers. Now I have one employee with two records, one record has an end date of null and the other is an end date in 1994. The 1994 end date is incorrect, because the employee also has a end date of null which means, he is still working. I need to find these records and delete the 1994 record. This needs to be done for approximately 59,000 records.

The VB command that we wrote is:

Option Compare Database
Option Explicit

Private Sub Command0_Click()
On Error GoTo Err_Command0_click

Dim db As Database
Set db = CurrentDb()

Dim rs As Recordset

Set rs = db.OpenRecordset("select*from employeedatabase")

Dim intserial As Long

While Not rs.EOF
intserial = rs("serial")

If intserial = rs("serial") Then
If rs("last_work") = Not "NULL" Then
rs.Edit
rs.Delete
rs.Update
rs.MoveNext
End If
End If

Wend
Beep


Exit_command0_click:
Exit Sub

Err_Command0_click:
MsgBox Err.Description
Resume Exit_command0_click

End Sub

Private Sub Form_Click()

End Sub
------------------------------------------------------

However, when it is run, a "Type mismatch" message appears.

HELP!!! <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

[sabrantley0623]

It would seem to indicate that one of your fields is not set to the correct data type (i.e., integer).

[LegareColeman]

I am not an Access VB programmer, so it would take me quite a while to do the code that does what you want to do. You didn't say which statement the error was on, but I do see two things that are pretty obviously incorrect in your code and one of them could cause that error. First, you have the following two lines of code:

<pre>intserial = rs("serial")

If intserial = rs("serial") Then
</pre>


The first statement sets intserial equal to rs("serial") and the second compares intserial to rs("serial"). Since intserial was just set equal to rs("serial"), that comparison should always be equal. I think that what you want to do here is base your record set on a query that sorts the records on serial number, and then compare the serial number to the serial number from the next record.

Second (this is the one that could cause that error), you have the following statement:

<pre>If rs("last_work") = Not "NULL" Then
</pre>


From my Excel VBA programming, that looks like the wrong syntax. I think you need something more like the following, but I don't know the exact syntax for Access VBA:

<pre>If Not IsNull(rs("last_work")) Then
</pre>

[rory]

Hi Nick,
In addition to Legare's points, your code will I think get stuck in an endless loop if it comes to a record where the last_work field is null because your MoveNext is included in the If...End If statement. This means that if last_work is null, you don't move to the next record and you're then stuck in your While...Wend loop.
You also don't need the Edit and Update statements if you're deleting. Just the rs.Delete is fine. However, as Legare pointed out, you're comparing the serial number to the serial number of the same record so this code would delete all records where last_work is not null.
I think the following code will do what you're after (it's untested though and it ignores the possibility of duplicate records where none of the last_work dates are null):
Sub DeleteDups()
Dim db As DAO.Database, rst As DAO.Recordset, rstTemp As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT Serial, Count(Serial) AS SerCount FROM employeedatabase GROUP BY Serial;")
rst.Filter = "[SerCount] > 1"
While Not rst.EOF
Set rstTemp = db.OpenRecordset("SELECT last_work from employeedatabase where serial = " & rst!serial)
With rstTemp
If Not IsNull(!last_work) Then
.Delete
End If
.MoveNext
End With
Set rstTemp = Nothing
rst.MoveNext
Wend
Set rst = Nothing
Set db = Nothing

End Sub
Hope that helps.
PS Backup your database before you try that code as it's Friday night and I'm on my way out the door as I write this!

[ravey]

Several others have suggested ways to make your code work, but my question is why you want to do this in code in the first place. Are you going to have to do this over and over again? I think you could accomplish what you want very simply with a Delete Query, using "Is Null" as the criterion under the termination date field. Or maybe I just don't understand what you're attempting to do.

[LegareColeman]

He wants to delete the record where the date is NOT null, and then only if there is another record in the table with the same serial number. I don't think you can meet the second condition using a query.

[ravey]

Oops. I missed that. Should've read his original problem more carefully.

[raskew]

Nick,

Please reassure us that you're working with a copy of your table and not the real thing! With the deletes built into your code, a misstep could be disastrous

You've only specified one field [serial] by name, so the data-type mismatch has to between [serial] and variable 'intserial'. Suspect that if your changed your code from: Dim intserial As Long -to-- Dim intserial As Integer, that problem would probably go away, but it's something you need to check by comparing the data-types of the field versus variable.

You realize, I hope, that by doing this deletion you are going to eliminate any history for any employee who was employed, departed, and then was reemployed. Thus, you'll end up with only the most recent period of employment, without any indication that the employee might have had 20 years of prior employment.

Think the first thing I'd want to do is use the Find Duplicates query wizard so that I could see the actual gravity of the problem The resulting query is non-destructive (no deletes). You may or may not be surprised by the results. Your action plan could change once you've seen what you're dealing with. This problem can be resolved via query, but you need to be absolutely sure of the desired end result before embarking on a deletion process.

If you want to zip and email a copy of your table, I'll be happy to take a look at it.

Bob
raskew@centurytel.net

[Nick]

Thank You all for your input. I can assure you that I have made a copy of this database prior to starting any deletes. With that said, I would also like to add that this procedure is part of a nominal file, therefore there will be no loss of work history because all this information is stored in a master file.

I've tried all the suggestions put forth, however none of them have been successful. The program suggested last week was very appreciated however, the program is not accepting certain commands such as, the rst statement (could it be a rs statement instead), nor is it accepting the deletedups statement or the filter.

I have verified my data types. There are some text fields. Could that be the problem?

It was suggested that I send a copy of the database, however, since the information is very confidential, I will have to get approval (I would probably modify it to take out the names to conserve confidentiality).

Thanks again.

[charlotte]

You'll have to explain what you mean by the program "not accepting" things like rst. That is merely an object variable, and the only reason you should get an error is if you declared your recordset variable as rs and then pasted in code using rst. Where did you paste the deletedups routine and where are you trying to call it from? If either of the fields you're referencing--serial or last_worked--is a text field, then you have to be sure to include quote delimiters around the literal value in the code or query.

Try this query. I would recommend you change it to a select query first to see if you actually are getting the right records, since I'm guessing at the fields and their exact relationships. This query basically tells Access to delete all those records with a last_worked of 1994 where there is more than one record for the same serial value. Keep in mind that if last_worked is a date rather than a number then you would have to change this to Year(last_worked)=1994 and if it is a string, you would have to put the 1994 in quotes.

DELETE DISTINCTROW * FROM employeedatabase WHERE employeedatabase.last_worked = 1994 and ((select count(*) from employeedata AS E where E.serial = employeedatabase.serial))=2;