Datatype mismatch error 3464

[hlock]

I've been struggling with putting together this code and have run into another problem. This whole project revolves around the fact that I have to query 3 separate linked tables to find one record. Given a claim number, the record could be in one of 3 linked tables. I have set up 3 queries. Each query pulls from one of the 3 linked tables. Depending on the format of a field, the code "decides" which query to run. The following code is behind a button on a form. The user enters various pieces of information, including [Claim#].

The code first deletes the current records from [T: Standard Claim Info for Forms], then uses an If/Else statement, based on the format of the [Claim#] field, to decide which query to run. Each of the 3 queries are append queries to the table [T: Standard Claim Info for Forms].

If the [Claim#] field falls into the If or the ElseIf statements, the appropriate queries run without error. However, if the [Claim#] field falls into the Else statement, I get the Error 3464: Datatype Mismatch error. The error falls on the qdf.Execute dbFailOnError statement in the Else section. My question is 2-fold:

1. Any suggestions on handling the 3 different linked tables differently?
2. Why am I getting the 3464 error?

Private Sub Command33_Click()
DoCmd.SetWarnings False
Dim dbs As DAO.Database
Dim strSql As String
Dim qdf As QueryDef
Set dbs = CurrentDb
strSql = "DELETE FROM [T: Standard Claim Info for Forms];"
dbs.Execute strSql, dbFailOnError
If (Me.[Claim#] Like "CB0*") Then
Set qdf = dbs.QueryDefs("Q: Standard Claim Info for Forms PC")
qdf.Execute dbFailOnError
ElseIf (Me.[Claim#] Like "CB*") And (Mid(Me.[Claim#], 3, 1) <> "0") Then
Set qdf = dbs.QueryDefs("Q: Standard Claim Info for Forms Bond")
qdf.Execute dbFailOnError
Else
Set qdf = dbs.QueryDefs("Q: Standard Claim Info for Forms")
qdf.Execute dbFailOnError
End If
DoCmd.OpenQuery "Q: Claim Information for Attorney Assignment", acViewNormal, acEdit
DoCmd.close acQuery, "Q: Claim Information for Attorney Assignment"
MergeAllWord "Attorney Assignment Letter"
DoCmd.SetWarnings True
End Sub

Thanks in advance for any help!

 

[tehNellie]

You should check your query : "Q: Standard Claim Info for Forms"

A data mismatch generally occurs because you're trying to compare two different types of data (generally tends to be Text and Number). As the query doesn't appear to take any input, check the JOIN columns within that query to ensure that they're both the same datatype.

That you appear to have the "same" data in 3 different locations and a user[form] cannot say "It's there", your code has to do shenanigans to figure out where to go, suggests an issue with your table design

 

[hlock]

Thanks. I've checked the query, however, I check again. The issue with the 3 different locations is that we recently joined with another company. They maintained 2 separate systems for their 2 types of businesses (thus 2 systems to query over). Then we have our sytem (1 system to query over). A record will not appear in more than 1 system. It's going to be a while before all of our systems are combined into one so we have to make do until then.

 

[jdraw]

A couple of things you could check:

Check the definition and data type for the Claim# in each of the 3 tables, and let us know what they are.

Show us the SQL for each of your saved qdf's.

 

[hlock]

I found the problem in a calculated field that didn't match the field in the table it was appending to.

Still looking for other ways of handling the queryingof 3 different systems.

Thanks.