cutting strings

[BayesTex]

Hi friends,

I am trying to cut strings in the field [Specie] to print in reports, like these:

"Jacaranda 5" to show "Jacaranda sp."
"Smilax 2" to show "Smilax sp."
"Pouteria 1" to show "Pouteria sp."

but, I have fields like:
"Juglans neotropica" "Casearia angustifolia Diels", etc, that I need leaving this way, without modification.

I mean, I want to eliminate the 'numeric' character inside the strings, if the string has one.

=Nz(cut[Specie],???) & Nz(" sp.")

I understand that exists a command "cut", to cut text strings, but how could be used if strings have different lengths (9, 6 and 8 characters before numeric character) , as you could see in samples I wrote before.

I guess it would be done finding the white space first, and use it to get the needed length. Could anyone explain me how can I do this?

Is there any option?

Thanks in advance.

 

[WayneRyan]

Bayes:

Put this function in a Public Module:

Public Function NewString (OldString As String) As String
Dim Temp As String
Dim i As Long

Temp = ""
For i = 1 to Len(OldString)
   If Not IsNumber(Mid(OldString, i, 1)) Then
      Temp = Temp & Mid(OldString, i, 1)
   End If
   Next i
NewString = Temp
End Function

Then, in the query that feeds your report, add a new column:

NewField: NewString([YourStringField])

Then just use NewField in the Report, it will be in the dropdown list.

hth,
Wayne