Randomly fill in a field

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

Table1 has 11,000+ records. Table2 has 1 date field and contains 100 records. I
added a new field to Table1. How can I randomly populate the new field in Table1
with dates from Table2 so that for every record in Table1, the new field in
Table1 has one of the dates in Table2?

Thanks!

Tom
 
Tom said:
Table1 has 11,000+ records. Table2 has 1 date field and contains 100 records. I
added a new field to Table1. How can I randomly populate the new field in Table1
with dates from Table2 so that for every record in Table1, the new field in
Table1 has one of the dates in Table2?

Thanks!

Tom
Click to expand...

Untested code but close enough to get you started.

Sub FillDates
Dim rst1 As Recordset
Dim rst2 As Recordset
Dim lngCnt As Long
Dim lngOffSet As Long

Set rst1 = Currentdb.OpenRecordset("Table1,dbopensdynaset)
Set rst2 = Currentdb.OpenRecordset("Table2,dbopenssnapshot)
rst2.movelast
lngCnt = rst2.RecordCount

Randomize
rst1.MoveFirst
Do While Not rst1.EOF
'create random number between 1 and rec cnt of table2
lngOffSet = Int(lngCnt * Rnd + 1)
rst2.MoveFirst
rst2.Move lngOffSet -1
rst1.Edit
rst1!DateField = rst2!DateField
rst1!Update
rst1.MoveNext
Loop
msgbox "Done"
End Sub
 
Back
Top