Type Mismatch Error

[kjworm]

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 (strActionItems 1)
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 (strActionItems 2)
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,Te amMember1,TeamM ember2,TeamMemb er3,TeamMember4 ,TeamMember5,Te amMember6,TeamM ember7,TeamMemb er8,TeamMember9 ,TeamMember10,T eamMember11,Tea mMember12,TeamM ember13,TeamMem ber14) " & _
"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

[nico5038]

Is hard to do without the actual code.
In this case I normally place a Break in the code (click in the left rules to get a brown dot) and execute the sub.
When the code halts I use the F8 to step one line after the assign statement of the SQL string.
Then type in the immediate window:
?strSQL
and press [Enter] to get the build SQL.
This I copy/paste in the SQL mode of the query editor and Access will give the error and place the cursor at the erroneous field.
There I correct the statement till it works and then amend the original string.

Nic;o)

[kjworm]

Well, as soon as I posted, I figured out that the "If Nz(Form.AI1) > 0 Then" piece of the code is causing my problems. This requires the "AI1" field to be numeric. If I enter text I get the Type Mismatch Error, but if I enter numbers, everything works fine.

What I am trying to do is create an entry into the DB only if a user populates the form. If the entry field is blank, then nothing will be inserted into the DB. What would be the best way to do this?

[nico5038]

That's the reason I use for the "empty" check:

IF Len(NZ(field))> 0 then ....

This works for both text and numbers being Null or empty.

Nic;o)

[kjworm]

I just replaced the Nz with Len and it works fine. I'll add in what you suggest and take it from there.

Thanks! This answers my question!

[nico5038]

Glad I could help, success with your application !

Nic;o)