Solved

How extract string of characters to the right of the 1st hyphen

Posted on 2016-09-13
4
42 Views
Last Modified: 2016-09-13
If I have a string of characters that looks like this:

#10 Small - something - something else

And I want to extract in query designer this part of it: (everything to the right of the 1st hyphen not including the space in front of the 1st "something")

something - something else

How can I do this?
0
Comment
Question by:SteveL13
  • 2
4 Comments
 
LVL 84
ID: 41796531
Use a combination of Mid and Instr.

What have you tried so far?
0
 

Author Comment

by:SteveL13
ID: 41796536
Right([tblPaper.Description],InStr([tblPaper.Description],"-")-2)

But it isn't even close.
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 41796554
Mid([tblPaper.Description], InStr([tblPaper.Description], "-") + 2, Len([tblPaper.Description]) - InStr([tblPaper.Description], "-"))
0
 
LVL 19

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 250 total points
ID: 41796569
in case there may not be a space after the first hyphen, here is a slight modification to what Scott wrote to TRIM instead:
trim(Mid([tblPaper.Description], InStr([tblPaper.Description], "-") +1 ))

Open in new window

also, the 3rd argument, how many characters to get, is optional so I left it out

if you are doing this in a query, create another column to filter for only records having a dash:

field: Description
table: tblPaper
criteria: Like "*-*"

btw, Description is a reserved word

http://allenbrowne.com/AppIssueBadWord.html
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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 …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

821 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