Loop through table and update field from null to random number

I now have, thanks to EE responses, a function that creates a random number.  Now, I have a table with 13,000 or so record and what I need is for the random function to popluate one field in the table for each of the records with a random number.  I tried an update query, but the process returned the same number.  I assume because it was not looping.  I think I need a recordset, but not sure how to approach this issue.
Public Function RandAccount() As Double
        RandAccount = Format(Int(CDec((10000000000# * Rnd) + 1)), "0000000000")
End Function

Open in new window

Sandra SmithRetiredAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

The query optimiser is seeing your call to RandAccount() and figuring it needs to be called only once.

You need to trick it into calling it for every record.  You can do this by passing one of the query fields as an argument:

Public Function RandAccount(Optional dummy as Variant) As Double

Then, in your query:

     RandAccount( [some field] )


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sandra SmithRetiredAuthor Commented:
Yep, I needed to have it call for each row and that was where I was stymied.  Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.