Removing numeric and alpha from

[mlrucci]

Good morning, I am attempting to update a table, preference is an update qry, by removing the numbers and _ from a table field. I am struggling with trying to remove "_", numeric and alpha at the end. In other words, from the numeric to the end needs to be removed (including the preceding "_". The data is inconsistent and struggling with trying to make this work, Can't do a left or right because of the inconsistency in the data. Any Ideas?

Original:
URDNKRECREM_JMA_11162021
URASNRCRSVC_11162021B


BPIN_NRSVEN_BK_11022021
URASNRCRSVC_11022021_actionable


Modified:
URDNKRECREM_JMA
URASNRCRSVC
BPIN_NRSVEN_BK
URASNRCRSVC

Thank you in advance for your assistance

[Welshgasman]

Use instrrev() and left functions?
Hmm that will not process your last entry.

Perhaps split() inside your own function?

[Micron]

Is this the same issue as here but just a different character now?

https://www.accessforums.net/showthr...160#post488160
Need to delete all preceding 0s, delete anthing after the second "-" and removed the "-" in the middle.

You'll need a function that loops over every character until it encounters a number, then get the string using Left and the number that the loop generates.
EDIT can you adapt this one that I wrote for your other issue (but didn't post because you didn't answer my question(s).

Function mlrucci(strIn As String) As String
Dim strOut As String
Dim i As Integer

'0871900602-601116-7-1
'delete all leading 0's and anthing after the second "-". Remove the "-" in the middle.
'End result should be: 871900602601116
i = 1
strOut = Left(strIn, InStr(InStr(strIn, "-") + 1, strIn, "-") - 1)
Do While Mid(strOut, i, 1) = "0"
   i = i + 1
Loop

mlrucci = Mid(strOut, i)
'Debug.Print mlrucci

End Function

[mlrucci]

Micron, yes and no, same issues but dealing with more inconsistencies. The other had no deviation in the layout of the data.

[CJ_London]

if all the numbers start with 1 then this would would

?left("URASNRCRSVC_11022021_actionable",instrrev(" URASNRCRSVC_11022021_actionable","_1")-1)
URASNRCRSVC

[mlrucci]

Ajax, unfortunately, the numbers change every month. I used that code originally, but there are times that it doesn't work because of that variable every month.

[Gicu]

Maybe have a look at this sample using a custom VBA function (just copy it into a standard module).

Cheers,
Vlad

[Micron]

Well, this is what I came up with

Function mrlucciVar(strIn As String) As String
Dim strOut As String
Dim i As Integer

i = 1
Do While Not IsNumeric(Mid(strIn, i, 1))
   strOut = Left(strIn, i)
   i = i + 1
Loop
mrlucciVar = Left(strOut, Len(strOut) - 1)
'Debug.Print mrlucciVar

End Function

[Gicu]

I used the one in here then a left with instr. It is a handy function to have as you can use it in many ways (ToNumber, OnlyNumber, FromNumber):
https://techjogging.com/extract-numb...cessexcel.html
Cheers,

[mlrucci]

Gicu, Thank you for the db example. I attempted to use the example and it works great sometimes. I am not sure what I am missing, but it seems that when I attempt to link/ or use relationship with the PCNID, it crashes and gives run-time error 13, type mismatch. I am including a stripped-down version of what I am working with. The db is based on importing information from excel, so pardon my naming conventions as they are not accurate as to allow import. Ideas?


ExampleDB_ExtractNumber.zip

[Gicu]

The error is caused by all the empty (Null) PoolID records; open the qryCalcMLC_PC_OMOrder_PNC query in design view and add Is Not Null in the criteria row for the PoolID (you only get 72 records not null in the sample).

Cheers,

[mlrucci]

Whoop Whoop, That was it!! Thank you very much for your help!! Made my day. Have been trying for hours and was really stuck. Have a great New Years!

[mlrucci]

Here is one for you, as the code reads in the module, it extracted the number in the string, but also causes a problem with the section I want to keep

Original:
UR2MPSRR_10292021

Result: It has a number in the string causing
#Func!

Need:
UR2MPSRR

Is there a way make this work.. I have 31,000 entries and 3,000 have this issue and do not want to do it manually. Ideas?

[CJ_London]

try this function instead

Function loseDate(txt)
Dim i As Integer


    txt = Nz(txt, "")


    For i = 1 To Len(txt) - 8


        If IsNumeric(Mid(txt, i, 8)) Then Exit For


    Next i


    If txt <> "" Then loseDate = Left(txt, i - 2)


End Function

[mlrucci]

That did it. Thank you!!!!!! What a long day. You are the best.