Access Trim after Special Character?

Hello Everyone,
I have a table that lists each of our stores like:
01 - StoreName 1
02 - StoreName 2
03 - StoreName 3

Ideally I would like to be able to Trim the name so it would be like:
StoreName 1
StoreName 2
StoreName 3

The field is called StoreName. Can some help me with the appropriate code to use in the Query?

Thanks,
Jester
smart2009Asked:
Who is Participating?
 
Helen FeddemaConnect With a Mentor Commented:
I see that I had an underscore in the SQL instead of a dash, which is what I intended.  Here it is again:

SELECT tblStores.StoreName, Mid([StoreName],InStr([StoreName],"-")+2) AS Store
FROM tblStores;

(this way there is no extra space in the last item name)
0
 
HainKurtSr. System AnalystCommented:
use

SELECT mid('02 - StoreName 2', 6)
0
 
käµfm³d 👽Commented:
Could you do it like this?
UPDATE stores
SET storename = MID(storename, INSTR(1, storename, "- "))

Open in new window

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
käµfm³d 👽Commented:
Correction:
UPDATE stores
SET storename = MID(storename, INSTR(1, storename, "- ") + 2)

Open in new window

0
 
HainKurtSr. System AnalystCommented:
I mean

mid(name,6)

assuming it always first 5 char to be removed
0
 
smart2009Author Commented:
Sorry I guess I needed to clarify I don't want to change the data in the table I would just like to have it shortend when using it in a Query. Sorry!
0
 
smart2009Author Commented:
HainKurt, I can't use the 6 Character because once we get to 100 storenames then it becomes 7 and so on.
0
 
Patrick MatthewsCommented:
A slight tweak to kaufmed's.  It returns an empty string if there is no hyphen:


UPDATE stores
SET storename = Trim(MID(storename, INSTR(1, storename & "-", "-") + 1))

Open in new window

0
 
Helen FeddemaCommented:
Try this:

SELECT tblStores.StoreName, Mid([StoreName],InStr([StoreName],"_")+6) AS Store
FROM tblStores;

and here is the query in Datasheet view:

Mid-and-Instr-demo.bmp
0
 
smart2009Author Commented:
This is exactly what I was looking for! THX!
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.