IIF vs SWITCH Function in MS-Access

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")

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, 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

Earlier Post Link References:

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

Read More

Preparing Rank List

Preparing a Rank List.

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 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 the large image in a 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 the Large Image in a New Tab/Window.

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

Similarly, Current Events and Social Studies are ranked in the same order, with each school receiving its rank based on its scores in its 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.

Read More

Restoring disabled Full Menus Access2007

Restoring disabled Full Menus Access 2007

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 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 of Birth is neither more than 100 years old nor a future date.

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

"Future date is invalid, and the 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 associated with Class Modules are visible to users. To limit access, you can hide these objects by right-clicking in the Navigation Pane and selecting 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 in the VBA window, access it via the Object Browser button on the toolbar.

Since User-Level Security is no longer available in Access 2007 and later versions, these are the methods you can use to implement security in 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 Database backups. 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 the Enter key:

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

5. Close and reopen the database.

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

Technorati Tags: Microsoft

Earlier Post Link References:

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

Read More

Running Workgroup Admin in Access2007

Running Workgroup Admin in Access2007.

If you are serious about implementing User-Level Security in Microsoft Access 2003 (mdb) databases and want to continue using them in Access 2007 security settings intact, avoid converting them to the Access 2007 format. Once upgraded, the security model changes completely, and User-Level Security is no longer supported in the accdb databases.

If you have already converted an MDB database into ACCDB format and lost all user-level security settings, the situation is not entirely hopeless. If you have a backup of the original .mdb file, you are in luck—simply restore the backup and continue using it in Access 2007, retaining your User-Level Security.

If no backup exists, you can still downgrade the database. Use the Save As… option from the Office Menu (top-left corner), select an earlier format such as Access 2002–2003 or Access 2000, and save the active database as a new .mdb copy. However, this will not automatically restore the lost security settings. You must reapply them manually, assigning permissions to each database object either at the group level or for individual users.

Run the Workgroup Administrator.

In either case, you must join the Workgroup Information File (a database with the file extension .mdw) from Access 2007 to attach your database Users/Groups and continue using them with your .mdb databases, or to implement new security profiles.

When you open an .mdb database in Access 2007, the Users and Permissions menu will appear under Database Tools. However, your Database Users and Groups will not be visible under this option unless you first join the Workgroup Information File you were using in an earlier version of Access.

In Access 2003, you can do this from Tools → Security, which provides an option to run the Workgroup Administrator program to create or join a Workgroup Information File. In Microsoft Access 2000, this program is located in the Language folder (…\1033 for U.S. English) of MS Office:

C:\Program Files\Microsoft Office\Office\1033\WrkgAdm.exe

Running this utility allows you to join the appropriate Workgroup Information File and manage security accordingly.

However, in Microsoft Access 2007, none of these menu options are available. The only way to access the Workgroup Administrator is by running the following command, either from VBA or directly in the Immediate Window:

DoCmd.RunCommand acCmdWorkgroupAdministrator

You can execute this command manually from the Immediate Window (open the VBA editor with Alt+F11, then press Ctrl+G to display the Immediate Window), or programmatically from a CommandButton Click event on a form. This will allow you to create a new Workgroup Information File or join an existing one on the server.

Once you have joined the Workgroup Information File, you can continue using .mdb files with their existing User-level security in Microsoft Access 2007, or manually restore any lost security settings.

Technorati Tags: Microsoft Security

Earlier Post Link References:

• Primary Key usage with many fields
• Percentage on Report Summary
• Embedded Macros in Access 2007
• Report Design in Access 2007
• Memo Field Text Formatting

Read More

Apply Filter to Table directly

Apply a filter directly to the table.

Normally, we use filter settings on Forms or Reports to extract records based on specific conditions. This can be accomplished in several ways—for example:

Adding a WHERE condition (without the keyword WHERE) in the parameter setting of the OpenForm macro action.

Specifying the ApplyFilter condition in the parameter action in a macro.

Supplying criteria in the DoCmd.OpenForm method: 

DoCmd.OpenForm "myForm", acNormal, , "EmployeeID Between 5 AND 10"

Using Filter by Selection directly on a form.

Setting a Query as the form’s record source.

What many developers overlook, however, is that the Filter property of a table itself can also be used to limit records when the table is opened in Datasheet View.

For instance, if you are reviewing someone else’s project and notice that a table displays only a few records in Datasheet View—while you are told the table has hundreds—you may wonder what happened to the rest. In such cases, the hidden culprit might be an active Table Filter.

Setting a Filter on a Table.

We will now explore how this filtering trick works on a Table, for a change. If you have ever applied a filter condition directly on a Form, then you already know enough to do the same with a table—it works almost identically.

To try this out, we need some ready-made data. The Northwind sample database is an excellent choice, but you may also experiment with any table from your own database.

1. Import the Order Details  Table from the Northwind sample database.

2. Open the Table in Design view.

3. Press F4 to display the Table Property Sheet.

   

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

   
   Table Property Sheet View.

4. Find the Filter Property and type [Order ID] Between 35 AND 45 into the property (in Access 2007). For earlier Access Versions, type [OrderID] Between 10249 AND 10251.

5. Set the Filter On Load property value to Yes.

6. Save and close the Table Structure.

7. Open the Table in Datasheet View to see the Filter in action.

The output records displayed will be only those with Order IDs between 35 and 45. If you design a Quick Form or Quick Report from this table, the Record Source property will be set automatically. It will include an SQL SELECT statement containing the WHERE condition derived from the table’s Filter property.

However, keep in mind that if you later modify the filter condition on the table, the change will not be reflected automatically in the Record Source SQL of the Form or Report. Once created, the Form or Report retains the original filter condition unless you manually update it. Automating through VBA.

Like most features in Microsoft Access, you can automate this process with VBA to change the filter criteria at the click of a button. However, there is a small catch, which I’ll explain shortly, so that you understand why it’s important. After all, we’re talking about setting a simple filter condition on the Filter property of a table’s structure.

There are two ways to address the Filter property of a table in VBA.

1. Front-door approach: Accessing the property through the path TableDef.Properties("Filter").

2. Backdoor approach: Accessing it via Containers("Tables").Documents("TableName").Properties("Filter").

As you can see, the second method is more elaborate, which is why I refer to it as the backdoor method. We’ll try out examples of both approaches.

The second approach becomes especially useful when working with Forms or Reports—for example, when generating a list of all Forms or Reports, or when you want to change the Form Name. In an earlier blog post, I demonstrated how to create a user-defined (custom) property to store values, such as the last record you worked on, so that the Form can reopen at that record and allow you to continue seamlessly. [Click here to learn more.]

Example-1:

1. Copy and Paste the following VBA Code into the Database's Standard Module.

   Public Function TableFilter1(ByVal OrderStart As Long, ByVal OrderEnd As Long)
   Dim db As Database
   Dim Tbldef As TableDef
   
   Set db = CurrentDb
   
   Set Tbldef = db.TableDefs("Order Details")
   Tbldef.Properties("Filter").Value = "[Order ID] >=" & OrderStart & " AND [Order ID] <=" & OrderEnd
   Tbldef.Properties("FilterOnLoad").Value = True
   db.TableDefs.Refresh
   
   End Function

2. Run the above sample Code from the Debug Window or from a CommandButton Click Event Procedure, like the sample run given below:

TableFilter1 35,45

NB: The above code and sample run are demonstrated using the Order Details table in Access 2007.
If you are working with an earlier version of Access, make the following adjustments:

• Change the field name from [Order ID] to [OrderID] (without the space).

• In the sample run, use:

TableFilter1 10249, 10251

instead of the values 35, 45 for the OrderID range.

If you have not tried out the manual filter method explained above or removed the filter criteria setting of the Filter Property then you will run into problems with the above program reporting an Error message stating that the Filter Property not found.

When you implement the VBA method see that an initial criteria setting is set in the Filter property of the Table.  Without the criteria setting the Filter Property will not be visible in VBA.

Example-2:

Copy and paste the following VBA Code into the Standard VBA Module and run the Code in the same way as Example-1 with a different set of OrderIDs:

Public Function TableFilter2(ByVal OrderStart As Long, ByVal OrderEnd As Long)
Dim db As Database, ctr As Container, doc As Document

Set db = CurrentDb

Set ctr = db.Containers("Tables")
Set doc = ctr.Documents("Order Details")
doc.Properties("Filter").Value = "[Order ID] >=" & OrderStart & " AND [Order ID] <=" & OrderEnd
doc.Properties("FilterOnLoad").Value = True
doc.Properties.Refresh

End Function

Technorati Tags: Microsoft

• Percentage of Report Summary
• Embedded Macros in Access 2007
• Report Design in Access 2007
• Memo Field Text Formatting
• Sub-Query in Query Column Expression

Read More

Dynamic Dlookup in Query Column

Dynamic DLookup in a Query Column.

If we need only a single value in a Query Column from a related Table, we often use the DLookup() function.

Normally, the standard approach is to add the related table to the query design surface.  Establish a relationship between the Primary Key of the main table and the Foreign Key of the other table, and then pull the required field value into the query column.

However, in certain cases, this method can lead to complications. To illustrate, let’s look at an interesting scenario where DLookup() is used in a query column.

Suppose we use the following expression to pick up the Sales Tax Rate for each sold item from the tblTaxRates table, so that the tax value can be calculated on the sales amount:

DLookup("TaxRate2005", "tblTaxRates", "[Product Code] = '" & [ProductCode] & "'")

Here’s the catch: the tax rate changes every year as part of the government’s annual budget revisions. The company maintains a history of these changes by adding a new tax rate field for each year, with the year as a suffix—e.g., TaxRate2005, TaxRate2006, TaxRate2007, and so on.

A sample image of the tblTaxRates table is shown below:

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

In the Query, we assign a consistent column name, TaxRate, and insert the DLookup() Function as its expression. For example:

TaxRate: DLookup("TaxRate2005","tblTaxRates","[Product Code]='" & [ProductCode] & "'")

Here, the function may refer to TaxRate2005, TaxRate2006, or TaxRate2007, depending on the year being processed. But the query output column name remains the same: TaxRate.

This consistency is useful when designing reports. On the report, we can place a control bound to the TaxRate field. Since the query always produces a column named TaxRate, the report design does not need to be modified each time the tax year changes.

Check the sample Query result image given below:

Right-click to open the large image in a New Tab/Window.

Applying Different Tax Rates

Now, consider a practical scenario:

• Some orders were placed in December, when the previous year’s tax rate still applied.

• Other orders were placed after the budget, when the new tax rate came into effect.

When preparing invoices, this means:

• Older invoices must use the TaxRate2005 field.

• Newer invoices must use the TaxRate2006 field.

In other words, the first parameter of the DLookup() function needs to change each time:

DLookup("TaxRate2005","","")
DLookup("TaxRate2006","","")

(The table name and criteria are omitted for simplicity.)

Obviously, we cannot expect the user to open the query design and manually update the field name every time they need to print invoices from a different year.

The solution is straightforward:

1. Create a small Form with a Text Box (or, better yet, a Combo Box that lists available tax rate fields).

2. Provide a CommandButton on the form.

3. The user enters (or selects) the required tax rate field name and clicks the button.

4. The code behind the button dynamically updates the query, refreshes it, and then opens the Report that depends on it.

This approach lets the user control which TaxRate field the DLookup() uses, without ever touching the query design.

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

The Textbox name is Tax on the Form.  Our DLookup() function in the Order Details Query column will look as given below:

TaxRate:DLookup([Forms]![InvoiceParam]![Tax], _
”tblTaxRates”,”[ProdCode]= '” & [Product Code] & “'”)

In the criteria, the ProdCode field of the tblTaxRate table should match the (Product Code) field of the Products Table linked to the Order Details Table to return the tax rate for each item on the Order Details table.

The sample SQL of the Query.

SELECT [Order Details].[Order ID],
 Products.[Product Code],
 Products.[Product Name],
 [Order Details].Quantity,
 [Order Details].[Unit Price],
 [Quantity]*[Unit Price] AS SaleValue,
 Val(DLookUp([Forms]![InvoiceParam]![Tax],"TaxRates", _
 "[ProdCode]='" & [Product Code] & "'")) AS TaxRate,
 [SaleValue]*[TaxRate] AS SaleTax,
 [SaleValue]+[SaleTax] AS TotalSaleValue
FROM Products INNER JOIN [Order Details] _
ON Products.ID = [Order Details].[Product ID];

The CommandButton Click Event Procedure can run the following code to open the Sales Invoice Report after refreshing the change on the Form:

Private Sub cmdRun_Click()
Me.Refresh
DoCmd.OpenReport "SalesInvoice", acViewPreview
End Sub

Technorati Tags: Microsoft

• Embedded Macros in Access 2007
• Report Design in Access 2007
• Memo Field Text Formatting
• Sub-Query in Query Column Expression
• GetRows() Function and Exporting Data

Read More

Creating Watermark on MS-Access Reports

Creating Watermarks in MS Access Reports.

No matter what kind of database design you create for your company, the ultimate output that external users expect is reports—whether in text format or as charts. Charts can be presented in simple 2D Designs, or in more polished 3D designs with gradient colors. While appearance can make reports more attractive, what truly matters is that the information presented is meaningful and serves its purpose.

Think of it like a song: the lyrics may be the same whether sung by an amateur in the bathroom or by a professional singer, but the audience will always prefer the professional version. The same principle applies to reports.

You can throw together a report in a few minutes. Or you can carefully design it, paying attention to every detail—control placement, sizing, font style, highlighting, headings, footers, and summary lines. This may take hours of refinement, but the result is worth it when you present the report and see your boss’s nod of approval.

Now, speaking of controls and enhancements—why not take it one step further? For instance, you can add a watermark (such as a company logo or name) to the report’s background. This small touch gives your report a professional polish. If you have a light grayscale image of your company logo (in .bmp format), you can easily print it as a watermark to enhance the presentation.

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

Incorporating a Watermark on the Report.

1. Open the Report in Design View.
2. Display the Report's Property Sheet (F4).
3. Look for the Picture Property and select the Property.
4. Click the Build button (...), at the right end of the property, to browse for the Watermark image on the disk and select it.
5. With the following three property settings of the Report, you can display and print the watermark image in various ways:
   • PictureAlignment = 2 (center)
   • PictureTiling = False 
   • PictureSizeMode = 3 (zoom)

When you load the Watermark picture in the background, with the above property settings, the Report Print Preview looks like the image given below:

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

Print the Report on the Printer or convert it to MS-Access Snapshot format (file extension: .snp) or into PDF.

The above work can be automated to define and assign the Watermark Image at the run-time of the Report, with the VBA Code given below:

The ReportWaterMark() Function.

Public Function ReportWaterMark()
Dim rpt As Report, txtRpt As String
Dim imgPath As String

txtRprt = ""
imgPath = ""

Do While txtRpt = "" Or imgPath = ""
  If txtRpt = "" Then
   txtRpt = Nz(InputBox("Give Report Name:", "Report Water Mark"), "")
  End If
  If imgPath = "" Then
   imgPath = Nz(InputBox("Watermark Image PathName:", "Report Water Mark"), "")
  End If
Loop

DoCmd.OpenReport txtRpt, acViewDesign

Set rpt = Reports(txtRpt)
With rpt
    .Picture = imgPath
    .PictureAlignment = 2
    .PictureTiling = False
    .PictureSizeMode = 3
End With
DoCmd.Close acReport, txtRpt, acSaveYes
Set rpt = Nothing

DoCmd.OpenReport txtRpt, acViewPreview

End Function

Copy the code, paste it into a Standard VBA Module, and save the Code.  You can run this program from a CommandButton Click Event Procedure like:

Private Sub cmdWMark_Click()
    ReportWaterMark
End Sub

When you run the main program, it will prompt you for two inputs: the report name and the watermark image location (including the file name). Be sure to note down this information before running the program. You only need to run the program once for a given report; the image will remain as the background picture, and you run it again to replace it with a different one.

Try the following property settings to see how they appear in the Report Print Preview:

.PictureAlignment = 0
    .PictureTiling = True
    .PictureSizeMode = 0

.PictureAlignment = 2
    .PictureTiling = False
    .PictureSizeMode = 1

Technorati Tags: Microsoft

• Report Design in Access 2007
• Memo Field Text Formatting
• Sub-Query in Query Column Expression
• GetRows() Function and Exporting Data
• Create Menus with Macros-2

Read More

iSeries Date in Imported Data

AS400 iSeries Date in Imported Data.

When working with an IBM iSeries (IBM AS/400) mainframe computer and importing raw data into Microsoft Access custom report preparations, an issue often arises. The date values in the data records are frequently stored as plain numbers.

These can appear in formats such as mmddyyyy (e.g., 07092011), ddmmyyyy (e.g., 09072011), or yyyymmdd (e.g., 20110709). Such values cannot be used directly as dates in queries or other analysis tasks. They must first be converted into proper date values before they can be utilized effectively.

Let us try one or two conversion examples.

Example-1: mmddyyyy

X = 07092011 or x=”07092011”

Y = July 09, 2011 (internal representation of the actual date number is 40733)

Conversion expression:

Y = DateSerial(Right(x,4),Left(x,2),Mid(x,3,2))

OR

y = DateValue(Left(x,2) & "-" & Mid(x,3,2) & "-" & Right(x,4))

Depending on the input date format (Asian, USA, or ANSI), the order in which you use the Left(), Right(), and Mid() functions within the DateSerial() or DateValue() functions will vary. However, writing these expressions repeatedly in every query is not practical and quickly becomes time-consuming. The simplest solution is to create a user-defined function with the required expression and call the Function from the query column—or from anywhere else the numeric date values are used in calculations or comparisons.

The DMY_N2D() Function.

Let us create the following simple Functions that will make our lives easier with these numbers in the long run.

Function: DMY_N2D(ByVal ddmmyyyy as Variant) As Date 

This function converts a date number in ddmmyyyy format into a valid date value. The input can be provided either as a long number (e.g., 09072011 or 9072011) or as text (e.g., "09072011"). The suffix _N2D stands for Number to Date. The first three characters in the function name (DMY) indicate the order of the day, month, and year segments in the input date number.

Public Function DMY_N2D(ByVal ddmmyyyy As Variant) As Date

'------------------------------------------------------------------
'Converts Numbers (in ddmmyyyy format) 09072011 into a Date Number
'Author : a.p.r.pillai
'Date   : Sept. 1999
'Rights : All Rights Reserved by www.msaccesstips.com
'------------------------------------------------------------------
Dim strN2D As String

On Error GoTo DMY_N2D_Err

strN2D = Format(ddmmyyyy, "00000000") ' add 0 at the left side, if 7 digit number

DMY_N2D = DateSerial(Right(strN2D, 4), Mid(strN2D, 3, 2), Left(strN2D, 2))

DMY_N2D_Exit:
Exit Function

DMY_N2D_Err:
MsgBox Err.Description,, "DMY_N2D()"
Resume DMY_N2D_Exit
End Function

The MDY_N2D() Function.

When the input Number is in mmddyyyy format:

Public Function MDY_N2D(ByVal mmddyyyy As Variant) As Date 
'------------------------------------------------------------------ 
'Converts Numbers (in mmddyyyy format) 07092011 into a Date Number 
'Author : a.p.r.pillai 
'Date   : Sept. 1999 
'Rights : All Rights Reserved by www.msaccesstips.com 
'------------------------------------------------------------------
Dim strN2D As String 

On Error GoTo MDY_N2D_Err 

strN2D = Format(mmddyyyy, "00000000") ' add 0 at the left side, if 7 digit number

MDY_N2D = DateSerial(Right(strN2D, 4), Left(strN2D, 2), Mid(strN2D, 3, 2)) 

MDY_N2D_Exit: 
Exit Function 

MDY_N2D_Err: 
MsgBox Err.Description,, "MDY_N2D()" 
Resume MDY_N2D_Exit 
End Function

The YMD_N2D() Function.

When the date number is in yyyymmdd (ANSI) format:

Public Function YMD_N2D(ByVal yyyymmdd As Variant) As Date
'------------------------------------------------------------------
'Converts Numbers (in yyyymmdd format) 20110709 into a Date Number
'Author : a.p.r.pillai
'Date   : Sept. 1999
'Rights : All Rights Reserved by www.msaccesstips.com
'------------------------------------------------------------------
Dim strN2D As String

On Error GoTo YMD_N2D_Err

YMD_N2D = DateSerial(Left(strN2D, 4),Mid(strN2D, 5, 2),Right(strN2D, 2))

DMY_N2D_Exit:
Exit Function

YMD_N2D_Err:
MsgBox Err.Description,, "YMD_N2D()"
Resume YMD_N2D_Exit
End Function

Earlier Post Link References:

• Memo Field Text Formatting
• Sub-Query in Query Column Expression
• GetRows() Function and Exporting Data
• Create Menus with Macros-2
• Create Menus with Macros

---

Read More

Change Query Top Values Property with VBA-2

Continued from Last Week's Topic.

With last week’s introduction, we have seen various ways to change the Top Value Property and other properties to change the SQL string of a SELECT Query.  Now we will learn how to redefine the Query for the Top Values and other property changes.

As I mentioned earlier, three types of queries, SELECT, APPEND, and MAKE-TABLE, only have the Top Values property.  SELECT and MAKE-TABLE queries have almost identical SQL strings, with DISTINCT, TOP nn, and PERCENT clauses appearing immediately after the SELECT clause at the beginning of the SQL String.

A sample SQL string of a MAKE-TABLE query is given below:

SELECT TOP 15 PERCENT SalesReportQ.* INTO chart
FROM SalesReportQ
ORDER BY SalesReportQ.Total DESC;

Unlike SELECT and MAKE-TABLE Queries, APPEND Queries have the Top Values property settings inserted somewhere in the middle of the SQL string immediately after the SELECT clause. Check the sample SQL of the Append Query given below:

INSERT INTO Table3 ( xID, Field1, Field2 )
SELECT DISTINCT TOP 17 PERCENT Table2.ID, Table2.Field1, Table2.Field2
FROM Table2
ORDER BY Table2.ID DESC;

Our VBA program scans through the SQL String to find the TOP Values property Clauses in the SQL String(wherever they appear), removes the existing settings, and inserts changes as per input from the User.

First, we will create a form for the User to input the Query 'Top Values' property values and click a CommandButton to redefine the SQL.

An image of a sample form is given below:

Right-click to open the large image in a new Tab or Window.

Two TextBoxes named Qry and TopVal, for Query name and Top values parameters, respectively, and a CheckBox named Unik for Unique value selection.  The Top Values TextBox can be set to a number or a percentage (15%).  If the Unik checkbox is set, then the query suppresses duplicate records based on the selected field values in the Query.

After setting the Query property values in the controls, the user should click the CommandButton to redefine the selected query SQL in the Query Name control.  The CommandButton's name is cmdRun (the Caption: Modify Query). When the Command Button is clicked, the cmdRun_Click() Event Procedure is run (the VBA Code is given below) and validates the input values in the controls above and calls the QryTopVal() function (with parameters: query name, Top Values property value, and Checkbox value) to redefine the Query based on the user inputs.

Form Module Code.

Private Sub cmdRun_Click()
Dim strQuery, strTopVal, bool As Boolean
Dim msg As String

On Error GoTo cmdRun_Click_Err

msg = ""
strQuery = Nz(Me![Qry], "")
If Len(strQuery) = 0 Then
   msg = "  *>>  Query Name not found." & vbCr
End If
strTopVal = Nz(Me![TopVal], 0)
If strTopVal = 0 Then
   msg = msg & "  *>>  Top Property Value not given."
End If
bool = Nz(Me![Unik], 0)
If Len(msg) > 0 Then
    msg = "Invalid Parameter Values:" & vbCr & vbCr & msg
    msg = msg & vbCr & vbCr & "Query not changed, Program Aborted...."
    MsgBox msg, , "cmdRun_Click()"
Else
    'Call the QryTopVal() Function to redefine the Query
    QryTopVal strQuery, strTopVal, bool
End If

cmdRun_Click_Exit:
Exit Sub

cmdRun_Click_Err:
MsgBox Err.Description, , "cmdRun_Click()"
Resume cmdRun_Click_Exit
End Sub

Copy and paste the VBA code into the Form Module and save the Form. Don't forget to name the Command Button as cmdRun.

The Main Function QryTopVal().

The main function QryTopVal() checks the validity of the Query Type (SELECT, APPEND, or MAKE-TABLE) and reads the Query SQL.  Checks for the existence of Top Values and other Property settings; if they exist, then removes them.  Redefines the query based on the Top Values and other property inputs.

Copy and paste the following VBA code for QryTopVal() into the Standard Module and save it:

Public Function QryTopVal(ByVal strQryName As String, _
                       ByVal TopValORPercent As String, _
                       Optional ByVal bulUnique As Boolean = False)
'--------------------------------------------------------------------
'Author : a.p.r. pillai
'Date   : Jun 2011
'Remarks: All Rights Reserved by www.msaccesstips.com
'Valid Query Types:
'  0 - SELECT
' 64 - APPEND
' 80 - MAKE TABLE
'--------------------------------------------------------------------
Dim strSQL1 As String, strSQL2 As String, strTopValue
Dim db As Database, qrydef As QueryDef, sql As String
Dim loc, qryType As Integer, locTop
Dim txt(1 To 3) As String, num
Dim J, xt, msg As String

On Error GoTo QryTopVal_Err

txt(1) = "DISTINCT"
txt(2) = "TOP"
txt(3) = "PERCENT"

Set db = CurrentDb
Set qrydef = db.QueryDefs(strQryName)
qryType = qrydef.Type

If qryType = 0 Or qryType = 64 Or qryType = 80 Then
   xt = qrydef.sql

   GoSub ParseSQL

   loc = InStr(1, TopValORPercent, "%")

   If loc > 0 Then
      TopValORPercent = Left(TopValORPercent, Len(TopValORPercent) - 1)
   End If

   If Val(TopValORPercent) = 0 Then
      sql = strSQL1 & strSQL2
   Else
      sql = strSQL1 & IIf(bulUnique, "DISTINCT ", "") & "TOP " & TopValORPercent & IIf(loc > 0, " PERCENT ", "") & strSQL2
   End If

   qrydef.sql = sql
   msg = "Query Definition of " & strQryName & vbCr & vbCr & "Changed successfully."
   MsgBox msg, , "QryTop()"
Else
   msg = strQryName & " - Invalid Query Type" & vbCr & vbCr
   msg = msg & "Valid Query Types: SELECT, APPEND and MAKE-TABLE"
   MsgBox msg, , "QryTop"
End If

QryTopVal_Exit:
Exit Function

ParseSQL:
For J = 1 To UBound(txt)
  xt = Replace(xt, txt(J), "", 1)
Next
  
  locTop = InStr(1, xt, "SELECT")
  num = Val(Mid(xt, locTop + 7))
  num = " " & Format(num) & " "
  strSQL1 = Left(xt, locTop + 7)
  xt = Right(xt, Len(xt) - (locTop + 7))
  xt = Replace(xt, num, "", 1, 1)
  strSQL2 = " " & xt
  locTop = InStr(1, strSQL2, "ORDER BY")
  If locTop = 0 Then
    MsgBox "ORDER BY Clause not found in Query.  Result may not be correct.", , "QryTopVal()"
  End If
Return

QryTopVal_Err:
MsgBox Err & " : " & Err.Description, , "QryTopVal()"
Resume QryTopVal_Exit

End Function

You may try the Code with sample Queries.

• Sub-Query in Query Column Expression
• GetRows() Function and Exporting Data
• Create Menus with Macros-2
• Create Menus with Macros
• Join Split Array Function-2

Read More

Change Query Top Values Property with VBA

Change Query Top Values Property with VBA.

We have already learned how to use the Top Values property of Queries (applicable in SELECT, MAKE-TABLE, and APPEND queries) in an earlier post. If you haven’t seen it yet, I recommend taking a look at that article first [link here].

The Top Values property of a Query can only be set manually during design time. Unfortunately, you cannot directly change its value dynamically in VBA. You also cannot expect users to open the query in Design View each time they want a different set of results.

So, how do we handle situations where we need flexibility with these values?

Sample SQL with TOP Property Setting.

When we manually change the Top Values property, Microsoft Access automatically updates the SQL  Query statement to reflect the change. Keeping this behavior in mind, we can use a little VBA trickery to manipulate the SQL directly, rather than searching a property setting that doesn’t exist.

Before we move on to that approach, let us first examine what actually happens to the SQL definition of a Query when you set the Top Values property or other related properties.

Here is a sample SELECT Query with the Top Values Property value set to 25.

SELECT TOP 25 Orders.OrderID, Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate, Orders.Freight
FROM Orders
ORDER BY Orders.Freight DESC;

As shown in the example, when the Top Values property is set to 25, Access automatically inserts the text TOP 25 immediately after the SELECT clause in the SQL string. This indicates that the Query will return only 25 records. In the ORDER BY clause, the Freight column is sorted in descending order, so the output consists of the 25 records with the highest freight values in the table.

If, instead of a fixed number of records, you want a percentage of the total records—for example, 25%—then the Top Values property must be set to 25% rather than 25. In this case, the SQL text changes accordingly to:

SELECT TOP 25 PERCENT Orders.OrderID, Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate, Orders.Freight

FROM Orders
ORDER BY Orders.Freight DESC;

The next property that affects the record set is the Unique Values property (valid values: Yes or No). When this property is set to Yes, Access suppresses duplicate records in the output. However, it evaluates only the fields included in the Query’s column list—other fields from the table that are not selected are ignored when checking for duplicates.

When this property is enabled, Access inserts the keyword DISTINCT in the SQL, immediately after the SELECT clause. The modified SQL will look like this:

SELECT DISTINCT TOP 25 PERCENT Orders.OrderID, Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate, Orders.Freight
FROM Orders
ORDER BY Orders.Freight DESC;

Another property you can set in a query is Unique Records (valid values: Yes or No). When this property is set to Yes, Access suppresses duplicate records in the output. Unlike the Unique Values property, this setting evaluates all fields in the source table, regardless of whether they are included in the query’s column list.

When enabled, the SQL changes by replacing the DISTINCT keyword with DISTINCTROW. This ensures that the uniqueness check is applied across the entire underlying table.

For example:

SELECT DISTINCTROW TOP 25 PERCENT Orders.OrderID, Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate, Orders.Freight
FROM Orders
ORDER BY Orders.Freight DESC;

We will exclude the Unique Records property from our VBA-based solution. As shown in the examples above, depending on the user’s requirements, we can dynamically add or remove any of these three elements—DISTINCT, TOP n, or PERCENT—to control the query output for reports.

Preparing for the VBA-based Solution.

Our methodology for modifying the SQL is straightforward. We will collect the required Query property values from the user in a TextBox and a CheckBox on a Form. Once the user enters the input and clicks a CommandButton, the SQL will be redefined. This will invoke the following actions to redefine the Query:

1. Open the Query Definition and read the existing SQL String.

2. Scan the SQL string to check for the key text DISTINCT, TOP nn, and PERCENT.  If found, then remove them from the SQL String.

3. Validate the input given by the User in the Textbox and checkbox, and insert appropriate SQL Clauses in the SQL String.

4. Update the modified SQL in the Query definition.

This article has become too long now.  Explaining the above four steps and introducing the VBA Routines may make it even longer.  We will complete this topic in the next blog post.

Earlier Post Link References:

• GetRows() Function and Exporting Data
• Create Menus with Macros-2
• Create Menus with Macros
• Join Split Array Function-2
• Join Split Array Function

Read More