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!
The formula - Left([Fieldname],Len([Fieldname]-1))
should be - Left([Fieldname],Len([Fieldname])-1)
6  1881 
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
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,
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
The formula - Left([Fieldname],Len([Fieldname]-1))
should be - Left([Fieldname],Len([Fieldname])-1)
Of course, yes, my bad. Good catch, OldBirdman.
Jim
Thanks a lot guys! this worked perfectly.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Colleyville Alan |
last post by:
I am trying to run an update query to take information in a field and remove
the last 4 chars. For example, the following piece of data: DAA1.CTF,
should be converted to DAA1 (without the ".CTF")....
|
by: deko |
last post by:
I'm trying to update the address record of an existing record in my mdb with
values from another existing record in the same table. In pseudo code it
might look like this:
UPDATE tblAddress SET...
|
by: Mark Carlyle via AccessMonster.com |
last post by:
I have this update query that I am trying to run. I know the syntax is
messed up but do not know how to correct it.
Select 'UPDATE', Transactions,'Set = where =
' From "Get Daily Balances"
...
|
by: Riley DeWiley |
last post by:
I have an UPDATE query that is always setting 0 records. When I cut and
paste the SQL into Access and use it, it fails in the same way unless I
coerce the date fields to be '=now()', in which case...
|
by: midlothian |
last post by:
Hello
Trying to update a memo field in an update query using the Replace
function, and am getting a type conversion error. Here is my parameter:
CStr(Replace(CStr(),"$",Chr$(13) & Chr$(10)))
...
|
by: Reedsp |
last post by:
OS: MS XP
Access version: 2003 SP2
I am trying to use an update query to replace quote marks with nothing. In essence, I'm removing quote marks. I get a error message when a field is empty or...
|
by: gnortenjones |
last post by:
I have a linked table (to an oracle db), and I am trying to run a simple update query against it to change some data, but I am getting the following error:
"...didn't update 0 fields due to a type...
|
by: robsjonathan |
last post by:
hello world,
i have two tables stock and sales where one to many relationship is
created. The join type is " 2: include all records from stock table
and only those from sales where the joined...
|
by: GoNowhere |
last post by:
Hi There,
I'm attempting to update a short date formatted field with a consistent year without changing the Month or Day. Below is what I have so far, when trying to update it however I get a...
|
by: mjvm |
last post by:
HI,
I have a main table (tblStudents) that holds students where the unique field is StudentID; and a related table (tblNotes) on a one-to-many relationship, where the unique field is NotesID, and...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |
