Update query suffering Type Conversion problem

[LittleSmudge]

I have an update query

DoCmd.RunSQL "UPDATE tblImport INNER JOIN tblPCourse " _
           & "ON (tblImport.Person_ID = tblPCourse.PersonRef) " _
           & "AND (tblImport.CourseCode = tblPCourse.CourseRef) " _
           & "AND (tblImport.EnrolmentDate = tblPCourse.EnrolmentDate) " _
           & "SET tblPCourse.EnrolmentStatus = " _
           & "IIf(tblImport.EnrolmentStatus = 1,'Current'," _
           & "IIf(tblImport.EnrolmentStatus = 2,'Completed'," _
           & "IIf(tblImport.EnrolmentStatus = 3,'Withdrawn',Null)));"

The Type of tblPCourse.EnrolmentStatus is Text(9)

Yet all I get when I run this is a MSAccess didn't update xxxx fields due to type conversion failure
Where xxxx is the number of record I'd expect it to update.


Yet if I run

DoCmd.RunSQL "UPDATE tblImportEnrol INNER JOIN tblPCourse " _
           & "ON (tblImportPerson_ID = tblPnCourse.PersonRef) " _
           & "AND (tblImport.CourseCode = tblPCourse.CourseRef) " _
           & "AND (tblImport.EnrolmentDate = tblPCourse.EnrolmentDate) " _
           & "SET tblPCourse.EnrolmentStatus = " _
           & "'Completed';"

It runs fine.

So it must be the Iif function.
What's wrong with it ?




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[PHV]

You may try this:
DoCmd.RunSQL "UPDATE tblImport INNER JOIN tblPCourse " _
& "ON (tblImport.Person_ID = tblPCourse.PersonRef) " _
& "AND (tblImport.CourseCode = tblPCourse.CourseRef) " _
& "AND (tblImport.EnrolmentDate = tblPCourse.EnrolmentDate) " _
& "SET tblPCourse.EnrolmentStatus = " _
& "IIf(tblImport.EnrolmentStatus = 1,'Current'," _
& "IIf(tblImport.EnrolmentStatus = 2,'Completed','Withdrawn')) " _
& "WHERE tblImport.EnrolmentStatus In (1, 2, 3)"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[LittleSmudge]

Thanks PHV.

That did not solve the problem but the new error message that I got when I tried your solution ( Data type mismatch in criteria expression ) led me to the cause of the problem.

tblImport.EnrolmentStatus is of TYPE Text(1)


So I need

DoCmd.RunSQL "UPDATE tblImport INNER JOIN tblPCourse " _
           & "ON (tblImport.Person_ID = tblPCourse.PersonRef) " _
           & "AND (tblImport.CourseCode = tblPCourse.CourseRef) " _
           & "AND (tblImport.EnrolmentDate = tblPCourse.EnrolmentDate) " _
           & "SET tblPCourse.EnrolmentStatus = " _
           & "IIf(tblImport.EnrolmentStatus = '1','Current'," _
           & "IIf(tblImport.EnrolmentStatus = '2','Completed'," _
           & "IIf(tblImport.EnrolmentStatus = '3','Withdrawn',Null)));"

Regards,



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.