Tyoe conversion error in update query

[singhals]

Hi,

I am running an update query where i am basically stripping of the last digit of a data. Thus for example:

20110068876v
180068877v

should become,

20110068876
180068877

to do this i am running update query with Update To as

Left([Fieldname],Len([Fieldname]-1))

However i am getting a type conversion error on all the rows.

Could someone please help me out here

Thanks!

[jimatqsi]

I presume you are updating the same field as the source of the data. So you are starting out with a string field and the program thinks you are plugging a number into it.

Try wrapping your equation in a Cstr function to make sure the program knows the result is a string and not a number. So your code will look like this:
Cstr(Left([Fieldname],Len([Fieldname]-1)))

Also, are you sure all the fields are populated? What happens if one of these has a null value? If that's possible, you need to code around that also.

Jim

[singhals]

Thanks. I tried running this but am still getting the same error of 'type conversion failure' for all fields. I have confirmed that all the fields are populated with data and none of them is blank.

Also, the Data Type is currently text, although I would be eventually wanting to convert these to numbers as I would be using them to link to another database which has this data in number format only.

Regards,

[jimatqsi]

Are you joining two or more tables in this query? Perhaps you are joining a text field to a numeric field. I believe that will give this type of error.

Jim

[OldBirdman]

The formula

Left([Fieldname],Len([Fieldname]-1))

should be

Left([Fieldname],Len([Fieldname])-1)

[jimatqsi]

Of course, yes, my bad. Good catch, OldBirdman.

Jim

[singhals]

Thanks a lot guys! this worked perfectly.