IIF vs SWITCH Function in MS-Access

Introduction.

The IIF() Function.

The IIF() Function definition is reproduced here from 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 the word "Large" if the amount is greater than 1000; otherwise, it returns the word "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 to return one of the values among them.

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 above example, for testing each expression, we can nest the IIF() function one within another, when two or more expressions are evaluated, and the parentheses must be paired properly.  This is where we face problems while 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")

OR

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, then the first example will be used with Switch() enveloped in Nz(). 

I think it is easier and more compact to use the Switch() Function when compared with IIF(), which needs to repeat the function name IIF with balancing of several parentheses when several expressions are evaluated.

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

Earlier Post Link References:

• Top N Records in Query
• Attachment field in Access 2007
• Embedded Macros in Access 2007
• Attachment Field in Access 2007
• PrimaryKey usage with many fields

Preparing Rank List

Introduction

We often use an Autonumber field in a table to automatically generate a unique sequence number for each record entered. This field can serve as the Primary Key. In related tables, a corresponding field can be designated as a Foreign Key to establish a relationship with the parent table, enabling combined views of related data forms, queries, or reports.

Even in a stand-alone table that is not part of any relationship, using an autonumber field is beneficial. It allows records to be sorted in the order they were entered, which is especially useful when the table does not include a date/time field for data entry.

The Running Sum Property

However, when you pull an Autonumber field into a query with any filter criteria, its values may no longer appear in consecutive order. If you intend to use the autonumber field for sequence numbers on a report, this isn’t a problem. You can simply add a textbox in the Detail Section of the report, set its Control Source to =1, and set the Running Sum property to Yes—Access will automatically number the report rows sequentially.

If the query is being used as a data source for a Data View or to create an output table with properly sequenced numbers, we need additional techniques to achieve correct numbering. In an earlier blog post, I shared a Function to generate sequence numbers for filtered query results. You can refer to it here: [Auto-numbering in Query Column].

The above discussion introduces the concept of assigning sequence numbers across all records in a query. But what if we want separate sequence numbers for each category or group of records?

For example:

• In a school, the headmaster may want to identify the highest-ranked holder in each subject for a particular class or school.

• Or, find the top 5 state-level rank holders in each subject across schools.

• In a procurement scenario, you want to identify the lowest quotes for each item from multiple suppliers.

To achieve this, we can write a VBA function that works on a report source table, adding a new field (e.g., Rank) to store the ranking. First, the data table must be prepared by consolidating information from input tables or queries.

Below is a sample image of a student’s table with several subjects, ready for running the Rank-List program.

Right-click to open Large Image in New Tab/Window.

The Rank List.

Our task is to organize the above data in a specified order and assign Rank numbers (1, 2, 3, …) based on the highest values in the Score field, sorted in descending order. This ranking should be done separately for each group of subjects in the Event field, which is sorted in ascending order.

The rank list is being prepared for Class No. 2, covering students from several schools in the area.

Table Name: SchoolTable

Sorting Order: The Event (Ascending), Score (Descending), School (Ascending) – School field sorting optional

Function Call Syntax: RankList(TableName, Primary Sorting Field, Value Field, Optional Third Sorting Field)

Sample Function Call: RankList(“SchoolTable”, ”Events”, ”Score”, ”School”)

The RankList() Function

The RankList() Function Code is given below:

Public Function RankList(ByVal TableName As String, _
                         ByVal Grp1Field As String, _
                         ByVal ValueField As String, _
                         Optional ByVal Grp2Field As String)
'-----------------------------------------------------------------
'Preparing Rank List
'Author : a.p.r.pillai
'Date   : August 2011
'Rights : All Rights Reserved by www.msaccesstips.com
'Remarks: Free to use in your Projects
'-----------------------------------------------------------------
'Parameter List:
'TableName  : Source Data Table
'Grp1Field  : Category Group to Sort on
'ValueField : On which to determine the Rank Order
'Grp2Field  : Sorted on for values with the same rank number
'-----------------------------------------------------------------
Dim db As Database, rst As Recordset, curntValue, prevValue
Dim srlRank As Byte, curntGrp1, prevGrp1
Dim prevGrp2, curntGrp2
Dim fld As Field, tbldef As TableDef, idx As Index
Dim FieldType As Integer

On Error Resume Next

Set db = CurrentDb
Set rst = db.OpenRecordset(TableName, dbOpenTable)

'Check for presence of Table Index "MyIndex"
'if not found then create
rst.Index = "MyIndex"

If Err > 0 Then
   Err.Clear
   On Error GoTo RankList_Err

Set tbldef = db.TableDefs(TableName)
Set idx = tbldef.CreateIndex("MyIndex")

FieldType = rst.Fields(Grp1Field).Type
Set fld = tbldef.CreateField(Grp1Field, FieldType)
idx.Fields.Append fld

FieldType = rst.Fields(ValueField).Type
Set fld = tbldef.CreateField(ValueField, FieldType)
fld.Attributes = dbDescending ' Line not required for sorting in Ascending
idx.Fields.Append fld

FieldType = rst.Fields(Grp2Field).Type
Set fld = tbldef.CreateField(Grp2Field, FieldType)
idx.Fields.Append fld

rst.Close

tbldef.Indexes.Append idx
tbldef.Indexes.Refresh
Set rst = db.OpenRecordset(TableName, dbOpenTable)
rst.Index = "MyIndex"
End If

curntGrp1 = rst.Fields(Grp1Field)
prevGrp1 = curntGrp1
curntValue = rst.Fields(ValueField).Value
prevValue = curntValue

Do While Not rst.EOF
     srlRank = 1
     Do While (curntGrp1 = prevGrp1) And Not rst.EOF
       If curntValue < prevValue Then
          srlRank = srlRank + 1
       End If
          rst.Edit
          rst![Rank] = srlRank
          rst.Update
          rst.MoveNext
          If Not rst.EOF Then
             curntGrp1 = rst.Fields(Grp1Field)
             prevValue = curntValue
             curntValue = rst.Fields(ValueField).Value
          End If
     Loop
     prevGrp1 = curntGrp1
     prevValue = curntValue
Loop
rst.Close
'Delete the Temporary Index
tbldef.Indexes.Delete "MyIndex"
tbldef.Indexes.Refresh

Set rst = Nothing
Set db = Nothing

RankList_Exit:
Exit Function

RankList_Err:
MsgBox Err & " : " & Err.Description, , "RankList()"
Resume RankList_Exit

End Function

The Code Creates a Temporary Index

In the first part of the program, we check whether an index named MyIndex exists in the input table. If the index is not found, the program creates it temporarily for use during the ranking process. Once the rank list has been generated in the table, the temporary index MyIndex is deleted.

The result of running the function:

RankList("SchoolTable", "Events", "Score", "School")

is shown below. Observe the Rank field values, which are assigned based on the Score values within each Event group.

Right-click to open Large Image in New Tab/Window.

In the Accounting event, the first two ranks are awarded to City View School, the third rank goes to Krum School, and the fourth, fifth, and sixth ranks are assigned to Holiday School. The seventh rank is shared by City View and Holiday Schools.

Similarly, the events Current Events and Social Studies are also ranked in order, with each school receiving ranks based on its scores within the respective event.

Download.

Download Demo RankList.zip

1. Auto-Numbering in Query Column
2. Product Group Sequence with Auto-Numbers.
3. Preparing Rank List.
4. Auto-Number with Date and Sequence Number.
5. Auto-Number with Date and Sequence Number-2.

Restoring disabled Full Menus Access2007

Introduction - Access 2003.

When developing an application, even for personal use, it is important to design each object as if it will be used by someone else. Only then will we take a careful look at each control’s functionality, strengths, and limitations. Users may not interact with a table, form, or report in the way we envision. They might enter incorrect data, attempt unintended actions, or experiment with the design of controls and forms.

For example, consider a Date of Birth or Invoice Date field on a form. If the field allows any date value, users may accidentally enter invalid dates, such as a future date. To prevent such errors, we should enable built-in validation checks on the field, alert users to mistakes, and enforce the entry of valid values.

Data Field Validation Checks.

We can enforce this by setting the Validation Rule and Validation Text properties of the date field.

The Validation Rule property can be set to <Date() to accept only date values earlier than today, suitable for a Date-of-Birth field.

If you want to restrict entries to a reasonable range, for example, limiting ages to 100 years, you can use a rule like:

>=(DateAdd("yyyy",-100, Date())) And <Date()  

This ensures that the entered date is not more than 100 years in the past and not a future date.

The Validation Text property can be set with a message such as:

"Future date is invalid and Age Limit is 100 years"

It is recommended to implement this directly on the Table field rather than just on the Form, as this ensures data integrity regardless of how the data is entered.

Preventing Changes to Important Objects.

Equally important is ensuring that users cannot modify critical objects such as Forms, Queries, Reports, Macros, and VBA code. This is where User-Level Security in Microsoft Access comes into play. 

Note, however, that this security feature is available only in Access 2003 and earlier versions.

Keep Users' Freedom within Limits.

Another effective way to prevent users from straying into unintended areas is to create custom menus and toolbars for your application, while disabling all built-in menus and toolbars. This ensures that users cannot access object designs or other sensitive features through the default Access interface.

Once your customized menus and toolbars are in place, you can disable the built-in menus by removing the relevant check marks in the Access Options. This creates a controlled environment, allowing users to interact only with the parts of the application you intend them to use.

Caution: Take a backup of the database before making the following changes otherwise you may not be able to get back into the database for making design changes yourself later.

Select Office Button  -> Access Options  -> Current Database  -> Ribbon and Toolbar Options

Remove check marks from the following options:

• Allow Full Menus
• Allow Default Shortcut Menus
• Allow Built-in Toolbars

After removing the check marks, you must close and reopen the database. Now, only the customized menus and toolbars you created will be available to the user.

However, your database objects—Forms, Reports, etc.—are still not fully protected from unauthorized changes. While users cannot right-click an object in the Navigation Pane to enter Design View, they can still access objects via the VBA Modules. For example, they might browse the Navigation Pane, select a Form or Report, and then choose the View Object option.

In the VBA Navigation Pane, Forms and Reports with associated Class Modules are visible to users. To limit access, you can hide these objects by right-clicking in the Navigation Pane and setting the Hide option.

For more comprehensive protection, you can also disable the Navigation Pane entirely by removing the check mark from Display Navigation Pane, in addition to disabling the built-in menus as explained earlier. This ensures that users cannot navigate to the module or object views at all.

Unless the user is very clever, you can consider your database objects reasonably safe—but not completely secure from a determined or knowledgeable individual. For instance, anyone familiar with keyboard shortcuts can open the VBA Editor by pressing ALT+F11. From there, the story unfolds on its own. Even if you hide the Navigation Pane within the VBA window, it can still be accessed via the Object Browser button on the toolbar.

Since User-Level Security is no longer available in Access 2007 and later versions, these are some of the methods you can use to implement security within an Access database.

Once you remove the full menus option, it becomes difficult to make design changes to Forms, Reports, or other objects. For this reason, it is highly recommended to take a backup before implementing these security measures.

Even during the design phase, make it a regular practice to create backups of your database. This ensures that if your database becomes corrupted or is accidentally deleted at any stage, your work is safe, and you do not risk losing all your progress.

Restoring Full Menus.

If you want to restore the full menus, there is a simple trick to regain access to the database and re-enable the Allow Full Menus option—without going through the Office Button menu directly.

1. Open the database.

2. Press ALT+F11 to display the VBA Window

3. Press CTRL+G to display the Debugging Window (Immediate Window).

4. Type the following command in the Debug Window and press Enter Key:

   CurrentDb.Properties("AllowFullMenus").Value = True

5. Close and reopen the database.

Now, you can approach Access Options through the Office Button (top left corner) and make changes to Options there.

Technorati Tags: Microsoft

Earlier Post Link References:

• Attachment Field in Access 2007
• PrimaryKey usage with many fields
• Percentage on Report Summary
• Embedded Macros in Access 2007
• Report Design in Access 2007