Need help seperating data in Access 97

I have the need to seperate the alpha characters from the numeric in Access. I have a field that contains data in it that is both alpha and numeric. (example: 123456A)

What I need to do is to seperate the Alpha character from the numeric. I have created a seperate column that will house the Alpha.

So I want to have col A contain all the numbers and col B contain the letters.

So my question is this : How do I remove the Alpha characters and move them into col B and leave the numeric intact?

I cant find anyone here at work who knows how this is done. So I have come to the experts: Inquireing minds want to know.

 

are the alpha chars mixed in like this.. 1b3c5d etc.. or are they in a fixed prefix or suffix position?

 

They are always the last character of the string

 

I hate my connection here at work... if it cuts out on me again, I'm going to scream..

any way. To make your code modular we use two functions that you can call for any value like your "123456A"

function ParseAlpha(byval Mystring)
AlphaValue=right(Mystring,1)
ParseAlpha=AlphaValue
end function

Function ParseNumeric(byval Mystring)
NumericValue=left(mystring,len(mystring)-1)
ParseNumeric=NumericValue
end function

'to get the separate values follow the example below.

MyStringValue="123456A" '*arbitrary variable just to test with


AlphaValue=ParseAlpha(MyStringValue)
NumericValue=ParseNumeric(MyStringValue)

I would make a new module or put these functions in a public module so that you can call them from where ever you want, whenever you want.

 

Thank you Master Kodo, I will give this a try and let you know what happens.

 

OK I cant get this to work in this access version. I also have another friend here working with it and she is trying to figure it out as well. Unfortunatly here at work we are only using access 97. it sucks but thats all we got.


Any Ideas? I'm just grasping at straws, I dont have a clue how to do this?

Any help will be greatly welcomed.

 

are you getting any errors? if so, what are they?

if you can post an example of how you're trying to implement it, that would help too.

 

here's an edit. because sometimes I'm stuck in ASP instead of VBA mind.


ok put this in the module...

----------------------------------------------
Function ParseAlpha(ByVal Mystring As String) As String
AlphaValue = Right(Mystring, 1)
ParseAlpha = AlphaValue
End Function

Function ParseNumeric(ByVal Mystring As String) As Single
NumericValue = Left(Mystring, Len(Mystring) - 1)
ParseNumeric = NumericValue
End Function
--------------------------------------------

' the following goes into the form event code or whereever.
'to get the separate values follow the example below.

MyStringValue="123456A" '*arbitrary variable just to test with make sure you dim the variable properly. You'll most likely not even use this.. so make sure your value is being passed and that you replace the "MYSTRINGVALUE" with the name of your variable.


Dim AlphaValue As String
AlphaValue = ParseAlpha(MyStringValue)

Dim NumericValue As Single
NumericValue = ParseNumeric(MyStringValue)

 

here is what we are working on.

Option Compare Database
Option Explicit

Public Function ParseAlpha(ByVal Mystring)
Dim AlphaValue As String
Dim ParseAlpha As String

AlphaValue = Right(Mystring, 1)
ParseAlpha = AlphaValue
End Function

Public Function ParseNumeric(ByVal Mystring)
Dim NumbericValue As Integer
Dim ParseNumeric As Integer

NumericValue = Left(Mystring, Len(Mystring) - 1)
ParseNumeric = NumericValue
End Function

'to get the separate values follow the example below.

MyStringValue = "123456A" '*arbitrary variable just to test with


AlphaValue = ParseAlpha(MyStringValue)
NumericValue = ParseNumeric(MyStringValue)

''Supp Issue: Right([WOTRejectStatusInfo]![WOT Order Tool Number],1)



compile error when I use the Function ParseAlpha(ByVal Mystring) and it highlights at ParseAlpha = AlphaValue

 

read my post JUST above yours.. see if it helps

 

I will give that a try. Thanks for the input.

 

suckup...j/k

 

you will soon learn too, mr_flea

 

No i won't!!! You'll never take me alive!!!

sorry master kodo - i beg your forgiveness

 

ok ok.. stop groveling. I hate it when people grovel.. it's always Sorry this and sorry that and I'm not wOrthy..

 

did this work for you Lordelin?