Each month, Mike’s Music uses random numbers to pick three customers to receive two free music lessons. To be eligible for selection, the customers must have purchased an instrument that cost at least $50. The Rnd function is used to generate a random number for each sale, which is then used in the selection of the three winners for a particular month.
The Rnd function returns a single precision number greater than or equal to 0 and less than 1. The problem with Rnd is that the result generated constantly changes. You also can’t sort by it. So if you want a record of what was chosen or to sort the results, you need to store the number once it’s generated.
In this case, we’re generating random numbers for the Sales table. To keep track of past winners and have a way to sort the generated numbers, add a long integer field called RandNum to the table. Make sure that it has no default value. When you create a numeric field in Access, the system may automatically set a default value of zero, so you need to specifically ensure it’s been deleted.
When it’s time to pick winners, an update query, “qUpRandNum,” is run to fill the RandNum fields that don’t already have a value:
UPDATE Sales SET Sales.RandNum = Int( Rnd( Sales.SaleID ) * 50000) + 1 WHERE ( Sales.RandNum Is Null );
The Rnd function returns a single precision random number and has an optional argument to set the seed for the random number. The seed is needed when you’re using Rnd in a query to get different results for each row. If you fail to specify the optional argument for Rnd to be a field in a query, you will get the same result for each record. That’s because Access sees that nothing is different about the expression, so it will only calculate it once. In this case, SaleID is used as the seed since it’s unique.
Rnd returns a small value with lots of digits, so the formula multiplies the random number by 50,000 since Mike’s Music may have several hundred sales in a given month. Using the Int function means only the integer portion of the number will be returned, which makes it easier to differentiate the generated numbers. One is then added to the number to ensure the final result isn’t zero. The WHERE statement then specifies that only records in the table that don’t have a value already will be calculated and updated.
Once the Sales table has values for RandNum, the “qParm_MonthWinners” query picks three winners for a particular month. Figure 1 shows the Design view of qParm_MonthWinners. The Sales and Customers tables are used as sources and link on CustomerID. The expression in the first column, YYMMsale: Format([SaleDate],"yymm"), names the column YYMMsale and uses the Format function to pull out the two-digit year and two-digit month from the sale date and return a text value. Instead of specifying a month in the query design, putting [Enter YYMM of sale:] in the Criteria will cause Access to prompt for the value because Access doesn’t recognize it as a field name. Thus, each time the query is run, the user can enter the desired year and month in YYMM format.
The next column is the customer name, represented by CustomerID. This is followed by purchase-related information, including Category, Product, and Amount. The criteria for the Amount limits the query results to instrument purchases greater than or equal to $50. The last column shows the random number generated for that purchase and sorts the query based on that column.
Because only three winners are chosen each month, we need to limit the query results to three records. Open up the property sheet while in Design view, then click in a gray area of the query design so the selection type of the property sheet is “Query Properties,” as in Figure 1. Set the Top Values property to 3. Figure 2 shows the Datasheet view of the query displaying the three winners for the month.
The tables in this example have text values for product and category in the Sales table. A best practice would instead involve a Products table with a unique ProductID as well as a Categories table with a unique CategoryID. The Sales table would then store ProductID and CategoryID just as it does CustomerID.
USING Rnd IN VBA
In VBA, you can set the seed for Rnd and eliminate the need to specify the optional argument by first using the Randomize statement. Like Rnd, there’s an optional argument for the seed. If nothing is specified, the number of seconds that have elapsed since midnight, or the result of the Timer function, will be used. Timer returns a single precision number.
You can also use Rnd to generate a random number within a specific range. In the expression Int((MaxNumber - MinNumber + 1) * Rnd( [MyFieldName] ) + MinNumber ), MinNumber is the lowest value and MaxNumber is the highest value. The Int function then truncates the result to an integer.
Download this month’s database: SF1903_RandomNumbers.
SF SAYS
The number generated by Rnd changes every time an expression is calculated. To keep a record of the values created, make a place to store them.
March 2019
