Solved

delete string from field

Posted on 2014-01-15
6
345 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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.

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now