type conversion failure

Question

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?

Erick 3E · Accepted Answer · 2016-06-01 21:54:52Z

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

Tudor Saru · Accepted Answer · 2016-06-01 16:36:20Z

what is the webaddress field?

from the table you have above it seems to me you want to:

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

Oh, gosh, sorry. I pasted the real query instead of the one that suites my example. Fixed it.

Collectives™ on Stack Overflow

type conversion failure

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related