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.

[code=vb]Public Function LeftmostChars(i nputstring 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(NumbertoCh eck))
NumbertoCheck = NumbertoCheck + 1
Loop Until (NumbertoCheck > 9) Or (PosFound > 0)
If PosFound = 0 Then
LeftmostChars = inputstring
Else
LeftmostChars = RTrim(Left$(inp utstring, PosFound - 1))
End If
End Function[/code]

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:
[CODE=vb]
Public Function fRetrieveCharsB eforeNumber(str Value 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
fRetrieveCharsB eforeNumber = Left$(strValue, intCounter - 1)
Exit For
Else
fRetrieveCharsB eforeNumber = vbNullString
End If
Next
End Function[/CODE]