Error #: 13 Type mismatch

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an append query that runs via a form. If I run the query using
DoCmd.RunSQL I get the Error. If I run the actual query object with the same
SQL statement, there is no error.

Any one know why this is happening?


DoCmd.RunSQL ("INSERT INTO NewFacSet ( TransactionIDNo, TransactionDate,
TransactionDemoStatus, " & _
"LocalGovernment, APN, AssessCatCode, CatIDNo, CF, SFC, SC,
SSC, [Facility Change?], " & _
"AppIDNo, FY, SFCCd, pos, IncUnits, BaseUnits, IncFlow,
BaseFlow, measure, " & _
"TotalFlow, TotalBOD, TotalSS, TotalMGDMiles, dist, charge,
Year ) " & _
"SELECT tblFacTrans.TransactionIDNo,
tblFacTrans.TransactionDate, tblFacTrans." & _
"TransactionDemoStatus, tblFacTrans.LocalGovernment,
tblFacTrans.APN, tblFacTrans." & _
"AssessCatCode, tblFacTrans.CatIDNo, tblFacTrans.CF,
tblFacTrans.SFC, tblFacTrans.SC, " & _
"tblFacTrans.SSC, tblFacTrans.[Facility Change?],
tblFacTrans.AppIDNo, tblFacTrans.FY, " & _
"tblFacTrans.SFCCd, InStr(AppIDSet.SFC,'/') AS pos, 'A' AS
IncUnits, 'B' AS " & _
"BaseUnits, 'A' AS IncFlow, 'B' AS BaseFlow, 'A' AS measure,
999999999 AS TotalFlow, " & _
"99999999.99 AS TotalBOD, 99999999.99 AS TotalSS,
99999999.9999 AS TotalMGDMiles, " & _
"Mid(AppIDSet.AppIDNo,1,InStr(AppIDSet.AppIDNo," - ")-1) AS
dist, 1.11 AS charge, " & _
"Right([forms].frmreports.txtdate3,4) AS Year " & _
"FROM tblFacTrans " & _
"GROUP BY tblFacTrans.TransactionIDNo,
tblFacTrans.TransactionDate, tblFacTrans." & _
"TransactionDemoStatus, tblFacTrans.LocalGovernment,
tblFacTrans.APN, tblFacTrans." & _
"AssessCatCode, tblFacTrans.CatIDNo, tblFacTrans.CF,
tblFacTrans.SFC, tblFacTrans.SC, " & _
"tblFacTrans.SSC, tblFacTrans.[Facility Change?],
tblFacTrans.AppIDNo, tblFacTrans.FY, " & _
"tblFacTrans.SFCCd " & _
"HAVING (((tblFacTrans.TransactionDate) Between
[forms].[frmreports].[txtdate1] And " & _
"[forms].[frmreports].[txtdate2])) " & _
"ORDER BY tblFacTrans.AppIDNo;")
 
A few things to try, right off-hand.

1) Year is a reserved word. Where you're using it as a field name, try
enclosing it in brackets [ ].

2) Set the SQL string to a variable then do a Debug.Print of the variable.
Does the printout in the Immediate window look correct? Verify that there a
spaces between words that need them. In the SQL view of the query, if there
is a space missing, but it is where you have a carriage return (i.e. start a
new line), the space will be assumed.

strSQL = "Insert Into ....;"
Debug.Print strSQL
DoCmd.RunSQL strSQL

3) What happens if you use

CurrentDb.Execute "Insert Into....", dbFailOnError
or
CurrentDb.Execute strSQL, dbFailOnError
 
Back
Top