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

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?
SteveL13Asked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
Mid([tblPaper.Description], InStr([tblPaper.Description], "-") + 2, Len([tblPaper.Description]) - InStr([tblPaper.Description], "-"))
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Use a combination of Mid and Instr.

What have you tried so far?
0
 
SteveL13Author Commented:
Right([tblPaper.Description],InStr([tblPaper.Description],"-")-2)

But it isn't even close.
0
 
crystal (strive4peace) - Microsoft MVP, AccessConnect With a Mentor Remote Training and ProgrammingCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.