Hello Everyone,
I have been fighting with a type mismatch error for many hours today and I can't seem to find what the problem is. Hopefully it is more than a missing apostrophe! I have isolated each SQL statement using a MsgBox and then input that exact code as a Query in access which works. I'm using Access 97 on Windows XP. My code is below:
Dim strKaizen As String
strKaizen = "UPDATE KaizenEvents SET(TeamLeader)= '" & [TeamLeader] & "' WHERE ProjectCode=" & [ProjectCode] & " "
DoCmd.RunSQL (strKaizen)
If Nz(Form.AI1) > 0 Then
Dim strActionItems1 As String
strActionItems1 = "INSERT INTO ActionItems (ProjectCode, ActionItem, POC, Status) " & _
"VALUES(" & [ProjectCode] & ",'" & [AI1] & "','" & [POC1] & "'," & [AIStatus1] & ")"
DoCmd.RunSQL (strActionItems1)
End If
If Nz(Form.AI2) > 0 Then
Dim strActionItems2 As String
strActionItems2 = "INSERT INTO ActionItems (ProjectCode, ActionItem, POC, Status) " & _
"VALUES(" & [ProjectCode] & ",'" & [AI2] & "','" & [POC2] & "'," & [AIStatus2] & ")"
DoCmd.RunSQL (strActionItems2)
End If
If Nz(Form.Goal1) > 0 Then
Dim strGoals1 As String
strGoals1 = "INSERT INTO Goals (ProjectCode, Goals, Status) " & _
"VALUES(" & [ProjectCode] & ",'" & [Goal1] & "'," & [GoalStatus1] & ")"
DoCmd.RunSQL (strGoals1)
End If
If Nz(Form.Goal2) > 0 Then
Dim strGoals2 As String
strGoals2 = "INSERT INTO Goals (ProjectCode, Goals, Status) " & _
"VALUES(" & [ProjectCode] & ",'" & [Goal2] & "'," & [GoalStatus2] & ")"
DoCmd.RunSQL (strGoals2)
End If
Dim strTeam As String
strTeam = "INSERT INTO TeamMembers (ProjectCode,TeamMember1,TeamMember2,TeamMember3,T eamMember4,TeamMember5,TeamMember6,TeamMember7,Tea mMember8,TeamMember9,TeamMember10,TeamMember11,Tea mMember12,TeamMember13,TeamMember14) " & _
"VALUES(" & [ProjectCode] & ",'" & [TmMbr1] & "','" & [TmMbr2] & "','" & [TmMbr3] & "','" & [TmMbr4] & "','" & [TmMbr5] & "','" & [TmMbr6] & "','" & [TmMbr7] & "','" & [TmMbr8] & "','" & [TmMbr9] & "','" & [TmMbr10] & "','" & [TmMbr11] & "','" & [TmMbr12] & "','" & [TmMbr13] & "','" & [TmMbr14] & "')"
DoCmd.RunSQL (strTeam)
I know that a mismatch occurs when one is trying to insert the wrong data type into a field. I have checked and rechecked my fields.
Status field = number
ProjectCode = number
Everything else = text
I have three tables that are related by the ProjectCode field. The Kaizen field has an autonumber primary key and the other ProjectCodes are just number fields with duplicates OK. I have this code on a form that will allow a user to input data that will then be sent to the appropriate table. There is a goals table, an action items table, and a team members table as there can be multiple occurrences of these items per unique kaizen event in the main table.
Can anyone spot why I may have a data mismatch error?
Additionally, if you have a suggestion for a better way to write the "Nz(....) >0 piece, that would be good too. I'm basically trying to avoid null and empty cells in the DB.
Thanks in advance