Type Conversion Failure in Update Query

[Kevin Beck via AccessMonster.com]

Hi, everyone,

I have searched posts and have come across this problem but can't seen to
solve it at all. I have a table [LOT CARDS] that has a Last, First, MI
field called 2nd_Right_Interment, which I need to split into three fields--
last, First and Middle Initial.

I keep getting "type conversion failure" All are text fields. I have tried
Make-Table query and get the same results.

It prompts this error and I also get "duplicate output destination" on the
make-table query. I'm an Excel 'transplant' so I'm quite new to SQL:

UPDATE [LOT CARD] SET [LOT CARD].[2nd_Right_Interment] = Left(
[2nd_Right_Interment],InStr([2nd_Right_Interment],",",-1)), [LOT CARD].
[2nd_Right_Interment] = Mid([2nd_Right_Interment],InStr(
[2nd_Right_Interment]," ",+1)), [LOT CARD].[2nd_Right_Interment] = Right(
[2nd_Right_Interment],InStr([2nd_Right_Interment]," ",-1))
WHERE ((([LOT CARD].[2nd_Right_Interment])<>"0"));

i set the where to not include "0" because every null is replaced with a
zero.

Any help appreciated, Thanks,

Kevin

 

[Ken Snell [MVP]]

I think you have some misplaced commas in the InStr arguments:

UPDATE [LOT CARD] SET [LOT CARD].[2nd_Right_Interment] = Left(
[2nd_Right_Interment],InStr([2nd_Right_Interment],",")-1)), [LOT CARD].
[2nd_Right_Interment] = Mid([2nd_Right_Interment],InStr(
[2nd_Right_Interment],",")+1)), [LOT CARD].[2nd_Right_Interment] = Right(
[2nd_Right_Interment],InStr([2nd_Right_Interment],"<")-1))
WHERE ((([LOT CARD].[2nd_Right_Interment])<>"0"));


or perhaps you meant this:

UPDATE [LOT CARD] SET [LOT CARD].[2nd_Right_Interment] = Left(
[2nd_Right_Interment],InStr([2nd_Right_Interment],",")-1)), [LOT CARD].
[2nd_Right_Interment] = Mid([2nd_Right_Interment],InStr(
[2nd_Right_Interment]," ")+1)), [LOT CARD].[2nd_Right_Interment] = Right(
[2nd_Right_Interment],InStr([2nd_Right_Interment]," ")-1))
WHERE ((([LOT CARD].[2nd_Right_Interment])<>"0"));

 

[Kevin Beck via AccessMonster.com]

Thanks, Ken,

I put in the code and it still seems to give me "type conversion failure."

I restricted the query to one field at a time to simplify things:

UPDATE [LOT CARD] SET [LOT CARD].[2nd_Right_Interment] = Left(
[2nd_Right_Interment],InStr([2nd_Right_Interment],",")-1)
WHERE ((([LOT CARD].[2nd_Right_Interment])<>"0"));

Thanks,

Kevin

 

[Kevin Beck via AccessMonster.com]

Actually--scratch previous message--

It worked perferctly, thank you!

I appreciate you getting back so quickly,

Kevin Beck

 

[Ken Snell [MVP]]

You're welcome.