?
Solved

delete string from field

Posted on 2014-01-15
6
Medium Priority
?
363 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
[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
  • 3
  • 2
6 Comments
 
LVL 48

Expert Comment

by:Dale Fye
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 48

Accepted Solution

by:
Dale Fye earned 2000 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 51

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 48

Expert Comment

by:Dale Fye
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
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.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

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