Recordset and Random Numbers

[Derek Wittman]

Good afternoon,
I am working on including a random number between 0 and 1 in a field in an
already created table. I've only done one recordset in the past, but it
'seems' like a decent application for it.

I want to go down my table line by line and insert, without any parameters,
a random number into field "RandNo". I have the DAO 3.6 references in place
and have my looping figured out. My problem is that I don't recall when to
use the .edit and the .update.

Is there anyone willing to reply with 'aircode' that I can tailor to make
this happen? Here's what I have so far:

Option Explicit
Sub RandomCreator()
dim dbs as DAO.database, rst as DAO.recordset
dim strsql as string

set dbs = CurrentDb
set rst = dbs.openrecordset("tblslotdifficulty", dbOpentable)
With rst
.movelast
.movefirst
While not .EOF
strsql = "Update tblslotdifficulty SET
[tblslotdifficulty].[RandNo] = rnd()"
.movenext
Wend
End With
End Sub

I know that this isn't right. But I am fried as to what is...

Can someone please help? It is very much appreciated! Thank you!

Derek

 

[Tim Ferguson]

With rst
.movelast
.movefirst

Movelast and movefirst not any help in this situation. You do need to check
that rs has any records, but you get that for free in the next line anyway.

While not .EOF
strsql = "Update tblslotdifficulty SET
[tblslotdifficulty].[RandNo] = rnd()"

Hmm: you can do the whole thing without any recordsets using SQL (although
not quite like this); or you can use the recordsets and no SQL.

.movenext
Wend

Wend has really been replaced by Do ... Loop

Try this:

Do While Not rst.EOF
rst.Edit ' prepare buffer for updating
rst!RandNo = Rnd() ' update the field
rst.Update ' send the change to the table

rst.MoveNext ' look at next record

Loop

or else do this for a SQL solution. The catch is that using
SET RandNo = Rnd()
will get the same random number in every record, because Jet is clever
enough to know that you just call the function once (ahem). Therefore, you
have to fool it into thinking that it has to call it once for every record
by giving it a dummy argument.

' see above for strange syntax
strSQL = "UPDATE tblSlotDifficulty" & vbNewLine & _
"SET RandNo = SomeRandomNumber(RandNo);"

' can't use db.Execute because you have to call the expression
' service to see the vba function
DoCmd.RunSQL strSQL

and then somewhere else in the module, you put this function as a wrapper:-

Public Function SomeRandomNumber(Dummy As Variant) As Double
SomeRandomNumber = Rnd()
End Function


Not sure offhand which is quicker in what circumstances. Last time I
benchmarked it, I got similar or contradictory results. Guess it comes down
to programming style and whichever method you feel more comfortable with.

All the best


Tim F

 

[Alex Dybenko]

try:
....
While not .EOF
.edit
!RandNo=rnd()
.update
.movenext
....

 

[Marshall Barton]

...
or else do this for a SQL solution. The catch is that using
SET RandNo = Rnd()
will get the same random number in every record, because Jet is clever
enough to know that you just call the function once (ahem). Therefore, you
have to fool it into thinking that it has to call it once for every record
by giving it a dummy argument.

' see above for strange syntax
strSQL = "UPDATE tblSlotDifficulty" & vbNewLine & _
"SET RandNo = SomeRandomNumber(RandNo);"

' can't use db.Execute because you have to call the expression
' service to see the vba function
DoCmd.RunSQL strSQL

and then somewhere else in the module, you put this function as a wrapper:-

Public Function SomeRandomNumber(Dummy As Variant) As Double
SomeRandomNumber = Rnd()
End Function

Tim, while that's a fully robust solution, you can avoid
creating your extra function and use Execute as long as
there is any field in the table that has positive numbers in
it. In most cases an autonumber field meets this need.

SET RandNo = Rnd(field)

or you could use any numeric field:
SET RandNo = Rnd(0*NZ(field,0)+1)

 

[Derek Wittman]

Good afternoon,
This is to all who helped. Saving the rainforest, er BANDWIDTH, anyway...

Thank you all very much. I was able to get it figured out. I'm not sure
about the .movelast and .movefirst statements. I saw them in a previous
example and have used them now two times.

Here's my final code:

Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strsql As Double, whatever As Variant
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblslotdifficulty", dbOpenTable)
With rst
.MoveLast
.MoveFirst
While Not .EOF
whatever = ![SLSLT PCK DIFC IND]
strsql = Rnd()
If whatever = "B" Then
.Edit
![RandNo] = strsql
.Update
End If
.MoveNext
Wend
End With
Set rst = Nothing
Set dbs = Nothing

I could not think of how to do it with SQL code. And the autonumber won't
work because I really only want to insert the rnd() functions when I have a
particular value in a different field - not all my records. (I have 41000
records).

Now, for my next trick, I need to sort my table according to descending
values in a particular calculated field. Do I need to do this from the table
itself, or can I use a query with calculated fields for my recordset? What
my goal is is to set my sales field as descending (regular + seasonal) and
take the cumulative sales between my X records and come up with a percent of
the total.

I'm not yet asking for ideas - merely getting to work on it.

Thank you again!
Derek

try:
....
While not .EOF
.edit
!RandNo=rnd()
.update
.movenext
....

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

Good afternoon,
I am working on including a random number between 0 and 1 in a field in an
already created table. I've only done one recordset in the past, but it
'seems' like a decent application for it.

I want to go down my table line by line and insert, without any
parameters,
a random number into field "RandNo". I have the DAO 3.6 references in
place
and have my looping figured out. My problem is that I don't recall when
to
use the .edit and the .update.

Is there anyone willing to reply with 'aircode' that I can tailor to make
this happen? Here's what I have so far:

Option Explicit
Sub RandomCreator()
dim dbs as DAO.database, rst as DAO.recordset
dim strsql as string

set dbs = CurrentDb
set rst = dbs.openrecordset("tblslotdifficulty", dbOpentable)
With rst
.movelast
.movefirst
While not .EOF
strsql = "Update tblslotdifficulty SET
[tblslotdifficulty].[RandNo] = rnd()"
.movenext
Wend
End With
End Sub

I know that this isn't right. But I am fried as to what is...

Can someone please help? It is very much appreciated! Thank you!

Derek

 

[Tim Ferguson]

"=?Utf-8?B?RGVyZWsgV2l0dG1hbg==?="

I could not think of how to do it with SQL code.

Marsh and I gave you two SQL versions upthread a bit.

I need to sort my table according to descending
values in a particular calculated field. Do I need to do this from
the table itself,
No

or can I use a query with calculated fields for my
recordset?

Yes -- for "can" read "should"

All the best


Tim F

 

[Tim Ferguson]

you can avoid
creating your extra function and use Execute as long as
there is any field in the table that has positive numbers in
it. In most cases an autonumber field meets this need.

SET RandNo = Rnd(field)

Thanks, Marshall. I should have remembered that Rnd() is a jet-SQL-legal
function

or you could use any numeric field:
SET RandNo = Rnd(0*NZ(field,0)+1)

or a text field

SET RandNo = Rnd(0*Len(TextField) +1)



B Wishes


Tim F

 

[Marshall Barton]

Marshall Barton wrote


Thanks, Marshall. I should have remembered that Rnd() is a jet-SQL-legal
function


or a text field

SET RandNo = Rnd(0*Len(TextField) +1)

Gee Tim, how esoteric can we get with this? ;-)

Neat idea for using a text field, but you might need an NZ
in there ;-)

 

[Derek Wittman]

Thanks, Tim! I'll give it a shot!

Derek

"=?Utf-8?B?RGVyZWsgV2l0dG1hbg==?="




Marsh and I gave you two SQL versions upthread a bit.


Yes -- for "can" read "should"

All the best


Tim F