Count Calculation with Alpha Numeric entry

  • Thread starter Thread starter Randy
  • Start date Start date
R

Randy

Is there a way to make a simple mathematical calculation when the number is
Alpa Numeric. Example: A12345 - A12340 should be 5. I cant get it to work
using: =[EndCertNo]-[BeginCertNo] I have 12 different certificates I need
to have accountability. Some start with A, some X, some Z
etc...Thanks..Randy
 
Are all of the "numbers" one letter followed by numbers? If so, try

=CLng(Mid([EndCertNo], 2)) - CLng(Mid([BeginCertNo], 2))

Mid will give you the remainder of the string starting with the 2nd
character, but it returns a string value. CLng will convert the value to a
Long Integer.
 
Thanks Wayne, this works great!
Wayne Morgan said:
Are all of the "numbers" one letter followed by numbers? If so, try

=CLng(Mid([EndCertNo], 2)) - CLng(Mid([BeginCertNo], 2))

Mid will give you the remainder of the string starting with the 2nd
character, but it returns a string value. CLng will convert the value to a
Long Integer.

--
Wayne Morgan
MS Access MVP


Randy said:
Is there a way to make a simple mathematical calculation when the number
is Alpa Numeric. Example: A12345 - A12340 should be 5. I cant get it
to work using: =[EndCertNo]-[BeginCertNo] I have 12 different
certificates I need to have accountability. Some start with A, some X,
some Z etc...Thanks..Randy
Click to expand...
Click to expand...
 
Back
Top