Type Conversion Failure in Update Query

  • Thread starter Kevin Beck via AccessMonster.com
  • Start date
K

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
 
K

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"));
 
K

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
 
K

Kevin Beck via AccessMonster.com

Actually--scratch previous message--

It worked perferctly, thank you!

I appreciate you getting back so quickly,

Kevin Beck
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top