Query Error

[Chris]

What is a "Numeric Field Overflow" (Error 3349) error
mean. I receive this message when I try and run an append
query for the second time. It seems to run the first time
but not the second time.

Thanks for your help

 

[John Vinson]

What is a "Numeric Field Overflow" (Error 3349) error
mean.

It means that some number is being generated which is larger than the
maximum value allowed for the target field's datatype - 16384 for an
Integer, 2 billion odd for a Long.

I receive this message when I try and run an append
query for the second time. It seems to run the first time
but not the second time.

Odd. Please post the SQL view of the query.

 

[Chris]

INSERT INTO tblMake ( Application_User_Name,
Business_Rule_ID, Contact_Method_Display_Description,
Homeowner_Contacted_Indicator,
Additional_Field_Edits_Indicator, User_Loan_Comment,
ItemID, Load_ControlID, Origination_System_ID, State_Code,
Loan_Record_ID, Work_Unit_Code, Process_Status,
Queue_Status_Code, Assigned_User_ID, Assigned_Date,
Transferred_From_User_ID, Transfered_From_User_Date,
FollowUp_Date_Set_Date, Disposition_Code, Reason_Code,
Client_Number, Loan_Number, Loan_Close_Date,
Loan_Period_Days_Count, Completion_User_ID,
Completed_Date, Reopen_User_ID, Work_Status_Code,
Work_Status_Date, Origination_System_Description,
Loan_User_ID, Business_Rule_Error_Description,
Reason_Display_Description, Disposition_Description,
Disposition_Display_Description )
SELECT tblqa.Application_User_Name,
tblqa.Business_Rule_ID,
tblqa.Contact_Method_Display_Description,
tblqa.Homeowner_Contacted_Indicator,
tblqa.Additional_Field_Edits_Indicator,
tblqa.User_Loan_Comment, tblqa.ItemID,
tblqa.Load_ControlID, tblqa.Origination_System_ID,
tblqa.State_Code, tblqa.Loan_Record_ID,
tblqa.Work_Unit_Code, tblqa.Process_Status,
tblqa.Queue_Status_Code, tblqa.Assigned_User_ID,
tblqa.Assigned_Date, tblqa.Transferred_From_User_ID,
tblqa.Transfered_From_User_Date,
tblqa.FollowUp_Date_Set_Date, tblqa.Disposition_Code,
tblqa.Reason_Code, tblqa.Client_Number, tblqa.Loan_Number,
tblqa.Loan_Close_Date, tblqa.Loan_Period_Days_Count,
tblqa.Completion_User_ID, tblqa.Completed_Date,
tblqa.Reopen_User_ID, tblqa.Work_Status_Code,
tblqa.Work_Status_Date,
tblqa.Origination_System_Description, tblqa.Loan_User_ID,
tblqa.Business_Rule_Error_Description,
tblqa.Reason_Display_Description,
tblqa.Disposition_Description,
tblqa.Disposition_Display_Description
FROM tblqa;

 

[John Vinson]

INSERT INTO tblMake ...

VERY odd indeed. No calculations, no expressions that I can see - just
selecting records from an existing table and inserting them into
another!

If you close the database between runs of the query do you get the
error? Is there a Primary Key on tblMake? If so, what?

 

[Gary Walter]

Hi Chris,

I don't know if this will help or not....
but if you copy the following code
into a module, then in Immediate window,
type:

?fCompareFieldTypes("tblMake","tblqa")

you will get print out where you can easily
see where you might be going wrong
(or at least, copy and paste results back here).

Good luck,

Gary Walter

'***start code ****
Public Function FieldType(intType As Integer) As String
'from post by Dan Artuso
Select Case intType
Case dbBoolean
FieldType = "dbBoolean"
Case dbByte
FieldType = "dbByte"
Case dbInteger
FieldType = "dbInteger"
Case dbLong
FieldType = "dbLong"
Case dbCurrency
FieldType = "dbCurrency"
Case dbSingle
FieldType = "dbSingle"
Case dbDouble
FieldType = "dbDouble"
Case dbDate
FieldType = "dbDate"
Case dbText
FieldType = "dbText"
Case dbLongBinary
FieldType = "dbLongBinary"
Case dbMemo
FieldType = "dbMemo"
Case dbGUID
FieldType = "dbGUID"
End Select

End Function

Public Function fCompareFieldTypes(pT1 As String, _
pT2 As String) As Boolean
On Error GoTo Err_fCompareFieldTypes
Dim db As DAO.Database
Dim tdf1 As DAO.TableDef
Dim tdf2 As DAO.TableDef
Dim fld As DAO.Field
Dim fld1 As DAO.Field
Dim fld2 As DAO.Field
Dim fld3 As DAO.Field
Dim idx As DAO.Index
Dim strName1 As String
Dim strName2 As String
Dim strType1 As String
Dim strType2 As String
Dim i As Integer

Set db = CurrentDb
Set tdf1 = db.TableDefs(pT1)
Set tdf2 = db.TableDefs(pT2)

Debug.Print "Table1: " & pT1 _
& " Table2: " & pT2

For i = 0 To tdf1.Fields.Count - 1
Debug.Print "----------------------------"
Set fld1 = tdf1.Fields(i)
strName1 = fld1.Name
strType1 = FieldType(tdf1.Fields(i).Type)
If strType1 = "dbText" Then
strType1 = strType1 _
& " (" & tdf1.Fields(i).Size & ")"
End If
'is it a primary key?
For Each idx In tdf1.Indexes
If idx.Primary Then
' Found a Primary Key
For Each fld In idx.Fields
If fld.Name = strName1 Then
strType1 = strType1 & " (pk)"
Exit For
End If
Next fld
Exit For
End If
Next idx
'do we have same field name in tdf2?
strName2 = ""
strType2 = ""
For Each fld In tdf2.Fields
If fld.Name = strName1 Then
strName2 = strName1
strType2 = FieldType(tdf2.Fields(strName2).Type)
If strType2 = "dbText" Then
strType2 = strType2 _
& " (" & tdf1.Fields(i).Size & ")"
End If
'is it a primary key?
For Each idx In tdf2.Indexes
If idx.Primary Then
' Found a Primary Key
For Each fld3 In idx.Fields
If fld3.Name = strName2 Then
strType2 = strType2 & " (pk)"
Exit For
End If
Next fld3
Exit For
End If
Next idx
Exit For
End If
Next fld
Debug.Print strName1 & vbCrLf & strType1, _
Tab(20), strType2

Next i

Debug.Print "----------------------------"
db.Close

fCompareFieldTypes = True

Exit_fCompareFieldTypes:
Set tdf1 = Nothing
Set tdf2 = Nothing
Set db = Nothing
Exit Function

Err_fCompareFieldTypes:
MsgBox Err.Description
Resume Exit_fCompareFieldTypes
End Function
'**** end code ****