Trying to strip out specific text from a field

[dmorand]

I've got a field in my database, and I want to retrieve all characters to the left of the first number value encountered. I'm not quite sure how to accomplish this

Here is an example of a field I'm working with:

PREDNISONE 10 MG = 2 TAB (10 MG = 2 TAB TAB)

I want to retrieve the value:
PREDNISONE

Any ideas?

[Stewart Ross]

Hi. There s no native function that I know of which can do what you want, but it is easy to devise a custom function in VB which does.

Copy the function below into a public code module (select Modules from the database window and open any existing module). If you do not have any existing modules create a new one, paste the function in, and save the module under any suitable name.

Public Function LeftmostChars(inputstring As String) As String
    'Finds the first position in the inputstring
    'of a numeric character and returns all characters
    'to the left of that position, with spaces trimmed on right
    '
    Dim PosFound As Integer
    Dim NumbertoCheck As Integer
    NumbertoCheck = 0
    Do
        PosFound = InStr(1, inputstring, CStr(NumbertoCheck))
        NumbertoCheck = NumbertoCheck + 1
    Loop Until (NumbertoCheck > 9) Or (PosFound > 0)
    If PosFound = 0 Then
        LeftmostChars = inputstring
    Else
        LeftmostChars = RTrim(Left$(inputstring, PosFound - 1))
    End If
End Function

To use it in a query, add a new column to the query and create a calculated field as in the example below, replacing the field name passed to the function the actual name of your field:

Drugname: LeftmostChars([Prescribed Dose])

No doubt there are simpler solutions than what I have come up with, but it was quick to do and it works...

-Stewart

[ADezii]

I've got a field in my database, and I want to retrieve all characters to the left of the first number value encountered. I'm not quite sure how to accomplish this

Here is an example of a field I'm working with:

PREDNISONE 10 MG = 2 TAB (10 MG = 2 TAB TAB)

I want to retrieve the value:
PREDNISONE

Any ideas?

Just a different approach:

Public Function fRetrieveCharsBeforeNumber(strValue As String) As String
Dim intNumOfChars As Integer
Dim intCounter As Integer
 
If Len(strValue) = 0 Then Exit Function
 
intNumOfChars = Len(strValue)
 
For intCounter = 1 To intNumOfChars
  If IsNumeric(Mid$(strValue, intCounter, 1)) Then
    fRetrieveCharsBeforeNumber = Left$(strValue, intCounter - 1)
      Exit For
  Else
    fRetrieveCharsBeforeNumber = vbNullString
  End If
Next
End Function