LEARN MS-ACCESS TIPS AND TRICKS: function

Public Function ROUNDDOWN(ByVal Num As Double, ByVal num_digits As Integer) As Double '------------------------------------------------- 'ROUNDDOWN() Function of Excel Redefined in MS-Access 'Author: apr pillai 'Date : Sept 2019 'Rights: All Rights Reserved by www.msaccesstips.com '------------------------------------------------- Dim S1 As Integer, S2 As Integer On Error GoTo ROUNDDOWN_Err S1 = Sgn(Num) S2 = Sgn(num_digits) Select Case S1 Case 0 ROUNDDOWN = 0 Exit Function Case 1 Select Case S2 Case 0 ROUNDDOWN = Int(Num) * S1 Case 1 ROUNDDOWN = (Int(Num * (10 ^ num_digits)) / 10 ^ num_digits) * S1 Case -1 num_digits = Abs(num_digits) ROUNDDOWN = Int(Num / (10 ^ num_digits)) * 10 ^ (num_digits) * S1 End Select Case -1 Select Case S2 Case 0 ROUNDDOWN = Int(Abs(Num)) * S1 Case 1 ROUNDDOWN = (Int(Abs(Num) * (10 ^ num_digits)) / 10 ^ num_digits) * S1 Case -1 num_digits = Abs(num_digits) ROUNDDOWN = (Int(Abs(Num) / (10 ^ num_digits)) * 10 ^ num_digits) * S2 End Select End Select ROUNDDOWN_Exit: Exit Function ROUNDDOWN_Err: MsgBox Err & " : " & Err.Description, , "ROUNDDOWN()" Resume ROUNDDOWN_Exit End Function

Formula	Description	Result
=ROUNDDOWN(3.2, 0)	Rounds 3.2 down to zero decimal places.	3
=ROUNDDOWN(76.9,0)	Rounds 76.9 down to zero decimal places.	76
=ROUNDDOWN(3.14159, 3)	Rounds 3.14159 down to three decimal places.	3.141
=ROUNDDOWN(-3.14159, 1)	Rounds -3.14159 down to one decimal place.	3.1
=ROUNDDOWN(31415.92654, -2)	Rounds 31415.92654 down to 2 decimal places to the left of the decimal point.	31400

Formula

Description

Result

=ROUNDDOWN(3.2, 0)

Rounds 3.2 down to zero decimal places.

=ROUNDDOWN(76.9,0)

Rounds 76.9 down to zero decimal places.

=ROUNDDOWN(3.14159, 3)

Rounds 3.14159 down to three decimal places.

3.141

=ROUNDDOWN(-3.14159, 1)

Rounds -3.14159 down to one decimal place.

3.1

=ROUNDDOWN(31415.92654, -2)

Rounds 31415.92654 down to 2 decimal places to the left of the decimal point.

31400

IIF vs. SWITCH Function in MS Access.

The IIF() Function.

The IIF() Function definition is reproduced here from the Microsoft Visual Basic for Applications Reference below:

The IIf Function returns one of two parts, depending on the evaluation of an expression.

Syntax: IIf(logical expression, true part, false part)

The IIf function syntax has these named arguments:

Part	Description
Expression	Required. The expression you want to evaluate.
True part	Required. Value or expression returned if the expression evaluates to True.
False part	Required. Value or expression returned if the expression is False.

Remarks

The 'IIf' expression always evaluates both the True Part and the False Part, even though it will return only one of them. Because of this, you should be aware of the side effects. For example, if evaluating to false in a division-by-zero error case, an error occurs even if the expression is True.

Example:

This example uses the IIf() function to evaluate the TestMe parameter of the CheckIt() Function and returns "Large" if the amount is greater than 1000; otherwise, it returns "Small".

Function CheckIt(TestMe As Integer)
      CheckIt = IIf(TestMe > 1000, "Large", "Small") 
End Function

Courtesy: Microsoft Access Help

Let us expand the above function to check a series of expressions that return a value.

Function CheckIt(TestMe As Integer)
      CheckIt = IIF(TestMe > 0 AND TestMe < 256, "Byte", _
      IIF(TestMe > 255 AND TestMe < 32768,"Integer","Large")) 
 End Function

As you can see from the example, by testing each expression, we can nest the IIF() function within another when two or more expressions are evaluated, and the parentheses must be paired properly. This is where we have a problem when using this function in Query columns or in criteria rows, etc.

If we need a valid value to be returned when none of the expressions evaluated to True (in the above example, the text "Large"), then IIF() is the correct solution; otherwise, we have a better function, Switch().

The Switch Function.

The Switch() Function is simple to use without nesting expressions like IIF(). Let us rewrite the CheckIt() Function, using the Switch() function, to see how simple it is.

Function CheckIt(TestMe As Integer) 
    CheckIt = Switch(TestMe > 0 AND TestMe < 256, "Byte", _
TestMe > 255 AND TestMe < 32768,"Integer") 

End Function

The above function does the same thing, but if none of the expressions evaluate to True, the result returned in the CheckIt variable is Null instead of the text "Large". If you want to check the returned result for Null and replace Null with the text "Large," then rewrite the expression as below:

CheckIt = NZ(Switch(TestMe > 0 AND TestMe < 256, "Byte",TestMe > 255 AND _
TestMe < 32768,"Integer"),"Large")

x = Switch(TestMe > 0 AND TestMe < 256, "Byte", _
TestMe > 255 AND TestMe < 32768,"Integer")

CheckIt = Nz(x,"Large")

If you are using the Switch() Function in a Query column or criteria row, the first example uses Switch() enveloped in Nz().

I think it is easier and more compact to use the Switch() Function compared with IIF(), which repeats the function name IIF and requires extra parentheses when evaluating multiple expressions.

Usage in a sample Query:

UPDATE Employees SET Employees.Designation = Switch(Employees.Designation IS NULL,'UNKNOWN')
WHERE ((Employees.FirstName IS NOT NULL));

See the Switch() Function definition given below for details, taken from the Microsoft Access Help Document.

Switch Function Evaluates a list of expressions and returns a Variant value or an expression associated with the first expression in the list that is True.
Syntax;
Switch(expr-1, value-1[, expr-2, value-2 … [, expr-n,value-n]])
The Switch function syntax has these parts:

Part Description

expr Required. Variant expression you want to evaluate.

value Required. Value or expression to be returned if the corresponding expression is True.

Remarks:
The Switch function argument list consists of pairs of expressions and values. The expressions are evaluated from left to right, and the value associated with the first expression to evaluate to True is returned. If the parts aren't properly paired, a run-time error occurs. For example, if expr-1 is True, the Switch returns value-1. If expr-1 is False, but expr-2 is True, the Switch returns value-2, and so on.
Switch returns a Null value if: None of the expressions are True. The first True expression has a corresponding value that is Null. Switch evaluates all of the expressions, even though it returns only one of them. For this reason, you should watch for undesirable side effects. For example, if the evaluation of any expression results in a division by zero error, an error occurs.
Example:
This example uses the Switch function to return the name of a language that matches the name of a city.
Function MatchUp (CityName As String)

    Matchup = Switch(CityName = "London", "English", CityName _
                    = "Rome", "Italian", CityName = "Paris", "French")
End Function