?
Solved

string comparision in access

Posted on 2005-03-28
7
Medium Priority
?
235 Views
Last Modified: 2008-01-09
hi ,
I need to update all rows in a table only for a certain field.
however i only need to update the value starting from position 5 to 3 byes.
like
update table_a
set field_a(5,3) = "555"

but i do not know the proper syntax.
thanks
0
Comment
Question by:chakrika
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 10

Expert Comment

by:perkc
ID: 13648011
Use the 'Replace' function:

UPDATE table_a
SET field_a = replace([table_a]![field_a],5,3,"555");

perkc
0
 

Author Comment

by:chakrika
ID: 13652051
Hi i tried to do that. but i get the following  error message:
Microsoft Access didn't update 0 field(s) due to a type conversion failure, 0 records(s) due to key violations, 0 records(s) due to lock violations, and 32 record(s) due to validation rule violations Do you want to continue running this type of action query anyway? To ignore the error(s) and run the query, click yes. For an explanation of the causes of the violations, click Help.
0
 

Author Comment

by:chakrika
ID: 13652185
I changed teh query to do this.
UPDATE table_a
SET field_a = replace(5,3,"555");

but then it replaced the entire value in the table with onlyjust '5'.

thanks
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:chakrika
ID: 13652373
ok i understand that the replace function should be used like this
REPLACE(old_text,start_num,num_chars,new_text)

 
But still i get the error message i mentioned above. I cannot update.
0
 
LVL 10

Expert Comment

by:perkc
ID: 13652759
Give me a couple of examples of the type of values that you have in the 'field_a' field.

Also, what format is the field_a table field? Text? Number?
0
 
LVL 2

Accepted Solution

by:
kalios earned 720 total points
ID: 13653115
chakrika, use something like this.
lets say the field_a is 10 bytes. so use this:

UPDATE table_a
SET table_a.[field_a] = MID(table_a.[field_a],1,4) & "555" & MID(table_a.[field_a],8,3);

Kalios
0
 

Author Comment

by:chakrika
ID: 13653130
super kalios.

works like a charm
thanks
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question