Unique Random number query with todays date..

[CJSwed]

Hi.. does anyone see anything obvious here?
I am trying to write a unique random number that ends with the date, so it just goes down the item code column of a table . The one problem, it writes just one code for the first record in the table and then stops.

INSERT INTO [tablename] ( [itemcode] )
SELECT Chr(Int((90-65+1)*Rnd()+65)) & Chr(Int((90-65+1)*Rnd()+65)) & CStr((Int((9-0+1)*Rnd()+0))) & "-" & Chr(Int((90-65+1)*Rnd()+65)) & Chr(Int((90-65+1)*Rnd()+65)) & CStr((Int((9-0+1)*Rnd()+0))) & "-" & CStr(Format(Date(),'yyyymmdd')) AS Expr1;

Am I missing something obvious? Its been a long day....

http://www.tek-tips.com/

Thanks,
Chris

 

[MajP]

INSERT INTO [tablename] ( [itemcode] )
SELECT Chr(Int((90-65+1)*Rnd()+65)) & Chr(Int((90-65+1)*Rnd()+65)) & CStr((Int((9-0+1)*Rnd()+0))) & "-" & Chr(Int((90-65+1)*Rnd()+65)) & Chr(Int((90-65+1)*Rnd()+65)) & CStr((Int((9-0+1)*Rnd()+0))) & "-" & CStr(Format(Date(),'yyyymmdd')) AS Expr1;

Huh? I do not get this. I would think
Chr(Int((90-65+1)*Rnd()+65))
is simply
chr(int(26 * rnd + 65))
And
CStr((Int((9-0+1)*Rnd()+0)))
Just simply
int(10 * rnd())

BTW you do not need to convert strings to strings and do not need to convert anything to strings when you conactenate.

When the rnd function is run in a query (or any function) it is ran once unless it is parameterized. You need to seed it with a unique value per record. This is normally done by seeding it with the PK of the record if numeric. However, no idea what you are talking about because that could at best produce a single record. Not sure how you expect to get multiple records.

Not that you have to, but that is a mess. I would build a UDF to return your unique ID. Likely still have to seed it uniquely per record.

 

[MajP]

Function

Public Function GetRandomID(Optional Seed As Long = 1) As String
 GetRandomID = Chr(Int(26 * Rnd(Seed) + 65)) & Chr(Int(26 * Rnd(Seed) + 65)) & Int(10 * Rnd(Seed)) & "-" & Chr(Int(26 * Rnd(Seed) + 65)) & Chr(Int(26 * Rnd(Seed) + 65)) & Int(10 * Rnd(Seed)) & "-" & Format(Date, "yyyymmdd")
End Function

Sql

SELECT Orders.OrderID, getRandomID([OrderID]) AS RandID
FROM Orders;

results

Order ID	RandID
10248	OT2-PM5-20131204
10249	JI2-LV3-20131204
10250	CR6-UL0-20131204
10251	LU5-NV5-20131204
10252	WJ1-DS1-20131204
10253	GC4-JQ8-20131204
10254	OZ9-OL6-20131204
10255	QF8-JO0-20131204
10256	DV4-UM0-20131204
10257	GA2-JN8-20131204
10258	BS6-GM7-20131204
10259	VX4-GI7-20131204
10260	WE4-IE6-20131204
10261	AT5-WX6-20131204
10262	YE2-WZ7-20131204
10263	BI2-AC7-20131204

In the above example if I do not provide a unique seed for each record, then each random ID will be the same.

 

[CJSwed]

Thanks bud!

I wish I had seen this earlier.... I ended up putting this in a module, so far it seems to be doing ok.

Dim db As DAO.Database
Dim recIn As DAO.Recordset
Dim recIn2 As DAO.Recordset
Dim recIn3 As DAO.Recordset
Dim v_filepath As String
Dim strLastField1 As String
Dim strLastField2 As String
Dim strLastField3 As String
Dim lngRecordsDeleted As Long
lngRecordsDeleted = 0

DoCmd.SetWarnings False

Dim strList As String

Set db = CurrentDb()
Set recIn2 = db.OpenRecordset("test")
Do While Not recIn2.EOF
v_dispcode = Chr(Int((90 - 65 + 1) * Rnd + 65)) & Chr(Int((90 - 65 + 1) * Rnd + 65)) & Chr(Int((90 - 65 + 1) * Rnd + 65)) & CStr((Int((9 - 0 + 1) * Rnd + 0))) & "-" & Chr(Int((90 - 65 + 1) * Rnd + 65)) & Chr(Int((90 - 65 + 1) * Rnd + 65)) & CStr((Int((9 - 0 + 1) * Rnd + 0))) & "-" & CStr(Format(Date, "yyyymmdd"))
recIn2.Edit
recIn2![display-code] = v_dispcode
recIn2.Update
recIn2.MoveNext
Loop
End Sub

 

[MajP]

Still do not understand the 90-65+1 or or the 9-0+1, I think it is a random letter and a random digit. Here is some properly written code that makes sense

Public Function GetRandomID(Optional Seed As Long = 1) As String
 GetRandomID = GetRandomLetter(Seed) & GetRandomLetter(Seed) & GetRandomDigit(Seed) & "-" & GetRandomLetter(Seed) & GetRandomLetter(Seed) & GetRandomDigit(Seed) & "-" & Format(Date, "yyyymmdd")
End Function

Public Function GetRandomLetter(Optional Seed As Long = 1) As String
  GetRandomLetter = Chr(Int(26 * Rnd(Seed) + 65))
End Function

Public Function GetRandomDigit(Optional Seed As Long = 1) As Integer
  GetRandomDigit = Int(10 * Rnd(Seed))
End Function

So you can call that from any update or insert query. If you use a recordset to insert then you should not have to provide a see. You code would simply be

Set recIn2 = currentdb.OpenRecordset("test")
Do While Not recIn2.EOF
  recIn2.Edit
  recIn2![display-code] = getRandomID()
  recIn2.Update
  recIn2.MoveNext
Loop

A little more readable?