Type Mismatch Error - I cannot figure out why. Help Please.

[Gandalf_One]

I am running the following VB script and keep getting a 'Runtime Error 13 - Type Mismatch'. The code is below. The type mismatch is occurring on intspan1 = ![span]

![span] is a number from the table. intspan1 is defined as a long integer. I have tried defining intspan1 as an integer, a variant, even a string - always the same error. I have also tried compact and repair but always the same error.

Please take a look at my code below, perhaps I am missing something.

Any advice would be very much appreciated.

Thanks!!

Dim db As Database
Dim rsPriorPension As DAO.Recordset
Dim rsServiceNo As DAO.Recordset
Dim rsLoad As DAO.Recordset
Dim strSN As String
Dim strSource1 As String
Dim strSource2 As String
Dim strSource3 As String
Dim strCCode1 As Long
Dim strCCode2 As Long
Dim strCCode3 As Long
Dim intspan1 As Long
Dim intspan2 As Long
Dim intspan3 As Long

Dim strCCPSflg1 As String
Dim strCCPSflg2 As String
Dim strHRMSflg As String
Dim dbchgnum As Long
On Error GoTo ErrorHandler
strSN = ""
dbchgnum = 4
spancnt1 = 0
'Initialize database and recordset values
Set db = CurrentDb()
Set rsServiceNo = db.OpenRecordset("qryDistinctServiceNumber_extract")
Set rsLoad = db.OpenRecordset("child_tblPriorPension")

'Initialize Variables


'Loop through all service numbers

With rsServiceNo

If .RecordCount > 0 Then
.MoveFirst

Do Until .EOF

strSN = ![ServiceNo]

Debug.Print strSN

Set rsPriorPension = db.OpenRecordset("Select * from tblMergedExtract where ServiceNo = '" & strSN & "' ORDER BY tblMergedExtract.ServiceNo, tblMergedExtract.Span, tblMergedExtract.Source;")

With rsPriorPension 'Loop through records with matching Service numbers

If .RecordCount = 3 Then
.MoveFirst


Do Until .EOF 'Apply conditions to selected data

Debug.Print spancnt1

If strSN = ![ServiceNo] Then
If ![Source] = "CCPS" Then
strSource1 = ![Source] And intspan1 = ![Span] And strCCode1 = ![ComponentCode] And strCCPSflg1 = "Yes"
End If

If ![Source] = "HRMS" Then
strSource2 = ![Source] And intspan2 = ![Span] And strCCode2 = ![ComponentCode] And strHRMSflg = "Yes"
End If

If ![Source] = "CCPS" And ![ComponentCode] = "2" And ![RelDate] < #1/3/2007# Then
strSource3 = ![Source] And strCCode3 = ![ComponentCode] And strCCPSflg2 = "Yes"
End If

End If
.MoveNext
Loop

If strCCPSflg1 = "Yes" And strHRMSflg = "Yes" And strCCPSflg2 = "Yes" Then

If intspan2 = intspan1 And strCCode2 = strCCode1 Then
.MoveFirst

Do Until .EOF

'Move data that matches criteria to child table

rsLoad.AddNew
rsLoad("ID") = ![ID]
rsLoad("ServiceNo") = ![ServiceNo]
rsLoad("Source") = ![Source]
rsLoad("ComponentCode") = ![ComponentCode]
rsLoad("EnrolDate") = ![EnrolDate]
rsLoad("RelDate") = ![RelDate]
rsLoad("RelReason") = ![RelReason]
rsLoad("UIC") = ![UIC]
rsLoad("Merged") = ![Merged]
rsLoad("Span") = ![Span]
rsLoad("DataChange") = dbchgnum

rsLoad.Update

.Delete
.MoveNext
Loop

End If
End If
End If
End With

.MoveNext
Loop
End If
End With
rsPriorPension.Close
CCctr = 0

db.Close
Set rsPriorPension = Nothing
Set rsLoad = Nothing
Set db = Nothing
'Call procedure that makes copy of the tblMergedExtract

Call CopyTableMergedExtract(dbchg:=dbchgnum)
Exit Sub
ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub

 

[RuralGuy]

In words describe what you expect this line to accomplish:
strSource2 = ![Source] And intspan2 = ![Span] And strCCode2 = ![ComponentCode] And strHRMSflg = "Yes"

 

[jzwp22]

I'm assuming that this is where the error occurs

If ![Source] = "CCPS" Then
strSource1 = ![Source] And intspan1 = ![Span] And strCCode1 = ![ComponentCode] And strCCPSflg1 = "Yes"
End If

I don't know for sure, but I do not think that you can have the word AND in the IF THEN structure. Each statment has to stand alone. Like this


If ![Source] = "CCPS" Then
strSource1 = ![Source]
intspan1 = ![Span]
strCCode1 = ![ComponentCode]
strCCPSflg1 = "Yes"
End If

 

[Gandalf_One]

I expect the variables strSource1, intspan1, strCCode1, strCCPSflg1 to be set equal to the corresponding database values.

This then used as comparison to another record further on in my code.

I have a number of scripts that use similar logic for data comparison in a data cleansing tool I am creating.

This though is the first time I have a type mismatch for the span. I know the error occurs on intspan1 = ![span] because I have tested by commenting out each variable one at a time. The type mismatch doesn't occur if the intspan1 = ![span] is commented out.

Thanks.

 

[RuralGuy]

jzwp22 has the right solution in post #3. Give it a try.

 

[Gandalf_One]

I have used AND in my scripts before without a problem, but decided to try removing the AND's anyway because I am trying everything.

It worked! Removing the AND's worked.

Thanks so much for your help JZWP22!!

 

[jzwp22]

You're welcome.

 

[tranchemontaigne]

As an aside, you might also want to check the values in the table that populate your result set. Remember that it is possible to receive null values. Performing any kind of comparator operation against a null can generate Type Mismatch errors.

It is always good practice to add a few lines of code to your block to evaluate each field of each record in the recordset and force any nulls to have a value. It's not elegant, but you can always fill nulls with "NULL" and then use the ISNUMERIC and similar functions later so you can explicitly handle any NULLs you find.

I hope this helps.

 

[Gandalf_One]

Thanks - Actually before I run any of the compare scripts I set any nulls to zeros in number columns and nulls equal to Null in string columns.

Thanks again.