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

[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!

 

[SgtJarrow]

How comfortable are you with VBA code? I think your best is to create a simple function that loops through the last characters of your value until it finds a non-alpha character, trimming off the alphas as you go. If you need some help, let me know and I will put together a sample bit of code.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[mmck1964]

I only know it can be done. I basically only work in the generic design views. I can give it a try though!

 

[SgtJarrow]

This should be about right...

Public Function StripAlphaEnd(strValue As String) As String

    Dim strTemp As String
    strTemp = strValue
    
    Do Until Not IsNumeric(Right(strTemp, 1))
        strTemp = Left(strTemp, Len(strTemp) - 1)
    Loop
    
    StripAlphaEnd = strTemp

End Function

Paste that into a new module in your database. Save it with any name.

Then in your query, you would put the following in field line:

StripedValue: StripAlphaEnd([fieldname])

FINALLY - please note I did not include any error handling or prechecking....if the value is all numeric or blank, you will probably get an error and should account for that.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer