I have a table with about 20,000 records in it. I have an empty field in the
table that i want to randomly populate with numbers between 1 - 100. I think
I need to use an Update query for this, but I have no idea what expression
should be used.

I'm pretty strong with the wizards and calculated expressions, but I've
never written actual code. Does anyone know how to do this from an Update
query?

Please help!!

On Jan 6, 9:57 am, Florida Ad Guy
<FloridaAd...@discussions.microsoft.com> wrote:
> I have a table with about 20,000 records in it. I have an empty field in the
> table that i want to randomly populate with numbers between 1 - 100. I think
> I need to use an Update query for this, but I have no idea what expression
> should be used.
>
> I'm pretty strong with the wizards and calculated expressions, but I've
> never written actual code. Does anyone know how to do this from an Update
> query?
>
> Please help!!

An update query will update every row to the same random number. I
would do a simple VBA loop:

Sub MakeRandomNumbers()

Dim rs as recordset
set rs = currentdb.openrecordset("MyTableName")

do until rs.eof
rs.edit
rs("TheField") = Int(Rnd()*100)
rs.update
rs.movenext
loop

rs.close

End Sub

This will iterate through the table and drop a number from 1-100 in
your field. Just update the table name and field name.

Hope this helps,
James

On Jan 6, 9:57 am, Florida Ad Guy
<FloridaAd...@discussions.microsoft.com> wrote:
> I have a table with about 20,000 records in it. I have an empty field in the
> table that i want to randomly populate with numbers between 1 - 100. I think
> I need to use an Update query for this, but I have no idea what expression
> should be used.
>
> I'm pretty strong with the wizards and calculated expressions, but I've
> never written actual code. Does anyone know how to do this from an Update
> query?
>
> Please help!!

An update query will update every row to the same random number. I
would do a simple VBA loop:

Sub MakeRandomNumbers()

Dim rs as recordset
set rs = currentdb.openrecordset("MyTableName")

do until rs.eof
rs.edit
rs("TheField") = Int(Rnd()*100)
rs.update
rs.movenext
loop

rs.close

End Sub

This will iterate through the table and drop a number from 1-100 in
your field. Just update the table name and field name.

Hope this helps,
James