stephenlecomptejr
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.
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.
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
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
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...
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Line 12 should be
If sLOB <> "HI" And sLOB <> "HI" Then
>>>
If sLOB <> "HI" And sLOB <> "CI" Then
If sLOB <> "HI" And sLOB <> "HI" Then
>>>
If sLOB <> "HI" And sLOB <> "CI" Then
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.
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
ASKER
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
ASKER
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...
and you trying to get this, 2,345,678,999 !!!!
it will fail...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
you should use Double to assign the return value of the function!29201957.xlsm
ASKER
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...
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
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.
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!
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!
ASKER
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.
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.
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.
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.
is it bigger than this?