Solved

delete string from field

Posted on 2014-01-15
6
351 Views
Last Modified: 2014-01-16
Access 2010:

I have  table:
table_images
2 fields:
Primary_ number
Primary_Image

What I need:

In the field "Primary_Image"

If any of the data has a name with a  "_ "<UNDERSCORE>  in it ... like below

3NU39_AW99.JPG

I need it to be changed to

3NU39.JPG

so starting at the  "_ "<UNDERSCORE>   delete the string behind it. but keep the  .jpg

example: again
primary_image
3NU24_AW99.JPG
3NU22_AW99.JPG
24Z173_AS01.JPG
3NU21_AW99.JPG

will be:
primary_image
3NU24.JPG
3NU22.JPG
24Z173.JPG
3NU21.JPG


Thanks
fordraiders
0
Comment
Question by:fordraiders
  • 3
  • 2
6 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39783023
After backing up your table, try something like

UPDATE yourTable
SET Primary_Image = Left([Primary_Image], instr([Primary_Image], "_")-1 & Mid([Primary_Image], instrrev([Primary_Image]), "."))
WHERE instr([Primary_Image], "_") > 0

You might want to start out with:

SELECT [Primary_Image], Left([Primary_Image], instr([Primary_Image], "_")-1 & Mid([Primary_Image], instrrev([Primary_Image]), ".")) as ModPrimImage
WHERE instr([Primary_Image], "_") > 0
0
 
LVL 3

Author Comment

by:fordraiders
ID: 39783145
get a wrong number of arguments error..
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 39783245
a couple of parenthesis issues.  For the SELECT statement try:

SELECT [Primary_Image], Left([Primary_Image], instr([Primary_Image], "_")-1) & Mid([Primary_Image], instrrev([Primary_Image], ".")) as ModPrimImage
WHERE instr([Primary_Image], "_") > 0

for the UPdate it would look like:

UPDATE yourTable
SET Primary_Image = Left([Primary_Image], instr([Primary_Image], "_")-1) & Mid([Primary_Image], instrrev([Primary_Image], "."))
WHERE instr([Primary_Image], "_") > 0
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39783265
You could use this expression:

SET Primary_Image = Left([Primary_Image], InStr([Primary_Image], "_") - 1 & ".jpg"

/gustav
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39783312
always trying to make things easier, aren't you Gustav?

I guess I'm just so used to pulling something off the front, and something off the back that it never occurred to me to do it the simple way (although you forgot the closing paren as well.

@fordraiders

If you are going to do it that way, I would modify the WHERE clause to look something like:

WHERE InStr([Primary_Image], "_") > 0  AND InStr([Primary_Image], ".jpg") > 0

This will make sure you do not inadvertently change the extension of some other image type.
0
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 39785441
Thanks
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

786 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