More Mathematical Functions
The mathematical functions in Access don't get much respect, because people don't need them terribly often. You've already seen Round( ) and Format( )the most useful of the bunchbut there are still a few others that Access mavens turn to from time to time in calculated fields. They're listed in Table-3.
Table-3. Functions for Numeric Data| Function | Description | Example | Result |
|---|---|---|---|
| Sqr( ) | Get the square root | Sqr(9) | 3 |
| Abs( ) | Gets the absolute value (negative numbers become positive) | Abs(-6) | 6 |
| Round( ) | Rounds a number to the specified number of decimal places | Round(8.89, 1) | 8.9 |
| Fix( ) | Gets the integer portion of the number, chopping off any decimal places | Fix(8.89) | 8 |
| Int( ) | The same as Fix( ), but negative numbers are rounded down instead of up | Int(-8.89) | -9 |
| Rnd( ) | Generates a fractional random number between 0 and 1 | Int ((6) * Rnd + 1) | A random integer from 1 to 6 |
| Val( ) | Converts numeric data in a text field into a bona fide number, so that you can use it in a calculation. Stops as soon as it finds a non-numeric character, and returns 0 if it can't find any numbers. | Val("315 Crossland St") | 315 |
| Format( ) | Turns a number into a formatted string, based on the options you chose | Format(243.6, Currency) | $243.60 |
Use Random Numbers for a Random Sort
People rarely use the Rnd( ) functionafter all, who needs to fill a column with made-up information? However, enterprising Access gurus have come up with one intriguing use for Rnd( ). They use it to sort a table so that all the records appear in a random order.
Basically, you add a calculated field that holds the random number. You can use a field expression such as Random: Rnd( ). If you look at your query results, then you will see a random value between 0 and 1 (like 0.7045, 0.2344, and so on) next to each record.
Now, switch back to Design view, and then clear the Show checkbox so that the Random field doesn't appear in the datasheet. Next, choose Ascending or Descending (it really doesn't matter) in the Sort box, and then rerun the query. Every time you use this query, the records appear in a different order, according to the random numbers that Access generates on the fly.