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 watch for undesirable 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 have to nest the IIF() function one within another, when two or more expressions are evaluated, and it is absolutely necessary that the parentheses are 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 very simple to use without nesting of expressions like IIF().  Let us rewrite the CheckIt() Function, with 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 work, but if none of the expressions evaluate to True then 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 repeating 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 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 Access2007
• Embedded Macros in Access2007
• Attachment Field in Access2007
• PrimaryKey usage with many fields

Read More

Preparing Rank List

Introduction

We use Autonumber Field in a Table to create automatic sequence numbers for each record entered into the Table. This can be defined as the Primary Key field. The related table field can be assigned these values as Foreign Key to link with the parent Table, so that combined related information can be obtained for Data Views or Reports.  Even if it is a stand-alone table, not part of any relationship, still it is a good idea to create an autonumber field so that the data can be sorted in keyed-in sequence, especially when the data entry date/time is not recorded in the table.

The Running Sum Property

But, when you pull this field into a Query with any filter criteria, then the auto-number Field contents may not be in consecutive order. If you have planned to use the auto-number field on the report for sequence numbers, then you have to look for alternatives. As far as the Report is concerned, this is not an issue, you can easily create one Textbox in the Detail Section of the Report and type the expression =1 in the Control Source property, and set the Running Sum property value to Yes.

If the Query is used as a Data View file or creates an output table from the filtered data with proper sequence numbers then we have to think of devising some tricks to achieve that. I have already created a Function for generating Sequence Numbers for filtered data in a Query column in an earlier Blog Post and you can have it from the link: auto-numbering in Query Column.

The above was only an introduction to what we are going to do here.  What we have seen in the above Auto-numbering example is assigning sequence numbers for the entire query output records.  But, what if we want separate sequence numbers for each category of records or records belonging to a particular group?

For example: In a School, the Headmaster would like to know who is the highest rank holder in each Subject in a particular Class/School.  Or to find the top 5 State-level student Rank holders of the Schools in each Subject.  Or you have invited quotations for supplying electronic goods and you would like to know item-wise lowest Quotes from among several suppliers.

To do this we can write a VBA Function to work on a report source table with a new data field: Rank, to write out the rank list values.  First, the Data Table must be prepared by combining information from input Tables/Queries, etc.  A sample image of a Students’ Table with several subjects is given below, ready for running the Rank-List Program.

The Rank List.

Our task is to organize the above data in a specified order and assign Rank numbers as 1,2,3 etc., based on the highest values in the Score field (sorted in descending order) for each group of subjects in the Event field (sorted in ascending order).  This rank list is prepared for Class No.2 involving 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 for the presence of an Index 'MyIndex' in the input table. If not found, then creates the Index, for temporary use.  After creating the rank list on the table, we delete the temporary Index “MyIndex” from the table.

The result of the run of RankList("SchoolTable","Events","Score","School") Function is given below.  Check the Rank field values based on the Score field values of the Event field.

The first two ranks are obtained for Accounting from the City View School, 3rd rank by Krum School, 4,5 & 6 awarded to Holiday School.  The seventh rank is shared by City View and Holiday Schools.

Similarly, Current Events and Social Studies are also listed according to their order of rank.

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.

Read More

Restoring disabled Full Menus Access2007

Introduction - Access 2003.

When you develop an application, even if it is for your own use, always consider that each object you design is for someone else use. Then only we will be serious about giving a closer look at each control’s function, their strength, and drawbacks.  Others may not treat each control on a Table, Form, Report, etc., as we visualize their usage, the Users may try to enter garbage into the data entry fields or may try to implement their own ideas, if they can, in the design of a Control or Form and so on.

Let us take an example of a Date-of-Birth (or an Invoice Date) data entry field on a Form.  If we leave this field open to accept any date value, then it is likely that the user may make mistakes like entering a future date.  To prevent such eventualities we must enable the built-in Validation checks on the value entered into the date field and warn the user about the mistake and force him to enter a valid value.

Data Field Validation Checks.

We can do this by setting the Validation Rule and Validation Text property values of the Date Field.  The Validation Rule property of the date field can be set with the expression: <Date() to accept date values earlier than Today in the date-of-birth field. But, this expression is good only if we are prepared to accept date-of-birth values earlier than 100 years or more, otherwise we must set a lower bound value too, like >=(DateAdd(“yyyy”,-100, Date()) and <Date().  To warn the user about the data entry error, the Validation Text property can be set with a message text like Future date is invalid and Age Limit 100 years.  This is better if you implement it on the Table itself rather than on the Form.

Preventing Changes to Important Objects.

It is equally important that Users are kept away from modifying objects like Forms, Queries, Reports, Macros, and VBA Programs.  This is where we consider implementing User-level Security in Microsoft Access.  The user-level security feature is available in Microsoft Access2003 or earlier versions only.

Keep Users' Freedom within Limits.

Another way of preventing users from straying out is to create Custom Menus and Custom Toolbars for the Application and disable all built-in Menus and Toolbars so that users are not able to get into object designs through the built-in Menus.  If you have designed customized Menus and Toolbars, then you can disable the built-in Menus by removing the Check-marks in the Access Options.

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, the customized Menus and Toolbars you have created are only available to the User. But, unfortunately even now your database objects Forms, Reports, etc., are not safe from unauthorized changes. It is true that they cannot right-click on an object in the Navigation Pane and go into the design view, but they can if they select the VBA Module's view from the Navigation Pane, browse for a Form/Report, select it and then select View Object option.

In the VBA Navigation Pane, Forms and Reports with VBA Class Module behind them are visible to the Users. You can hide those objects by setting their Hide Option by right-clicking on the database Navigation Pane. If you don't want Users to go into the Module View, through the Navigation Pane then you may remove the check-mark from the Display Navigation Pane option along with the Menu options explained above.

Unless the User is too smart you can consider your Database Objects safe, but not safe enough from a smarter one. If someone familiar with the Keyboard Shortcuts can open the VBA Module wide open by pressing ALT+F11. You can write the rest of the story yourself from there.  Even If you keep the Navigation Pane of the VBA window hidden it can be opened by clicking on the Object Browser button on the toolbar.

Since the User-level Security is not available in Access2007 and later versions, these are some of the methods available to you to implement security within an Access Database.

Once you remove the Full Menus Option it will be difficult for you to get back into the database for making changes to Forms, Reports, etc. That's why I suggest taking a backup, to be on the safe side.

Even during design time make it as a regular practice to take back-ups of the database, to save the work done so far, before starting with latest changes. If the database somehow got corrupted (or deleted by mistake) at any stage of design time you are safe from totally losing your database.

Restoring Full Menus Back.

If you want the Full menus back again we can play a small trick to get back into the database and put the check-mark back into the Allow Full Menus Option, without going through directly from the Office Button Menu.

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 re-open the database again.

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

Technorati Tags: Microsoft

Earlier Post Link References:

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

Read More