473,325 Members | 2,608 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Assign randon number to all records in one field

I need to assign a different random number to each record in one
column in one table in access. How can i do this?

Oct 3 '07 #1
2 5561
ARC
That's a strange one!

That's an odd one. Only way I know of to do this would be with code. Say
your column in the table is called RandNumb, then you would need to open the
table recordset (and open another recordset of the same table for searches)
via code, start at the first record, and use a random function in each loop
to generate a random number. Then before you update the line, do a loop
where you search existing values in the RandNumb column, and keep generating
/ searching until it comes up with a unique random number, then write it.
Keep going until the end of recordset:

Sorry in advance, I just wrote this in this window, so didn't have time to
test. And the code's a bit orthodox, as I'm not using a second do / loop for
the random #. Anyway, it's the basic structure, just may need some
de-bugging. Hope this helps.
Dim db as database, rs as recordset, IntRandNum as integer, TopRandNum as
long
set db=currentDb()
set rs =db.openrecordset("<tblname>",db_open_dynaset)
set rs2=db.openrecordset("<tblname>",db_open_snapshot) 'snapshot. rs2
will be search only
TopRandNum=100000 'set the max random number you would want
rs.movefirst
do until rs.eof
IntRandNum = Int((TopRandNumVal * Rnd) + 1)
contin:
rs2.findfirst "RandNumb = " & IntRandNum
if rs2.nomatch then
rs.edit
rs!RandNumb = IntRandNum
rs.update
else
IntRandNum = Int((TopRandNumVal * Rnd) + 1)
goto contin
end if
rs.movenext
loop
rs2.close
rs.close
set db = nothing
<ar****@yahoo.comwrote in message
news:11**********************@50g2000hsm.googlegro ups.com...
>I need to assign a different random number to each record in one
column in one table in access. How can i do this?
Oct 3 '07 #2
You can do it with an update query using the RND() function.
Note: in the example I force it to eval with each record by referencing a
field in the table. You will also need to call Randomize() sometime before
running the query.

SELECT Rnd(IIf([Qty]<>0,1,1)) AS Expr1
FROM tblParts;

<ar****@yahoo.comwrote in message
news:11**********************@50g2000hsm.googlegro ups.com...
I need to assign a different random number to each record in one
column in one table in access. How can i do this?

Oct 3 '07 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Bryan Zash | last post by:
When querying a bit field, I am encountering a problem with MS SQL Server returning a larger number of records for a table than the actual number of records that exist within that table. For...
2
by: news.cablevision.qc.ca | last post by:
Hi! I want, using a form, enter a number and then, read that number of records in a database and store information in variables. The only way I see to do this is to do a FOR...NEXT to the...
14
by: amywolfie | last post by:
Hi All: I know this is simple, but I just can't seem to get there: I need to sort a table by a text field (txtDescription), then assign sequential numbers to the field SEQUENCE in table. ...
2
by: Megan | last post by:
Hi everybody- I have 2 tables, Hearings and Rulings. Both have primary keys called, CaseID, that are autonumbers. I don't want both tables to have the same autonumber. For example, if Hearings...
7
by: astro | last post by:
Anyone have suggestions on where to troubleshoot this error? Background: -Access 2k v. 9.0.6926 sp3 - front and backend on production server (wiindows 2k) -accessed via Citrix -front-end is...
2
by: dskillingstad | last post by:
I'm trying to assign a custom value to a textbox. Here's what I have. I've created a module and "default value" code for a textbox which generates a custom auto-number (yyyy-0000) when a New...
7
by: john | last post by:
I have a form with 1 table. I would like to create a field in my form that shows the number of records (of the whole table) that have one particular value. Is that possible? I fiddled with a...
15
by: Hexman | last post by:
Hello All, How do I limit the number of detail records selected in a Master-Detail set using SQL? I want to select all master records for a date, but only the first 3 records for the details...
3
by: Bjesparz | last post by:
I need to assign a new, sequential, Work Order number inside the WO# field of my Orders table. I also want to to have control over the number assignment using a double click button in the Order form....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.