How can I remove the last alpha characters in a query?

[mmck1964]

I have field called [LOCATION] that varies in length. I need to remove any alpha characters at the end, if there are any. Listed are some examples.

[LOCATION] [NEEDED]
PA 109S 55W 2N 1A PA 109S 55W 2N 1
WC 155A WC 155
PB4 68N 1UNH PB4 68N 1
B 3E 1P B 3E 1
XB1 203S 14W 2N 6PU XB1 203S 14W 2N 6
PB1 6 PB1 6
Thanks!

 

[Brianwarnock]

I haven't got time to work on it at the moment but I would be looking at a routine using something like
length=len(flda)
then a loop with IIf(Not IsNumeric(Right([flda],1)),length-1) to obtain the length of the field then use Left to copy new field

Obviously the above is rough.

Brian

 

[mmck1964]

I have tried a variety of Right, Left, Len, Instr with no success. I will work on what you have sent, but if you have any ideas, they would be appreciated. Also I am doing this in a Query, design view.
Thanks!

 

[EMP]

If your version of Access has the StrReverse() function, try this:-

Needed: Left([LOCATION],Len([LOCATION])-InStr(StrReverse([LOCATION])," ")+1) & Val(Mid([Location],Len([LOCATION])-InStr(StrReverse([LOCATION])," ")+1))

^

 

[mmck1964]

WOW!!! That worked GREAT!!!!. Thank you so very much.

 

[Brianwarnock]

A neat trick by EMP.
For anybody following this thread whose string does not have a last group following a blank that has the format nndd then the following function works for all layouts.

Brian

Function lessalpha(flda As String) As String
length = Len(flda)

Loop1:
If Not IsNumeric(Mid(flda, length, 1)) Then
length = length - 1
GoTo Loop1

End If
lessalpha = Left(flda, length)
End Function