I'm trying to update a table in Microsoft Access with the following SQL:

UPDATE tblLastLDAP, tblGenex1 SET tblGenex1.SURROGATEID = Left([tblLastLDAP]![Last],6) & Int((99-1+1)*Rnd()+1);

I want the records to each be set to a number between 1 and 99 (+ the beginning info), but the result of the SQL above sets all of the records to the same number.

How do I tell Access to process each record separately?

Thanks!

Try:

Left([tblLastLDAP]![Last],6) & Int((99-*Rnd())+1);

This still updates all records at once, rather than updating them individually.

Hmm, Ok, the next step is to use a custom function so you can initialize the number. This works, I tested it:

Public Function NewNum(strLast As String) As String
Randomize Len(strLast) ' Initialize random-number generator.
NewNum = Left(strLast, 6) & Int((99 * Rnd) + 1)
End Function

In your Update query use:

=NewNum([Last])