Menu
The Case statement can only be used in VBA code in Microsoft Access. You can also use the To keyword to specify a range of values. You can also comma delimit values. And finally, you can also use the Is keyword to compare values.
An expression is a combination of mathematical or logical operators, constants, functions, table fields, controls, and properties that evaluates to a single value. You can use expressions in Access to calculate values, validate data, and set a default value.
Right-click the control that you want to change, and then click Properties on the shortcut menu. Access displays the property sheet for the control. Click the All tab, and then click the Validation Rule property box. in the property box to create an expression by using the Expression Builder.
A Select Case statement allows a variable to be tested for equality against a list of values. Each value is called a case, and the variable being switched on is checked for each select case.
You can use the IIF() function instead.
IIF(condition, valueiftrue, valueiffalse)
condition is the value that you want to test.
valueiftrue is the value that is returned if condition evaluates to TRUE.
valueiffalse is the value that is returned if condition evaluates to FALSE.
There is also the Switch function which is easier to use and understand when you have multiple conditions to test:
Switch( expr-1, value-1 [, expr-2, value-2 ] … [, expr-n, value-n ] )
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, Switch returns value-1. If expr-1 is False, but expr-2 is True, Switch returns value-2, and so on.
Switch returns a Null value if:
None of the expressions is True.
The first True expression has a corresponding value that is Null.
NOTE: 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.
There is no case statement in Access. Instead you can use switch statement. It will look something like the one below:
switch(dbo_tbl_property.LASTSERVICEDATE > Contour_dates.[Last CP12 Date],dbo_tbl_property.LASTSERVICEDATE,dbo_tbl_property.LASTSERVICEDATE <= Contour_dates.[Last CP12 Date],Contour_dates.[Last CP12 Date])
For further reading look at: http://www.techonthenet.com/access/functions/advanced/switch.php
Or for case function implementation example in VBA:
http://ewbi.blogs.com/develops/2006/02/adding_case_to_.html
Regards, J.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With