Run-time error "13": Type mismatch" - NEED URGENTLY HELP

F

Fred's

Hi Folks,


I've built a form with 3 botton control which the first two bottom
open 2 table and third bottom execute "Archive"
Execute the sql statement which you can find the full statement
below.

My problem is that when I am pressing the "Archive" button, I 'm
getting this error "Run-time error "13": Type mismatch" which goes to
THIS:

Err_Execute:

' Notify user of any errors that result from
' executing the query.
If DBEngine.Errors.Count > 0 Then
For Each errLoop In DBEngine.Errors
MsgBox "Error number: " & errLoop.Number & vbCr & _
errLoop.Description
Next errLoop
End If

Resume Next

Can someone could help me to fix this error

Thank you in advance
Fred's



Here is MY full Sql statement:


Private Sub ViewArchive_Click()
Dim strSQLAppend As String
Dim strSQLDelete As String
Dim errLoop As Error
Dim dteExpiry As Date

dteExpiry = DateAdd("yyyy", -2, Date)

' Define two SQL statements for action queries.
strSQLAppend = "INSERT INTO NCtblExpired " & _
"( scarID, scarNumber, supplierName, retekNumber,
problemDate, season, division, orderNumber, country, style, color1,
color2, color3, color4, problemCode1, problemCode2, problemCode3,
problemCode4, problemCode5, problemCode5, problemCode6,
problemDescription, impact, correctiveAction, preventiveAction,
reworkManhours, hourlyRates, laborCost, disposition,
reworkInstructions, &_ sppCoordinator, rootCause, supplierResponse,
responseCode, responseDate, resp_Code, reviewedby, reviewDate,
followUpHistory, status, generateScar, sentToSuppliers, closedDate, "
& _
"merchType, verificationDate, verifiedBy ) " & _
"SELECT ncTbl.scarID, " & _
"ncTbl.scarNumber, ncTbl.supplierName,
ncTbl.retekNumber, ncTbl.problemDate, ncTbl.season, ncTbl.division,
ncTbl.orderNumber, ncTbl.country, ncTbl.style, ncTbl.color1, "
strSQLAppend = strSQLAppend & "ncTbl.color2" & _
"ncTbl.color2, ncTbl.color3, ncTbl.color4,
ncTbl.problemCode1, ncTbl.problemCode2, ncTbl.problemCode3,
ncTbl.problemCode4, ncTbl.problemCode5, ncTbl.problemCode6,
ncTbl.problemDescription, " & _
strSQLAppend = strSQLAppend & "ncTbl.impact" & _
"ncTbl.impact, ncTbl.correctiveAction,
ncTbl.preventiveAction, ncTbl.reworkManhours, ncTbl.hourlyRates,
ncTbl.laborCost, ncTbl.disposition, ncTbl.reworkInstructions,
ncTbl.sppCoordinator, ncTbl.rootCause " & _
strSQLAppend = strSQLAppend & "ncTbl.supplierResponse" & _
"ncTbl.supplierResponse, ncTbl.responseCode, ncTbl.responseDate,
ncTbl.resp_Code, ncTbl.reviewedby, ncTbl.reviewDate,
ncTbl.followUpHistory, ncTbl.status, ncTbl.generateScar,
ncTbl.sentToSuppliers, ncTbl.closedDate, ncTbl.merchType,
ncTbl.verificationDate, ncTbl.verifiedBy, " & _
"FROM ncTbl " & _
"WHERE ncTbl.problemDate <= #" & dteExpiry & "#;"

strSQLDelete = "DELETE ncTbl.scarID, " & _
"ncTbl.scarNumber, ncTbl.supplierName, ncTbl.retekNumber,
ncTbl.problemDate, ncTbl.season, " & _
"ncTbl.division, ncTbl.orderNumber, ncTbl.country,
ncTbl.style, ncTbl.color1, ncTbl.color2, " & _
"ncTbl.color3, ncTbl.color4, ncTbl.problemCode1,
ncTbl.problemCode2, ncTbl.problemCode3, " & _
"ncTbl.problemCode4, ncTbl.problemCode5,
ncTbl.problemCode6, ncTbl.problemDescription, " & _
"ncTbl.impact, ncTbl.correctiveAction,
ncTbl.preventiveAction, ncTbl.reworkManhours, " & _
"ncTbl.hourlyRates, ncTbl.laborCost, ncTbl.disposition,
ncTbl.reworkInstructions, " & _
"ncTbl.sppCoordinator, ncTbl.rootCause, ncTbl.supplierResponse,
ncTbl.responseCode, " & _
"ncTbl.responseDate, ncTbl.resp_Code, ncTbl.reviewedby,
ncTbl.reviewDate, ncTbl.followUpHistory, " & _
"ncTbl.status, ncTbl.generateScar, ncTbl.sentToSuppliers,
ncTbl.closedDate, ncTbl.merchType, " & _
"ncTbl.verificationDate, ncTbl.verifiedBy, " & _
"FROM ncTbl " & _
"WHERE ncTbl.problemDate <= #" & dteExpiry & "#;"



' Run action query to restore data. Trap for errors,
' checking the Errors collection if necessary.
On Error GoTo Err_Execute
CurrentDb.Execute strSQLAppend, dbFailOnError
CurrentDb.Execute strSQLDelete, dbFailOnError
On Error GoTo 0

Exit Sub

Err_Execute:

' Notify user of any errors that result from
' executing the query.
If DBEngine.Errors.Count > 0 Then
For Each errLoop In DBEngine.Errors
MsgBox "Error number: " & errLoop.Number & vbCr & _
errLoop.Description
Next errLoop
End If

Resume Next

End Sub
 
D

Dirk Goldgar

Fred's said:
Hi Folks,


I've built a form with 3 botton control which the first two bottom
open 2 table and third bottom execute "Archive"
Execute the sql statement which you can find the full statement
below.

My problem is that when I am pressing the "Archive" button, I 'm
getting this error "Run-time error "13": Type mismatch" which goes to
THIS:

Err_Execute:

' Notify user of any errors that result from
' executing the query.
If DBEngine.Errors.Count > 0 Then
For Each errLoop In DBEngine.Errors
MsgBox "Error number: " & errLoop.Number & vbCr & _
errLoop.Description
Next errLoop
End If

Resume Next

Can someone could help me to fix this error

Thank you in advance
Fred's



Here is MY full Sql statement:


Private Sub ViewArchive_Click()
Dim strSQLAppend As String
Dim strSQLDelete As String
Dim errLoop As Error
Dim dteExpiry As Date

dteExpiry = DateAdd("yyyy", -2, Date)

' Define two SQL statements for action queries.
strSQLAppend = "INSERT INTO NCtblExpired " & _
"( scarID, scarNumber, supplierName, retekNumber,
problemDate, season, division, orderNumber, country, style, color1,
color2, color3, color4, problemCode1, problemCode2, problemCode3,
problemCode4, problemCode5, problemCode5, problemCode6,
problemDescription, impact, correctiveAction, preventiveAction,
reworkManhours, hourlyRates, laborCost, disposition,
reworkInstructions, &_ sppCoordinator, rootCause, supplierResponse,
responseCode, responseDate, resp_Code, reviewedby, reviewDate,
followUpHistory, status, generateScar, sentToSuppliers, closedDate, "
& _
"merchType, verificationDate, verifiedBy ) " & _
"SELECT ncTbl.scarID, " & _
"ncTbl.scarNumber, ncTbl.supplierName,
ncTbl.retekNumber, ncTbl.problemDate, ncTbl.season, ncTbl.division,
ncTbl.orderNumber, ncTbl.country, ncTbl.style, ncTbl.color1, "
strSQLAppend = strSQLAppend & "ncTbl.color2" & _
"ncTbl.color2, ncTbl.color3, ncTbl.color4,
ncTbl.problemCode1, ncTbl.problemCode2, ncTbl.problemCode3,
ncTbl.problemCode4, ncTbl.problemCode5, ncTbl.problemCode6,
ncTbl.problemDescription, " & _
strSQLAppend = strSQLAppend & "ncTbl.impact" & _
"ncTbl.impact, ncTbl.correctiveAction,
ncTbl.preventiveAction, ncTbl.reworkManhours, ncTbl.hourlyRates,
ncTbl.laborCost, ncTbl.disposition, ncTbl.reworkInstructions,
ncTbl.sppCoordinator, ncTbl.rootCause " & _
strSQLAppend = strSQLAppend & "ncTbl.supplierResponse" & _
"ncTbl.supplierResponse, ncTbl.responseCode, ncTbl.responseDate,
ncTbl.resp_Code, ncTbl.reviewedby, ncTbl.reviewDate,
ncTbl.followUpHistory, ncTbl.status, ncTbl.generateScar,
ncTbl.sentToSuppliers, ncTbl.closedDate, ncTbl.merchType,
ncTbl.verificationDate, ncTbl.verifiedBy, " & _
"FROM ncTbl " & _
"WHERE ncTbl.problemDate <= #" & dteExpiry & "#;"

strSQLDelete = "DELETE ncTbl.scarID, " & _
"ncTbl.scarNumber, ncTbl.supplierName, ncTbl.retekNumber,
ncTbl.problemDate, ncTbl.season, " & _
"ncTbl.division, ncTbl.orderNumber, ncTbl.country,
ncTbl.style, ncTbl.color1, ncTbl.color2, " & _
"ncTbl.color3, ncTbl.color4, ncTbl.problemCode1,
ncTbl.problemCode2, ncTbl.problemCode3, " & _
"ncTbl.problemCode4, ncTbl.problemCode5,
ncTbl.problemCode6, ncTbl.problemDescription, " & _
"ncTbl.impact, ncTbl.correctiveAction,
ncTbl.preventiveAction, ncTbl.reworkManhours, " & _
"ncTbl.hourlyRates, ncTbl.laborCost, ncTbl.disposition,
ncTbl.reworkInstructions, " & _
"ncTbl.sppCoordinator, ncTbl.rootCause, ncTbl.supplierResponse,
ncTbl.responseCode, " & _
"ncTbl.responseDate, ncTbl.resp_Code, ncTbl.reviewedby,
ncTbl.reviewDate, ncTbl.followUpHistory, " & _
"ncTbl.status, ncTbl.generateScar, ncTbl.sentToSuppliers,
ncTbl.closedDate, ncTbl.merchType, " & _
"ncTbl.verificationDate, ncTbl.verifiedBy, " & _
"FROM ncTbl " & _
"WHERE ncTbl.problemDate <= #" & dteExpiry & "#;"



' Run action query to restore data. Trap for errors,
' checking the Errors collection if necessary.
On Error GoTo Err_Execute
CurrentDb.Execute strSQLAppend, dbFailOnError
CurrentDb.Execute strSQLDelete, dbFailOnError
On Error GoTo 0

Exit Sub

Err_Execute:

' Notify user of any errors that result from
' executing the query.
If DBEngine.Errors.Count > 0 Then
For Each errLoop In DBEngine.Errors
MsgBox "Error number: " & errLoop.Number & vbCr & _
errLoop.Description
Next errLoop
End If

Resume Next

End Sub
Click to expand...

Do you have references set to both ADO (Microsoft ActiveX Data Objects
2.x Library) and DAO (Microsoft DAO 3.6 Object Library)? Try replacing
this:
Dim errLoop As Error
Click to expand...

with this:

Dim errLoop As DAO.Error

Make sure that you do have a reference set to the DAO library.
 
F

Fred's

Do you have references set to both ADO (Microsoft ActiveX Data Objects
2.x Library) and DAO (Microsoft DAO 3.6 Object Library)? Try replacing
this:


with this:

Dim errLoop As DAO.Error

Make sure that you do have a reference set to the DAO library.

--
Dirk Goldgar, MS Access MVPwww.datagnostics.com

(please reply to the newsgroup)- Hide quoted text -

- Show quoted text -
Click to expand...

Hi Dirk,
Finally I add the library, but now I'm getting these error messages:

Error Number 3078 - The microsoft jet database cannot find the input
table or query 'false'. Make sure it exist and that its name is
spelled correctly.

Then, WHEN i PRESS THE OK BUTTON, I'm getting this other message:
Error Number 3130 - Syntax error in DELETE statement
 
D

Dirk Goldgar

Fred's said:
Hi Dirk,
Finally I add the library, but now I'm getting these error messages:

Error Number 3078 - The microsoft jet database cannot find the input
table or query 'false'. Make sure it exist and that its name is
spelled correctly.

Then, WHEN i PRESS THE OK BUTTON, I'm getting this other message:
Error Number 3130 - Syntax error in DELETE statement
Click to expand...

Can you identify the exact line on which the error is raised? Is it
when you execute the DELETE query you posted? I don't immediately see
why that would give you that message, but I do note that it is
unnecessarily complex, since you list all the fields to be deleted, when
all you really need is:

strSQLDelete = _
"DELETE FROM ncTbl " & _
"WHERE ncTbl.problemDate <= #" & dteExpiry & "#;"

You should, however, explicitly format dteExpiry to MM/DD/YYYY format in
the SQL string -- that's what the Jet database engine prefers, in the
case of any date literal whose interpretation is ambiguous. So I'd
recommend this:

strSQLDelete = _
"DELETE FROM ncTbl " & _
"WHERE ncTbl.problemDate <= #" & _
Format(dteExpiry, "mm/dd/yyyy") & "#;"

That really doesn't explain why you're getting the error you report,
though. Please make the change to that SQL statement, and if that
doesn't work, report back with exactly what statement is raising the
error, and the value of the SQL string being executed at that time.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Automating Archiving Records in Microsoft Access, using Append and Delete Queries: 1
I need help with a "Run-Time Error 13: Type Mismatch" 2

Top