Random number fail

[Pikeboy]

On my form I have a label that I click in hopes of populating another label with a caption based on a query result. The query result uses a random number in its criteria. At one time this worked perfectly. Now, the first time I click it I get an error message, click 'End', and then click the label again and the code works. What is keeping it from working the first time? When you click debug and look at the values the variable Result will always be 0. It clearly should be between 1 and 20.

lowResult = 1
highResult = 20
Randomize

Do Until n = 5
Result = lowResult + Int((highResult - lowResult + 1) * Rnd())
n = n + 1
Loop

Thanks in advance.

Pikeboy

[Welshgasman]

Works for me.
Why the loop?

[Micron]

And the error message and number is??
You might have to post the whole procedure to get an answer re why it doesn't work the first time for you. If you do, please post code within code tags (use # button on posting toolbar) and maintain indentation.

[Pikeboy]

I am using this to automate the rolling of dice for a board game I have played for years. I was finding without the loop the instances where consecutive rolls were identical to be far more frequent than I anecdotally recall doing so manually.

[davegri]

Here's what I used for a game:

Function fcnRoll() As Integer
    Randomize
    fcnRoll = Int((6 - 1 + 1) * Rnd + 1)
End Function

To get total for two dice:

twodice = fcnRoll + fcnRoll


Here is a distribution debug.print for 10 million rolls.
As you see, the distribution is valid.
2 and 12 are about equal as are
3 and 11,
4 and 10,
5 and 9,
6 and 8,
with 7 most common.

Roll 2 998496
Roll 3 1822885
Roll 4 2822770
Roll 5 3775259
Roll 6 4643795
Roll 7 5584500
Roll 8 4638688
Roll 9 3771408
Roll 10 2831578
Roll 11 1820118
Roll 12 995989

[Pikeboy]

Hopefully this comes through ok.

[Welshgasman]

You do not say what line it errors on?

Walk though your code and report back.

You SHOULD always test for EOF before even trying to use a recordset. No guarantee you will have records to process?

[Pikeboy]

Dim tSql As String
Dim zz As DAO.Recordset
Dim strErrorChance As Integer
Dim strRange As String
Dim lowResult As Integer
Dim highResult As Integer
Dim Result As Integer
Dim whtDice As Integer
Dim redDice1 As Integer
Dim redDice2 As Integer
Dim twtysideDice As Integer
Dim strXResult As String

If Me.lblErrorChance.Caption = "" Or Me.lblXX4.Caption = "DH" Then Exit Sub

lowResult = 1
highResult = 20
Randomize

Do Until n = 5
Result = lowResult + Int((highResult - lowResult + 1) * Rnd()) /// First time through variable Result will equal 0. When you try setting the expression 'Int((highResult - lowResult + 1) * Rnd())' to its own variable it equals 'Empty'
n = n + 1
Loop

'Find player range
sql = "SELECT tblPlayerERatings.Range " & _
"FROM tblPlayerERatings " & _
"WHERE (((tblPlayerERatings.playerID) = '" & Me.cboH4 & "') And ((tblPlayerERatings.Position) = '" & Me.cboPosH4 & "')) " & _
"GROUP BY tblPlayerERatings.Range;"
Set rs = CurrentDb.OpenRecordset(sql)
strRange = rs("Range")

'Find X-Result
sql = "SELECT tblXResults.Result " & _
"FROM tblXResults " & _
"WHERE (((tblXResults.Position) = '" & Me.cboPosH4 & "') And ((tblXResults.Range) = " & strRange & ") And ((tblXResults.Roll) = " & Result & ")) " & _
"GROUP BY tblXResults.Result;"
Set rs = CurrentDb.OpenRecordset(sql)
strXResult = rs("Result") /// This is where code stops. This query record returns error because the variable Result is equal to 0. No such return should be possible.

[Welshgasman]

Well keep it in a loop until result is not 0?
Again, I have no idea why your is 0

Put a debug.print Result after Result = and see what gets produced.

Why not follow Dave's example and add 1 to Rnd ?
In fact why not use his example completely and see what that produces?

[Pikeboy]

Unfortunately, Dave's example produced the same result. I was able to work around it by inserting this code:

If rs.RecordCount = 0 Then
Randomize
Result = Int((20 - 1 + 1) * Rnd + 1)
sql = "SELECT tblXResults.Result " & _
"FROM tblXResults " & _
"WHERE (((tblXResults.Position) = '" & Me.cboPosH4 & "') And ((tblXResults.Range) = " & strRange & ") And ((tblXResults.Roll) = " & Result & ")) " & _
"GROUP BY tblXResults.Result;"
Set rs = CurrentDb.OpenRecordset(sql)
strXResult = rs("Result")
Else
strXResult = rs("Result")
End If

I am still curious to know why it is failing. I am not exaggerating when I state I had been using this well over a year without issue.

Thanks for everyone's input. It is much appreciated.

[Welshgasman]

Well something is obviously wrong with your system.
Have you had any updates recently?

Using
Result = LowResult + Int((HighResult - LowResult + 1) * Rnd())
Debug.Print n & " - " & Result

I get

? rndvalue
0 - 8
1 - 6
2 - 19
3 - 10
4 - 7
7
0 - 12
1 - 20
2 - 5
3 - 8
4 - 3
3

Never a zero?

[davegri]

If as you say in post #1 "at one time worked perfectly" and no code changes have been made, I would expect the problem to lie with the data, which indeed has most probably changed.

[Welshgasman]

If as you say in post #1 "at one time worked perfectly" and no code changes have been made, I would expect the problem to lie with the data, which indeed has most probably changed.

No data used in the function though?

[davegri]

OP has not demonstrated that the problem is with the random number.
In fact, the code producing the Result variable in Post#1 is valid (see below), so the problem is either elsewhere in the code or due to bad data.


Edit: In original post#1, it appears that Option Explicit has not been specified, as there is no visible declaration for n, nor has rs been declared as a DAO.recordset, or sql as string.

Public Sub Check()
    Dim n As Integer, Result As Integer, Count As Long, j As Integer
    Dim lowresult As Integer: lowresult = 1
    Dim highResult As Integer: highResult = 20
    Dim agg As Long
    Randomize
        For n = 0 To 21
            For j = 1 To 1000
            Result = lowresult + Int((highResult - lowresult + 1) * Rnd())
                If Result = n Then Count = Count + 1
                If Result < 1 Or Result > 20 Then Debug.Print "???? " & Result
                DoEvents
            Next j
            Debug.Print n, Count
            agg = agg + Count
            Count = 0
        Next n
        Debug.Print agg
End Sub

This code generates the random number 22000 times, 1000 times each for the numbers 0 thru 21 and counts the total occurrences that each (0-21) happens for the 1000 generations.
The random number generated is never less than one or greater than twenty.
The perfect count is 50, and you can see below that the count falls within an acceptable random nominal range. Each time the test is run, the counts are different, clustering around 50 (random!).
Immediate window:

check
0 0
1 53
2 59
3 45
4 53
5 63
6 62
7 62
8 60
9 44
10 46
11 43
12 47
13 49
14 51
15 61
16 55
17 43
18 50
19 46
20 46
21 0
1038