type conversion failure

I have a table that looks something like this:

The empty fields can be null or an empty string, I’m not sure. I want to update the firstName field to be all the characters in the name field until the –, whether firstname was empty or not. It doesn’t matter what was in there before the update.

I tried the following queries without success:

update table1
set firstname= Left([Name],InStr(1,[Name],"-")-1)

But I got a conversion error on two records.

I tried this:

update table1
set firstname= iif(name is null,firstname,Left([Name],InStr(1,[Name],"-")-1))  

But I still got an error.

Why is this happening and how to I fix it?

Answer

The reason you are getting an error is that you cannot use a string function on a null value. First you need to turn the Nulls into blanks, and then extract the name. IIf(IsNull([Name]),"",Left([Name],InStr(1,[Name],"-")-1))

This should prevent the type conversion