I need to generate a unique serial number for each field in the table. The serial number shall consist of two letters and six numbers, for example AA123456. Someone can suggest how to do it in MS Access
12 8246
Can you provide any more information? You say each field in the table is that correct or did you mean each row / record? Is there a specific reason you have to have it in the described format of "two letters and 6 numbers"? Just curious as that sounds very strange. I see you have a lot of views with zero replies. It may be better if there was more information to work with to better understand what you are trying to accomplish. Take Care & God Bless ~ Stephen
Thanks Stephen! There is table with information about cars and its owners and some other documents, all this information will be writen on the special list (one row for one car and its owner), I need a unique serial number for each row in the table. Two letters and 6 numbers is a standart for this documents. Valid is a separate generator for letters and numbers, in diferent fields. I hope someone will help me ;)
One more question: When you say "Valid is a seperate generator for letters and numbers, in different fields" are you saying that the value has to be in two seperate fields or that it can be in two seperate fields?
NeoPa 32,556
Expert Mod 16PB
Ivan, You need to explain your question more clearly (as Stephen says). At the moment, we have no information to use to determine which two letters should be used for any particular record. Without this it will be impossible to provide a workable solution.
More questions for you. - You say the value must be unique (and I'll assume you are referring to unique per record rather than per field). Does the number part need to be unique?
- Does the number part need to be a value one more than the last value used, that matches the same two letters? EG. If you needed a value for a "CD" record and the last one used was "CD220431", would the next one need to be "CD220432"?
With the limited information provided the following function should be easily modified to achieve the desired results regardless of wether or not you keep the values in two seperate fields or one single field: -
Option Compare Database
-
Option Explicit
-
-
Public Function GenerateUniqueSerialNumber() As String
-
-
On Error GoTo GenerateUniqueSerialNumber_Error
-
-
Dim lngREC As Long
-
Dim strSQL As String
-
Dim strALPHA As String
-
Dim lngNUMERIC As Long
-
Dim daoDBS As DAO.Database
-
Dim daoREC As DAO.Recordset
-
Dim rayALPHA(1, 25) As String
-
-
Set daoDBS = CodeDb
-
-
strSQL = _
-
"SELECT " & vbCrLf & _
-
"[USN_ALPHA] AS [ALPHA], " & vbCrLf & _
-
"MAX([USN_NUMERIC]) AS [NUMERIC] " & vbCrLf & _
-
"FROM MyTable " & vbCrLf & _
-
"WHERE [USN_ALPHA] = (" & vbCrLf & _
-
"SELECT MAX([USN_ALPHA]) " & vbCrLf & _
-
"FROM MyTable) " & vbCrLf & _
-
"GROUP BY [USN_ALPHA];"
-
-
Set daoREC = daoDBS.OpenRecordset(strSQL)
-
-
If Not (daoREC.BOF And daoREC.EOF) Then
-
strALPHA = daoREC("ALPHA").Value
-
lngNUMERIC = daoREC("NUMERIC").Value
-
End If
-
-
daoREC.Close
-
daoDBS.Close
-
-
rayALPHA(0, 0) = "1"
-
rayALPHA(1, 0) = "A"
-
rayALPHA(0, 1) = "2"
-
rayALPHA(1, 1) = "B"
-
rayALPHA(0, 2) = "3"
-
rayALPHA(1, 2) = "C"
-
rayALPHA(0, 3) = "4"
-
rayALPHA(1, 3) = "D"
-
rayALPHA(0, 4) = "5"
-
rayALPHA(1, 4) = "E"
-
rayALPHA(0, 5) = "6"
-
rayALPHA(1, 5) = "F"
-
rayALPHA(0, 6) = "7"
-
rayALPHA(1, 6) = "G"
-
rayALPHA(0, 7) = "8"
-
rayALPHA(1, 7) = "H"
-
rayALPHA(0, 8) = "9"
-
rayALPHA(1, 8) = "I"
-
rayALPHA(0, 9) = "10"
-
rayALPHA(1, 9) = "J"
-
rayALPHA(0, 10) = "11"
-
rayALPHA(1, 10) = "K"
-
rayALPHA(0, 11) = "12"
-
rayALPHA(1, 11) = "L"
-
rayALPHA(0, 12) = "13"
-
rayALPHA(1, 12) = "M"
-
rayALPHA(0, 13) = "14"
-
rayALPHA(1, 13) = "N"
-
rayALPHA(0, 14) = "15"
-
rayALPHA(1, 14) = "O"
-
rayALPHA(0, 15) = "16"
-
rayALPHA(1, 15) = "P"
-
rayALPHA(0, 16) = "17"
-
rayALPHA(1, 16) = "Q"
-
rayALPHA(0, 17) = "18"
-
rayALPHA(1, 17) = "R"
-
rayALPHA(0, 18) = "19"
-
rayALPHA(1, 18) = "S"
-
rayALPHA(0, 19) = "20"
-
rayALPHA(1, 19) = "T"
-
rayALPHA(0, 20) = "21"
-
rayALPHA(1, 20) = "U"
-
rayALPHA(0, 21) = "22"
-
rayALPHA(1, 21) = "V"
-
rayALPHA(0, 22) = "23"
-
rayALPHA(1, 22) = "W"
-
rayALPHA(0, 23) = "24"
-
rayALPHA(1, 23) = "X"
-
rayALPHA(0, 24) = "25"
-
rayALPHA(1, 24) = "Y"
-
rayALPHA(0, 25) = "26"
-
rayALPHA(1, 25) = "Z"
-
-
If lngNUMERIC = 999999 Then
-
lngNUMERIC = 111111
-
If Right(strALPHA, 1) = "Z" Then
-
If Left(strALPHA, 1) = "Z" Then
-
MsgBox _
-
"Error: You have already used up all " & vbCrLf & _
-
"possible alpha numeric combinations.", vbCritical, "All Used Up!"
-
Else
-
For lngREC = 0 To 25
-
If Left(strALPHA, 1) = rayALPHA(1, lngREC) Then
-
strALPHA = rayALPHA(1, lngREC + 1) & "A"
-
Exit For
-
End If
-
Next
-
End If
-
Else
-
For lngREC = 0 To 25
-
If Right(strALPHA, 1) = rayALPHA(1, lngREC) Then
-
strALPHA = Left(strALPHA, 1) & rayALPHA(1, lngREC + 1)
-
Exit For
-
End If
-
Next
-
End If
-
Else
-
lngNUMERIC = lngNUMERIC + 1
-
End If
-
-
GenerateUniqueSerialNumber = strALPHA & CStr(lngNUMERIC)
-
-
GenerateUniqueSerialNumber_Exit:
-
strSQL = ""
-
strALPHA = ""
-
lngREC = Empty
-
lngNUMERIC = Empty
-
Set daoREC = Nothing
-
Set daoDBS = Nothing
-
Exit Function
-
-
GenerateUniqueSerialNumber_Error:
-
MsgBox "Error " & Err.Number & vbCrLf & Err.Description, "ERROR:"
-
Err.Clear
-
Resume GenerateUniqueSerialNumber_Exit
-
-
End Function
-
I'm still slightly confused, but if you are looking for a Unique Serial Number in the Format of (AA123456) for each Record in a Table, then the following Function will do the trick. I'll post the Code along with 25 Sample Runs. There will be 676,000,000 possible combinations (26*26*10*10*10*10*10*10), so the chance of duplication is extremely small to say the least. If you also wish to check for Duplication within the Function, this will require a simple adjustment. - Public Function fGenerateUniqueSerialNum()
-
Dim intPosCtr As Byte
-
Dim strBuild As String
-
Randomize
-
-
For intPosCtr = 1 To 8 '2 Alphas/6 Numeric
-
If intPosCtr < 3 Then 'Alpha positions 1 and 2
-
strBuild = strBuild & Chr$(Int((26) * Rnd + 65)) 'A to Z
-
Else 'Numeric positions 3 to 8
-
strBuild = strBuild & CStr(Int(Rnd * 10))
-
End If
-
Next
-
-
fGenerateUniqueSerialNum = strBuild
-
End Function
Sample OUTPUT (25 Runs): - FK089124
-
ZG410697
-
VP074019
-
YK787230
-
TC983631
-
GP914590
-
AJ778185
-
UF109668
-
JF468179
-
TM053270
-
WH766492
-
VX607518
-
YR490893
-
VZ319172
-
FB768009
-
BM253267
-
II180499
-
RU807448
-
PS459616
-
KI714160
-
CW231591
-
DA398771
-
ND671751
-
LB223929
-
DC336604
To NeoPa:
Serial number should be unique per record.
The number part need to be a value one more than the last value used.
For example, the first one "AA000001", second "AA000002" .... then "AA999999"..... then "AB000001"......
NeoPa 32,556
Expert Mod 16PB Ivan Popov:
For example, the first one "AA000001", second "AA000002" .... then "AA999999"..... then "AB000001"......
This example certainly clarifies what you are after.
You would then need code somewhat similar to this : - Private Function GetNext() As String
-
Dim strMax As String
-
Dim lngNum As Long
-
-
strMax = Nz(DMax("[SerNoField]", "[YourTable]"), "AA000000")
-
lngNum = CLng(Mid(strMax, 3))
-
If lngNum > 999998 Then lngNum = 0
-
GetNext = Left(strNext, 2) & Format(lngNum + 1, "000000")
-
End Function
NeoPa 32,556
Expert Mod 16PB
I left an important bit out of the last post. It should have read : - Private Function GetNext() As String
-
Dim strMax As String
-
Dim lngNum As Long
-
Dim intOne As Integer, intTwo As Integer
-
-
strMax = Nz(DMax("[SerNoField]", "[YourTable]"), "AA000000")
-
lngNum = CLng(Mid(strMax, 3))
-
intOne = Asc(strMax)
-
intTwo = Asc(Mid(strMax, 2))
-
If lngNum > 999998 Then
-
lngNum = 0
-
If intTwo > Asc("Y") Then
-
intTwo = Asc("A") - 1
-
intOne = intOne + 1
-
End If
-
intTwo = intTwo + 1
-
End If
-
lngNum = lngNum + 1
-
GetNext = Chr(intOne) & Chr(intTwo) & Format(lngNum, "000000")
-
End Function
Just one question about the first 2 letters on your serial number. Do they increment as the serial reaching 999999? looks like a very long time to get to AB to me as a car dealer. Or is there actually some criterias that these letters will increment instead of waiting the 999999 of AA?
All numbers must be consistent.
NeoPa 32,556
Expert Mod 16PB
Ivan, have you looked at post #10? You haven't responded to it.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: tom |
last post by:
Hi,
How do I get the serial number of the harddisk in .NET?
I want this to be the same number even if the user has
reformatted, so I do not want the volume serial number.
Thanx,
t
|
by: |
last post by:
Hi,
Do memory sticks have serial numbers like harddrives? If so how can I get
this, I want to uniquely identify a memory stick (removable drive) for
authentication.
Thanks
|
by: Lauren Wilson |
last post by:
Does anyone know how the following info is extracted from the user's
computer by a Front Page form?
HTTP User Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
rv:1.7.5) Gecko/20041107...
|
by: Paul |
last post by:
Hi Guys,
I have written an application in Visual C#, I wish to add serial number
validation dll to the installer, how do I go about doing this.
Thanks in advance.
Paul Mathebula
|
by: Paul Bromley |
last post by:
Ok - I have given up on trying to find the active IP address for a given PC.
For licensing purposes I need to retrive a unique identifier from the PC
that the program is installed on. The Hard disk...
|
by: Jassim Rahma |
last post by:
my CPU mac address is BFEBFBFF000006F6
and my software name is : shefa
is there any function or encryption way in C# to generate a 24 digits (alpha
numeric) from both CPu MAC address and...
|
by: malteselemon |
last post by:
Using Access 2003, I need to create an automatic Serial Number.
Example "7235-E001"
"7" is current year
"235" is day
"-E" is static
"001" is sequencial, restarting at "001" each day
I set a...
|
by: Lit |
last post by:
Hi,
How can I get the CPU serial number via C#
How can I get a screen shot via C#
Thank you,
Lit
|
by: swamimeenu |
last post by:
Hi,
I need to add a automatic serial number in datareport in vb without having tht field in database..
but the remaining fields are connected from db...
how to generate automatic serial number...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |