change a decimal number to a whole number

[Tami]

The following was used in a query to scramble the records and reassign a new
number. The problem is, it did it in decimal format ex: 1.243565454 and I
cannot figure out how to change it to a single digit. I would like it to
start with the number 1 and then continue without skipping numbers. So if I
have a hundred records it would start at 1 and end at 100. Can I change the
format in the Shuffle: RndNum([fieldname]) field?

Public Function RndNum(vIgnore As Variant) As Double

Static bRnd As Boolean
If Not bRnd Then
'Initialize the random number generator once only
bRnd = True
Randomize
End If
RndNum = Rnd()
End Function

Then add a calculated field to your Query by typing

Shuffle: RndNum([fieldname])

 

[Ken Snell \(MVP\)]

Look at the Int and CInt functions.

Int(1.243565454) yields 1 as the number.

CInt(1.243565454) yields 1 as the number, too.

However, CInt rounds up if the fractional part of the number is .5 or
higher; Int does not round.

So, Int(1.743565454) yields a value of 1, but CInt(1.743565454) yields a
value of 2.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

The following was used in a query to scramble the records and reassign a
new
number. The problem is, it did it in decimal format ex: 1.243565454 and I
cannot figure out how to change it to a single digit. I would like it to
start with the number 1 and then continue without skipping numbers. So if
I
have a hundred records it would start at 1 and end at 100. Can I change
the
format in the Shuffle: RndNum([fieldname]) field?

Public Function RndNum(vIgnore As Variant) As Double

Static bRnd As Boolean
If Not bRnd Then
'Initialize the random number generator once only
bRnd = True
Randomize
End If
RndNum = Rnd()
End Function

Then add a calculated field to your Query by typing

Shuffle: RndNum([fieldname])

 

[John W. Vinson]

The following was used in a query to scramble the records and reassign a new
number. The problem is, it did it in decimal format ex: 1.243565454 and I
cannot figure out how to change it to a single digit. I would like it to
start with the number 1 and then continue without skipping numbers. So if I
have a hundred records it would start at 1 and end at 100. Can I change the
format in the Shuffle: RndNum([fieldname]) field?

Public Function RndNum(vIgnore As Variant) As Double

Static bRnd As Boolean
If Not bRnd Then
'Initialize the random number generator once only
bRnd = True
Randomize
End If
RndNum = Rnd()
End Function

Then add a calculated field to your Query by typing

Shuffle: RndNum([fieldname])

You can't have it both ways, Tami! Do you want random numbers? or do you want
sequential numbers? If they're random they're not sequential and vice versa
(well, one chance in N factorial that a random series will happen to be
sequential)!

If you have a set of 100 records with field containing integer values from 1
through 100, you can *sort* by Shuffle and *show* the integer; it will be
shuffled into a random order. You don't need to display Shuffle at all.

 

[Clif McIrvin]

See what Ken and John had to say.

For random integers between 1 and 100, change
--
Clif

The following was used in a query to scramble the records and reassign
a new
number. The problem is, it did it in decimal format ex: 1.243565454
and I
cannot figure out how to change it to a single digit. I would like it
to
start with the number 1 and then continue without skipping numbers.
So if I
have a hundred records it would start at 1 and end at 100. Can I
change the
format in the Shuffle: RndNum([fieldname]) field?

Public Function RndNum(vIgnore As Variant) As Double

Static bRnd As Boolean
If Not bRnd Then
'Initialize the random number generator once only
bRnd = True
Randomize
End If
RndNum = Rnd()
End Function

Then add a calculated field to your Query by typing

Shuffle: RndNum([fieldname])

 

[John W. Vinson]

See what Ken and John had to say.

For random integers between 1 and 100, change

The only trouble there is that it will not be very likely to give every
integer only once - you very well might get series like 2, 2, 2, 4, 5, 7, 7
and so on.

 

[Clif McIrvin]

The only trouble there is that it will not be very likely to give
every
integer only once - you very well might get series like 2, 2, 2, 4, 5,
7, 7
and so on.

Good point. I 'conveniently' forgot about that.