Update Query with Rnd (random numbers) question

[The Boondock Saint]

Hello Everyone,

Im trying to make a query which will allow me to run a query update so that
all my listings will get a new random number generated for them,

Ive got it selecting any listing with a ID of >0 (so basically thats all of
the listings)

What id like it to do is then assign a random number to the randsort field,
(prefer a number like 0.812 or 0.342 etc etc)

To start with ive tried using the Rnd feature, but its updating all the
listings with exactly the same number (though granted the number changes
each time the query is run)

I see in some of the other comments regarding rnd that you may need to put a
randomize statement somewhere,

Im very new to programming in access and was just wondering am I on the
right track, and if so where abouts should i declare the randomize code
since its in a query?

Thanks for your time and look forward to your replies

Cheers Saint
MSACCESS 2003

 

[tina]

to return a random number to each record in a query, paste the following
function into a public module, as

Public Function isRandomNum(ByVal X As String) As Single

' the x variable is included to force a call to this function on every
' record in the query. it is NOT used within the function itself.

Randomize
isRandomNum = Rnd

End Function

you can call the function in the query's OrderBy statement, as

....ORDER BY isRandomNum([PrimaryKeyField])

replace PrimaryKeyField with the correct name of the table's primary key
field, of course. actually, you can probably use any field in the table; i
just make a habit of referencing the PK field whenever the value of the
referenced field is not important to whatever i'm doing at the time.

(prefer a number like 0.812 or 0.342 etc etc)

that, i can't tell you how to do. but since you're using the random numbers
to sort the query records, why does it matter what the numbers actually are?

hth

 

[The Boondock Saint]

Thanks for your reply Tina,

I put the code into a module, and then went into the query itself, and put
in the UPDATE TO section of the field i want to update and made it
isRandomNum([PrimaryKeyField]) with the PKF as link_id ,which is my
PFK...

I then went to run it, but its coming up with a compile error... Expected
variable or procedure, not module

Was I ment to put a number or something in the place of the X? Im very new
to this kind of coding, so any help would be awesome..

Cheers Saint

to return a random number to each record in a query, paste the following
function into a public module, as

Public Function isRandomNum(ByVal X As String) As Single

' the x variable is included to force a call to this function on every
' record in the query. it is NOT used within the function itself.

Randomize
isRandomNum = Rnd

End Function

you can call the function in the query's OrderBy statement, as

...ORDER BY isRandomNum([PrimaryKeyField])

replace PrimaryKeyField with the correct name of the table's primary key
field, of course. actually, you can probably use any field in the table; i
just make a habit of referencing the PK field whenever the value of the
referenced field is not important to whatever i'm doing at the time.

(prefer a number like 0.812 or 0.342 etc etc)

that, i can't tell you how to do. but since you're using the random numbers
to sort the query records, why does it matter what the numbers actually are?

hth

Hello Everyone,

Im trying to make a query which will allow me to run a query update so that
all my listings will get a new random number generated for them,

Ive got it selecting any listing with a ID of >0 (so basically thats all of
the listings)

What id like it to do is then assign a random number to the randsort field,
(prefer a number like 0.812 or 0.342 etc etc)

To start with ive tried using the Rnd feature, but its updating all the
listings with exactly the same number (though granted the number changes
each time the query is run)

I see in some of the other comments regarding rnd that you may need to

put

a
randomize statement somewhere,

Im very new to programming in access and was just wondering am I on the
right track, and if so where abouts should i declare the randomize code
since its in a query?

Thanks for your time and look forward to your replies

Cheers Saint
MSACCESS 2003

 

[tina]

you can't name the public module the same name as the procedure; if that's
what you did, then change the module name.

also, since you're using this in an Update, change the syntax slightly, to

isRandomNum([TableName].[PrimaryKeyField])

replace TableName with the correct name of the table, of course. also, note
that the function returns a Single data type. check in table Design view to
make sure that the field being updated has a data type of Single or Double.

hth

Thanks for your reply Tina,

I put the code into a module, and then went into the query itself, and put
in the UPDATE TO section of the field i want to update and made it
isRandomNum([PrimaryKeyField]) with the PKF as link_id ,which is my
PFK...

I then went to run it, but its coming up with a compile error... Expected
variable or procedure, not module

Was I ment to put a number or something in the place of the X? Im very new
to this kind of coding, so any help would be awesome..

Cheers Saint

to return a random number to each record in a query, paste the following
function into a public module, as

Public Function isRandomNum(ByVal X As String) As Single

' the x variable is included to force a call to this function on every
' record in the query. it is NOT used within the function itself.

Randomize
isRandomNum = Rnd

End Function

you can call the function in the query's OrderBy statement, as

...ORDER BY isRandomNum([PrimaryKeyField])

replace PrimaryKeyField with the correct name of the table's primary key
field, of course. actually, you can probably use any field in the table; i
just make a habit of referencing the PK field whenever the value of the
referenced field is not important to whatever i'm doing at the time.

(prefer a number like 0.812 or 0.342 etc etc)

that, i can't tell you how to do. but since you're using the random numbers
to sort the query records, why does it matter what the numbers actually are?

hth

Hello Everyone,

Im trying to make a query which will allow me to run a query update so that
all my listings will get a new random number generated for them,

Ive got it selecting any listing with a ID of >0 (so basically thats

all
of

the listings)

What id like it to do is then assign a random number to the randsort field,
(prefer a number like 0.812 or 0.342 etc etc)

To start with ive tried using the Rnd feature, but its updating all the
listings with exactly the same number (though granted the number changes
each time the query is run)

I see in some of the other comments regarding rnd that you may need to

put

a
randomize statement somewhere,

Im very new to programming in access and was just wondering am I on the
right track, and if so where abouts should i declare the randomize code
since its in a query?

Thanks for your time and look forward to your replies

Cheers Saint
MSACCESS 2003

 

[The Boondock Saint]

Thanks very much,
Its working now, thanks to your awesome help,

Brillant, Thanks alot
Hope this might help others who are trying to do this also.. Thanks Tina

Cheers Saint

you can't name the public module the same name as the procedure; if that's
what you did, then change the module name.

also, since you're using this in an Update, change the syntax slightly, to

isRandomNum([TableName].[PrimaryKeyField])

replace TableName with the correct name of the table, of course. also, note
that the function returns a Single data type. check in table Design view to
make sure that the field being updated has a data type of Single or Double.

hth

Thanks for your reply Tina,

I put the code into a module, and then went into the query itself, and put
in the UPDATE TO section of the field i want to update and made it
isRandomNum([PrimaryKeyField]) with the PKF as link_id ,which is my
PFK...

I then went to run it, but its coming up with a compile error... Expected
variable or procedure, not module

Was I ment to put a number or something in the place of the X? Im very new
to this kind of coding, so any help would be awesome..

Cheers Saint

to return a random number to each record in a query, paste the following
function into a public module, as

Public Function isRandomNum(ByVal X As String) As Single

' the x variable is included to force a call to this function on every
' record in the query. it is NOT used within the function itself.

Randomize
isRandomNum = Rnd

End Function

you can call the function in the query's OrderBy statement, as

...ORDER BY isRandomNum([PrimaryKeyField])

replace PrimaryKeyField with the correct name of the table's primary key
field, of course. actually, you can probably use any field in the

table;

i actually
are? to
put

 

[tina]

you're welcome

Thanks very much,
Its working now, thanks to your awesome help,

Brillant, Thanks alot
Hope this might help others who are trying to do this also.. Thanks Tina

Cheers Saint

you can't name the public module the same name as the procedure; if that's
what you did, then change the module name.

also, since you're using this in an Update, change the syntax slightly, to

isRandomNum([TableName].[PrimaryKeyField])

replace TableName with the correct name of the table, of course. also, note
that the function returns a Single data type. check in table Design view to
make sure that the field being updated has a data type of Single or Double.

hth

Thanks for your reply Tina,

I put the code into a module, and then went into the query itself, and put
in the UPDATE TO section of the field i want to update and made it
isRandomNum([PrimaryKeyField]) with the PKF as link_id ,which is my
PFK...

I then went to run it, but its coming up with a compile error... Expected
variable or procedure, not module

Was I ment to put a number or something in the place of the X? Im

very
new

to this kind of coding, so any help would be awesome..

Cheers Saint

to return a random number to each record in a query, paste the following
function into a public module, as

Public Function isRandomNum(ByVal X As String) As Single

' the x variable is included to force a call to this function on every
' record in the query. it is NOT used within the function itself.

Randomize
isRandomNum = Rnd

End Function

you can call the function in the query's OrderBy statement, as

...ORDER BY isRandomNum([PrimaryKeyField])

replace PrimaryKeyField with the correct name of the table's primary key
field, of course. actually, you can probably use any field in the

table;

i
just make a habit of referencing the PK field whenever the value of the
referenced field is not important to whatever i'm doing at the time.

(prefer a number like 0.812 or 0.342 etc etc)

that, i can't tell you how to do. but since you're using the random
numbers
to sort the query records, why does it matter what the numbers actually
are?

hth


"The Boondock Saint" <noemail> wrote in message
Hello Everyone,

Im trying to make a query which will allow me to run a query

update

so thats
all all
the

need

to on
the