Data Type Mismatch

[MHamid]

Hello,

I am using the code below to add a record, but I’m getting a run-timeerror ‘3464’: Data type mismatch in criteria expression”. What am I doingwrong? Is it the spaces?

[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]CurrentDb.Execute "INSERT INTO archive_RET_Accumulated([REF_NUM],[Exam ID], [Exam Name], [Exam Agency], [OCC SL No], [Exam Objective], [PrimaryExam Scope DSMT Managed Segment], [Field Work Start Date], [Field Work CompleteDate], [Exam Flag], [Exam Category], [Exam Subcategory], [Exam Owner], [ExamStatus], [Primary Exam Scope DSMT Managed Geography], [Senior Owner],[O&T_Impact], [Exam_Owner_Group_Name], [Exam_Owner_DSMT],[Senior_Owner_Group_Name], [Senior_Owner_DSMT], [RET_Accumulated_Flag],[RET_Accumulated_Added_Date])" & _[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    "VALUES ('" &Me.REF_NUM & "',  '" &Me.Exam_ID & "', '" & Me.Exam_Name & "',  '" & Me.Exam_Agency & "','" & Me.OCC_SL_No & "', '" & Me.Exam_Objective &"', '" & Me.Primary_Exam_Scope_DSMT_Managed_Geography &"', '" & Me.Field_Work_Start_Date & "', '" &Me.Field_Work_Complete_Date & "', '" & Me.Exam_Flag &"', '" & Me.Exam_Category & "', '" &Me.Exam_Subcategory & "', '" & Me.Exam_Owner &"','" & Me.Exam_Status & "', '" &Me.Primary_Exam_Scope_DSMT_Managed_Geography & "', '" &Me.Senior_Owner & "', '" & Me.OT_Impact & "','" & Me.Exam_Owner_Group_Name & "', '" &Me.Exam_Owner_DSMT & "', """ &Me.Senior_Owner_Group_Name & """, '" &Me.Senior_Owner_DSMT & "', '" & Me.RET_Accumulated_Flag &"', '" & Me.RET_Accumulated_Added_Date & "');",dbFailOnError

Thank you

 

[xenou]

Too many fields to know.

You need to debug your string. Try putting this into your form and then check out the results (maybe just use a temp button to run it).

sub foo()

dim strSQL as string

strSQl = "INSERT INTO archive_RET_Accumulated([REF_NUM],[Exam ID], [Exam Name], [Exam Agency], [OCC SL No], [Exam Objective], [PrimaryExam Scope DSMT Managed Segment], [Field Work Start Date], [Field Work CompleteDate], [Exam Flag], [Exam Category], [Exam Subcategory], [Exam Owner], [ExamStatus], [Primary Exam Scope DSMT Managed Geography], [Senior Owner],[O&T_Impact], [Exam_Owner_Group_Name], [Exam_Owner_DSMT],[Senior_Owner_Group_Name], [Senior_Owner_DSMT], [RET_Accumulated_Flag],[RET_Accumulated_Added_Date])" & _
    "VALUES ('" &Me.REF_NUM & "',  '" &Me.Exam_ID & "', '" & Me.Exam_Name & "',  '" & Me.Exam_Agency & "','" & Me.OCC_SL_No & "', '" & Me.Exam_Objective &"', '" & Me.Primary_Exam_Scope_DSMT_Managed_Geography &"', '" & Me.Field_Work_Start_Date & "', '" &Me.Field_Work_Complete_Date & "', '" & Me.Exam_Flag &"', '" & Me.Exam_Category & "', '" &Me.Exam_Subcategory & "', '" & Me.Exam_Owner &"','" & Me.Exam_Status & "', '" &Me.Primary_Exam_Scope_DSMT_Managed_Geography & "', '" &Me.Senior_Owner & "', '" & Me.OT_Impact & "','" & Me.Exam_Owner_Group_Name & "', '" &Me.Exam_Owner_DSMT & "', """ &Me.Senior_Owner_Group_Name & """, '" &Me.Senior_Owner_DSMT & "', '" & Me.RET_Accumulated_Flag &"', '" & Me.RET_Accumulated_Added_Date & "');" 
	
debug.print strSQl

end sub

Not sure if its the html or your actual code but there are some cases with no space after the & operator. Also all string, date, and number types need to be handled differently in literal sql commands (strings need single quotes around them ('a string'), dates need octothorpes around them (#1/1/2018#) and numbers don't need anything around them.