Link to home
Start Free TrialLog in
Avatar of stephenlecomptejr
stephenlecomptejrFlag for United States of America

asked on

Is it possible to have a unique numeric identifier with leading zeroes in Microsoft Access?

With the following coding I keep getting an overflow error at line:
lNewValue = CDbl(sNewValue) 

Basically I do a Debug.Print UniqueIDNumber("CI", "023456789")

Anyway to overcome this?  Trying to make a unique identifier that is numeric only.
Got an idea to replace all leading zeroes with a 9.  The unique identifier is just to run a report.


User generated image

User generated image
Public Function UniqueIDNumber(sLOB As String, sPID As String) As Long

  Dim sNewValue As String
  Dim lNewValue As Long
  '
  If sLOB = "CI" Then
    sNewValue = sPID & "99"
  End If
  If sLOB = "HI" Then
    sNewValue = sPID & "88"
  End If
  If sLOB <> "HI" And sLOB <> "HI" Then
    sNewValue = sPID & "77"
  End If
  sNewValue = Replace(sNewValue, " ", "")
  lNewValue = CDbl(sNewValue)
  UniqueIDNumber = lNewValue

End Function

Open in new window

Avatar of HainKurt
HainKurt
Flag of Canada image

Long Integer — For integers that range from -2,147,483,648 to +2,147,483,647

is it bigger than this? 
Avatar of Dale Fye
Personally, I would just use an autonumber field and then append 0's to the front of it, something like:

UniqueID: Right("00000000" & yourTable.ID, 8)

This would display as a string but if it is just for a report, or even for a report to display (no edits), this should work.

Dale
whats the value of sNewValue when you get that error?
put sNewValue  into add/watch window
put a breakpoint
and check the value before getting that error
and see when do you get that error...
SOLUTION
Avatar of Flyster
Flyster
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Line 12 should be

 If sLOB <> "HI" And sLOB <> "HI" Then 
>>>
 If sLOB <> "HI" And sLOB <> "CI" Then 
Avatar of stephenlecomptejr

ASKER

correct on that last comment, HainKurt

Flyster,

line should be:  
lNewValue = Val(sNewValue) instead of Cdbl... but no matter using Cdbl or Val I get the same overflow error cause of the leading zeroes.
what happens if you just send as string

Public Function UniqueIDNumber(sLOB As String, sPID As String) As String
  Dim sNewValue As String

  If sLOB = "CI" Then
    sNewValue = sPID & "99"
  End If

  If sLOB = "HI" Then
    sNewValue = sPID & "88"
  End If

  If sLOB <> "HI" And sLOB <> "CI" Then
    sNewValue = sPID & "77"
  End If

  UniqueIDNumber = Replace(sNewValue, " ", "")
End Function

Open in new window

Here is the updated code and an image of the error and values asked for:


Public Function UniqueIDNumber(sLOB As String, sPID As String) As Long

  Dim sNewValue As String
  Dim lNewValue As Long
  '
  If sLOB = "CI" Then
    sNewValue = sPID & "99"
  End If
  If sLOB = "HI" Then
    sNewValue = sPID & "88"
  End If
  If sLOB <> "HI" And sLOB <> "CI" Then
    sNewValue = sPID & "77"
  End If
  sNewValue = Replace(sNewValue, " ", "")
  lNewValue = Val(sNewValue)
  UniqueIDNumber = lNewValue

End Function

Open in new window

If I use a string, it works but it has to be a number.
max value for Long is 2,147,483,647

and you trying to get this, 2,345,678,999 !!!!

it will fail...
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Double throws the same Overflow error.
Double throws the same Overflow error

no it does not!
probably you get the double value and assign to a long value!
Sub test()
  Dim uid As Double
  uid = UniqueIDNumber("CI", "023456789")
End Sub

Open in new window

you should use Double to assign the return value of the function!
29201957.xlsm
You were right on that last one.  However it does not display a zero in the front but in this case doesn't matter.  Technically the answer to the above question is yes if a string but not as any numeric type

User generated image.  
integer numbers does not have 0 in front, but strings may have...
if you want integer, do not expect 0 in front
if you need 0 then forget about long/integer/double but use string...
Stephen,
Just us a simple autonumber field.  Then, when you display the information on your form, create a query and in the query use the syntax I provided to display that ID (long integer) with leading zeros (as a string).  You won't be able to edit this field, but you shouldn't need to, and you won't have to jump through hoops as you have above simply to display a number with leading zeros.

Dale
You should always STORE your information as a unique NUMBER.

However, when it comes to DISPLAYING the information we can set the format property (Sorry Dale, I disagree with changing the value by turning it into a string)

If we set the FORMAT property to "00000" then 22 would be DISPLAYED as 00022 but still stored as 22.

That way, we don't get confused as to whether 00022 is the same is 0022 or 022 or 22. The VALUE is always 22, but the display can have as many zeros as you want. 
For something that should be so simple, why is it proving to be so hard?

Usually because everyone is missing some key info about the problem that the author hasn't mentioned yet.
The parameter coming  into the function is a string - let's start there.  Do we have any control over how/where this number comes from?  If not, let's start at the beginning of the function when we get sPID as a string.
Now, what are the requirements?  What is all this talk about a number that needs to be a string (or a string that needs to be a number)?  Let's get that straight first.  THINK!  What is the difference between a number and a string?  (When it comes to coding, you'd be surprised at how many people don't really know......)
Do we need to be appending any additional numbers?  If so, why and what are we trying to end up with.
Also, please work within the confines of your data type.  Overflow errors are just plain innocent ignorance.
Don't make this more complicated than it needs to be.  This is so simple, experienced coders can do it without even thinking about it.

So many possible solutions.... let's pick one!
We currently have a UniqueID column number (long) data type.   This column cannot be an autonumber field because we want control as to what the number should be.   Almost all of the UniqueID numbers are social security numbers.   Along with the social number we want to identify certain groupings that social number belongs to - thus the sLOB string is the particular group where we add an extension where it still creates a unique identifier.  I think we are realizing in the process that the UniqueID is automatically cutting off the zeroes in the front due to it not being the string.

We are not wanting to change the UniqueID field to string since this would affect a lot of databases but may have to.  This function above is run as an update to a field in a table but we get an error that says 'updatable query not valid'.

I guess another question would be if social security is a valid unique identifier - then what's the best data type should be used in this case.   You are all giving me different options but autonumber is not one of them, Dale.
"Almost all of the UniqueID numbers are social security numbers "

My advice is NEVER store a SSN.  If you have no choice, then NEVER STORE it unencrypted!
And only allow it to be viewed by a highly select set of users.
Keep in mind that SSN's are considered one of the sacred pieces of "personal identification" that people are told to guard against giving out, but so many places depend on for personal identification.
However, seeing as to how you already have it and are using it, it's your business.
Let's assume you want to continue using the SSN as a base for your unique identifier, and you want to have a unique identifier that is a string, but has only digits in it (all numbers) (it must be a string or you can't have leading zeros - no if, ands, or butts....)  You say your SSN field is a number field, so any SSNs with leading zeros are missing them.
Converting your sLOB to two-digit numbers that you append to the right-end of your sPID is one way to do it.  It's up to you how you manage and use that.
You mentioned that you can't really change anything about the field you are using as a unique identifier because it is used as-is in other places, so perhaps a new field used strictly for this special purpose is in order.
You will need to create a query to create your specially formatted unique id, or add a string field to your table where you enter the value you want to use as a unique id.

To create your formatted string unique id, you'll need to take your SSN, append the numeric LOB to the end, then format the resulting number as a string with leading zeros.  You can do this in a query which you use in your processing, or update a new field in a table with this new value.  Here is an example of a formula that should do it:
=Format([SSN] & [LOB],"000000000000")
It assumes that [SSN] is a numeric value (i.e. 1234567890), that [LOB] is your 2-digit LOB (i.e. 88), and that you want a 12-character string with leading zeros.  It should produce results like:
"012345678988"
This can be in a query column, or used to update a field in a table.