removing letters from s/n

  • Thread starter Thread starter Paul sullivan
  • Start date Start date
P

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.
 
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))
 
Paul said:
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.
Click to expand...


val("12345a")
12345
 
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
 
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
 
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
 
Back
Top