removing letters from s/n

[Paul sullivan]

i am working on a db which has a serial number field. The
problem is over time users have created or changed the
entries from only being "numbers" to being " numbers + a
letter" at the end . ex. 12345/12345a. How can I remove
the letter found at the end of the s/n only- not all of
the s/n's have this problem.
Any suggestions would be appreciated.

 

[Ken Snell]

The letter (just one letter) is always at the end if it is there? Try this:

NewNumber = Left(OldNumber, Len(OldNumber) + (IsNumeric(Right(OldNumber,
1)=False))

 

[Joan Wild]

i am working on a db which has a serial number field. The
problem is over time users have created or changed the
entries from only being "numbers" to being " numbers + a
letter" at the end . ex. 12345/12345a. How can I remove
the letter found at the end of the s/n only- not all of
the s/n's have this problem.
Any suggestions would be appreciated.

val("12345a")
12345

 

[John Spencer (MVP)]

Pardon me for jumping in.

Val was my first thought also, but then I saw this might be dangerous if the
serial number field can have leading zeroes. Val would strip any leading zeroes.
I was going to suggest adding a format statement along with the Val to force
formatting to 5 characters, then I realized that the "Number" might not always
be just five numeric characters.

With due respect,
John

 

[Joan Wild]

All valid points John; without further details from the OP, we'll never
know.

 

[Paul Sullivan]

Alittle more info; This is a nonAccess legacy system
which is going to be changed in the near future. Sorry to
say but there isn't any standard lenght for the s/n (been
told to deal with the problem as is!!) s/n's can be 5, 6,
or 7 numbers with the letter attached to the end of some
but not all of them. Right now people are in the process
of creating new part numbers with the correct s/n and they
will then have to go back and delete the bad entry ( there
are approx. 11,000 part numbers!)-I have been trying to
find away to manipulate the data and create a new
temporary db using Access until they impliment a new
system in approx. a year. Right now I am able to export to
Excel and then import that into Access.
Thanks Again Everyone
Paul

 

[Ken Snell]

My suggestion will work regardless of the length of the number text.

 

[Gary Walter]

Hi Paul,

PMFBI

In the post I get from Ken, it was missing a parenthesis,
but with it, Ken's formula should be exactly what you want
I would believe.

OldNumber="12345a"
?Left(OldNumber, Len(OldNumber) + (IsNumeric(Right(OldNumber,1))=False))
12345
OldNumber="12345"
?Left(OldNumber, Len(OldNumber) + (IsNumeric(Right(OldNumber,1))=False))
12345

Good luck,

Gary Walter

 

[Ken Snell]

Hi Paul,

PMFBI

In the post I get from Ken, it was missing a parenthesis,

You are correct. Thank you for catching the error.

< g >