Letters to Numbers

  • Thread starter Thread starter Andrew
  • Start date Start date
Is it possible to convert letters (Like initials for an employees name) to
numbers?
Click to expand...

Yes. Chr("A") is equal to 65 for example.

What exactly are you trying to accomplish? If you're trying to come up with a
unique identifier, you're on the wrong track: David Aaron Palmer and Dorothy
Ann Peterson will give the same number.
 
Below is a slightly more veratile function which handles both upper and lower
case alphabetical character as an input variable.

'---------------------------------------------------------------------------------------
' Procedure : ConLtrToNo
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Convert Alphabetical characters into their numeric values
' a=1, b=2, c=3, ... and/or A=1, B=2, C=3, ...
' Copyright : The following may be altered and reused as you wish so long as
the
' copyright notice is left unchanged (including Author, Website
and
' Copyright). It may not be sold/resold or reposted on other
sites (links
' back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sLetter : The character to convert into a numerical value
'
' Usage Example:
' ~~~~~~~~~~~~~~~~
' ConLtrToNo("A")
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
'
**************************************************************************************
' 1 2010-Feb-13 Initial Releas
'---------------------------------------------------------------------------------------
Function ConLtrToNo(sLetter As String) As Integer
Dim iChrVal As Integer

On Error GoTo Error_Handler

iChrVal = Asc(sLetter)
Select Case iChrVal
Case 65 To 90 'Values A through Z
ConLtrToNo = iChrVal - 64
Case 97 To 122 'Values a through z
ConLtrToNo = iChrVal - 96
Case Else
ConLtrToNo = 0
End Select

Error_Handler_Exit:
On Error Resume Next
Exit Function

Error_Handler:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf &
"Error Number: " & _
Err.Number & vbCrLf & "Error Source: ConLtrToNo" & vbCrLf & "Error
Description: " & _
Err.Description, vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Function

--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.
 
Yes, I am going to come up with a unique number (somewhat), I will however
split the employee initials and represent them in a series with an auto
gererated id from the field. Like Left([Emp_inits],1) & Mid([Emp_Inits],2,1)
& Right([Emp_Inits],1] and use it in a query (expresion) to generate a uniqe
number for a very small group of users.
 
Yes, I am going to come up with a unique number (somewhat), I will however
split the employee initials and represent them in a series with an auto
gererated id from the field. Like Left([Emp_inits],1) & Mid([Emp_Inits],2,1)
& Right([Emp_Inits],1] and use it in a query (expresion) to generate a uniqe
number for a very small group of users.
Click to expand...

This is called an "Intelligent Key" in the jargon... and unfortunately that's
not a compliment!

Three of the basic principles of database normalization are that 1) each field
should be "atomic", containing only one fact; 2) data should not be stored
redundantly, i.e. you shouldn't store the same fact in two different places;
and 3) a record should depend only on its key - not the key on the record.

This proposed key violates all three of these basic principles!

You'll end up with a number which is just as meaningless to the user as an
arbitrary, sequential or random numeric key would be, but it will still be
dependent on the data. What if one of your users gets married and changes her
last name? Do you change the key... everywhere it's referenced in your
database, everywhere it's printed out, everwhere it's stashed in someone's
mind? Or do you leave it inconsistant with the algorithm that generates it?

Keep it simpler! Just use a Custom Counter to assign sequential numbers to
your users.
 
Back
Top