Edit field

Fed Alexander
Fed Alexander used Ask the Experts™
Hello experts

I have a field called MRN in a table called insurance  that contains data
A-000-622-999 in the MRN field.

I want to delete  The first 6 characters and the dash in the middle between the 2 and the 9.

It should be 622999

Any help please
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

split("A-000-622-999","-")(2) &split("A-000-622-999","-")(3)

Open in new window

and as a function
Public Function returnUsefull(InputString As String) As String
returnUsefull = Split(InputString, "-")(2) & Split(InputString, "-")(3)
End Function

Open in new window

Most Valuable Expert 2015
Distinguished Expert 2018
That could be:

Number = Replace(Right("A-000-622-999",7), "-", "")

Open in new window


Thank you, if I use the split or the replace code for the field, would I need to enter the content of the field in the formula as you highlighted. I am planning to put this in a query

I have 1000 of records looking like this and I need the last 6 numbers only
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!


Worked nicely
John TsioumprisSoftware & Systems Engineer

Would you mind sharing some "info" why my solution was not approved.
The only case that the accepted solution is "better" is only if you use it directly on a query as split is not usable via straight Access SQL.
On the other hand my solution is far more versatile as it splits the data based on the delimiter and not on the length of the data...is easily maintainable and it can easily altered to support more cases.
At first :
Replace(Right("A-000-6222-999",7), "-", "")
should fail just because the string has for some reason one more character...(probably this is not the case but you just can't handle it.
Mark EdwardsChief Technology Officer

It's obvious to me why it wasn't picked....  You even mentioned one of the reasons it wasn't picked....  And there are other reasons it doesn't work...  But there are reasons why the picked one wouldn't work if we started assuming things that the user didn't include.
We have to assume that the user wanted a solution that applied the logic to handle just this exact value, not any value in the field, including nulls.

Personally, based on the information supplied by the user, I would have been temped to give a solution that applies the logic to any value in the field:
TextValue = REPLACE(MID([MRN] & "",7),"-","")
This would handle Nulls, but put an empty string if a field was Null.
If I needed a numerical result:
NumericValue = Val(REPLACE(MID([MRN] & "",7),"-",""))
If I wanted to keep a Null and only return a non-empty string:
Value = IIF([MRN] Is Null,Null,REPLACE(MID([MRN],7),"-",""))
If I wanted to keep a Null or return a numeric value:
Value = IIF([MRN] Is Null,Null,Val(REPLACE(MID([MRN],7),"-","")))

...and that's just a few examples...  You could also use the NZ() function, or data type conversion functions like CStr(), etc.  There's a multitude of ways to do something in this business.  So many, in fact, that respondents' imaginations start to run wild and imagine things that the author never mentioned or cared about.
John TsioumprisSoftware & Systems Engineer

@Mark you are falling the same trap... everything is around 7 ...what ever change to the string...everything will fall apart
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Personally, I think John's solution is far more elegant.

But without additional input from the user:

1.  Are there any records which contain NULL values?
2.  Is the structure of that field always identical to the provided example, or can some of those number segments have more or fewer digits?
Mark EdwardsChief Technology Officer

7 is the authors parameter - nothing else.  Is there a different (better) limit/position that the author would prefer, or is he "stuck" with 7?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial