Random Number Format

[GavZ]

Hi - I want to create a 9 digit random number that will NOT duplicate with each record. I also need the number to have the format of 000-000-000

I want it to always have 9 digits and always positive.

I cant do it - hope someone can!

Thanks in advance
Gav

 

[bigstav]

Random Number generator ?

Hi Gav, try this :-


Public Function GenRand()

Dim strRandNo As String

strRandNo = CStr((Int((999 - 100 + 1) * Rnd + 100))) + "-" + CStr((Int((999 - 100 + 1) * Rnd + 100))) + "-" + CStr((Int((999 - 100 + 1) * Rnd + 100)))

MsgBox strRandNo

End Function

 

[GavZ]

Thanks for this - it produces the random number.

I have set it in the Table to not accept duplicates but how do i get it to NOT show an error when it does duplicate - i just want it to produce another random number that is not a duplicate.

Hope this Makes sense!

 

[bigstav]

Randoms ....

Not sure how you're getting duplicates. I have just run this code to generate 100,000 records, and there are no duplicates - how many are you using ? ! ...


Public Sub Insert()

Dim strRandNo As String
Dim Conn As ADODB.Connection
Dim strSQL As String

Set Conn = CurrentProject.Connection

Dim n As Long
n = 0

Do
If n = 100000 Then
MsgBox "DONE"
Exit Sub
End If
strRandNo = CStr((Int((999 - 100 + 1) * Rnd + 100))) + "-" + CStr((Int((999 - 100 + 1) * Rnd + 100))) + "-" + CStr((Int((999 - 100 + 1) * Rnd + 100)))
strSQL = "INSERT INTO [Nos] VALUES ('" & strRandNo & "')"
'Debug.Print strSQL
Conn.Execute strSQL
n = n + 1
Loop

End Sub

 

[Moniker]

To prevent the error, do a DLookup on the the table, looking for your number first. If DLookup returns zero (no matches), then write the number to the table. If DLookup returns a one, it's found a match, so rerun the randomize routine, check for a duplicate again, etc.

Quick rundown:

1) Create random number.
2) Does this random number already exist in my table? (Use DLookup to check.)
2a) Yes? Go to step 1.
2b) No? Write new number to table.
3) Rinse and repeat.

 

[WayneRyan]

Moniker,

DCount ???

Wayne

 

[bigstav]

Update

Moniker is right, DLookup() will check for duplicates b4 insert, code to test will now look something like this ...



Public Sub Insert()

Dim strRandNo As String
Dim Conn As ADODB.Connection
Dim strSQL As String
Dim Check

Set Conn = CurrentProject.Connection

Dim n As Long
n = 0

Do
If n = 100 Then
MsgBox "Finished"
Exit Sub
End If

TryAgain:
strRandNo = CStr((Int((999 - 100 + 1) * Rnd + 100))) + "-" + CStr((Int((999 - 100 + 1) * Rnd + 100))) + "-" + CStr((Int((999 - 100 + 1) * Rnd + 100)))
Check = DLookup("[FieldName]", "TableName", "[FieldName] = '" & [strRandNo] & "'")
If IsNull(Check) Then 'Created No does NOT already exist.
strSQL = "INSERT INTO [TableName] VALUES ('" & strRandNo & "')"
Conn.Execute strSQL
Else 'Created No DOES exist, so don't try insert.
GoTo TryAgain:
End If

n = n + 1

Loop

End Sub