remove chars after 2nd dash - Access Developer Forum


	Back to Access Developer Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

remove chars after 2nd dash

Bryon Relyea

3 years ago

Good morning, my access gurus -

I'm familiar with the test to remove characters after a dash/hyphen in my data sets. However, I have 5 stinking products which actually have two dashes in their format. How would I adjust the code below to remove data after the second dash?

Code:
Left([TableName].[FieldName] & "-",InStr([TableName].[FieldName] & "-","-")-1)

As always, thank you for your time and assistance!!!

	Alex Hedley @Reply 3 years ago
Can you share an example of the original data and the expected outcome?

	Bryon Relyea OP @Reply 3 years ago
Original: ABCD-1234-56789 Desired Outcome: ABCD-1234 these are product SKUs, where the ABCD-1234 is the same for all products, and the '56789' is a specific package size. I want to remove the package sizes from all material because other associated data elements are the same regardless of package size. removing the pack size will allow for removal of redundant info.

	Alex Hedley @Reply 3 years ago
InStrRev should be able to get the last '-' then just do Left() to that position.

	Bryon Relyea OP @Reply 3 years ago
So, do I just replace "InStr" with "InStrRev" ?

	Alex Hedley @Reply 3 years ago
Untested, give it a go. You want to find the pos of the '-' and then get all the text up until that. Did you watch the linked video? Code: `Left([TableName].[FieldName], InStrRev([TableName].[FieldName], "-")-1)`

	Bryon Relyea OP @Reply 3 years ago
Thanks, Alex! will give it a try in a bit, and let you know how it goes.

	Bryon Relyea OP @Reply 3 years ago
Hello, Alex - THANK YOU!! this worked perfectly. output is exactly what I needed.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developer Forum.

Next Unseen

New Feature: Comment Live View