LEARN MS-ACCESS TIPS AND TRICKS

Word Mail-Merge with Ms-Access Table

a.p.r. pillaiFebruary 17, 2020Access Emails and Alerts, Access Fields, Access How Tos, Access Mail Merge, Database Records No comments

Word Mail Merge with MS Access Table.

Form Letters.

Form letters are prepared from a standard Microsoft Word template, with addressee-specific information inserted into each letter. Appointment letters, interview call letters, employment contracts, and similar documents are typical examples. Preparing such letters using Microsoft Word’s Mail Merge feature is much easier than composing each letter individually for every addressee.

These types of letters contain common body text that applies to all recipients, along with specific areas where personalized information can be inserted, such as the recipient’s Name and Address, interview date, or the nearest location where a candidate can attend the interview.

If interview call letters need to be sent to hundreds of prospective candidates, we need to prepare only a single template letter containing the standard body text applicable to all recipients. Other personalized information can then be automatically inserted from the associated data table at the appropriate locations in each letter during printing. In Microsoft Word, this process is known as Mail Merge.

We have already tried to replicate this process in Microsoft Access using VBA some time ago. The links to those posts are provided below for your reference:

The Names, Addresses, and other related information of each individual can be kept in a data table. The external Table can be prepared in any of the following Applications:

MS Word Document with Table Grid.
MS Excel Data Table.
MS Access Table.

The Table can be linked to the Form Letter so that MS Word can pick the required information from the Table and insert it where table field codes are placed in the body of the letter.

Sample MS Word Table.

We have prepared a small Table in an MS Word Document within the Table Grid, as an example in case you prefer to prepare one in Microsoft Word itself. Here is an image of the data table with only 3 entries, similar to the one in the Northwind Employees Table.

Click on the Image to Enlarge.

MS Access Employees2 Table.

But we will be using a modified Employees Access Table prepared with a few new fields for our Mail Merge Demo Run, taken from the original Employees Table in the Northwind sample database. The image of the Employees2 Access Table is given below:

Click on the Image to Enlarge.

Create the Employees2 Table from the Northwind Employees Table, with the selected fields in your Database. Create three more fields and fill them with sample data as shown in the above image.

Sample Letter Specimen.

A Specimen image of the Letter, without the individual Employee details, is given below:

Click the Image to enlarge it.

Create a Letter in Microsoft Word similar to the specimen above, and save it as Employees.

Sample Merge-Printed Letter.

When we merge-print the personal information in the Document, from the Access Table, it will look like the sample image given below:

Click the Image to Enlarge.

Inserting the Merge Fields in the Document.

Let us prepare a Mail-Merge Document in MS Word, using the Employees2 Table as Source Data, matching the above-merged letter model.

We assume that the above employees of our organization are awarded an annual increment in recognition of their commendable contributions to the company’s overall growth and to encourage them to perform better by taking on future challenges.

Open the Employees Word Document, as per the above specimen, created and saved earlier. If you have not yet created one, let us do it now.
Click on Start Mail-Merge from the Mailings Menu and select Letter.
Click on Select Recipients and select Use Existing List.
Find the Database where you saved the Employees2 Table and click Open.
The list of Tables is displayed; select the Employees2 Table, and click OK to select it, and the Employees2 table is attached to the Document.
Next, click Edit Recipient List to display all records in the Employees2 table. A checkmark indicates that an employee record is selected for inclusion in the merged letters. If an employee should be excluded from the letters, clear the checkmark for that record.

First, we will insert the Address Block and Greeting Line information into the letter using the Merge Fields selected from the attached table. Although these elements can be inserted in two simple steps using the automatic options, we will take a more manual approach to customize the output and avoid complications if the automatic method is not fully understood by the first-time user. You can try the automatic method later by selecting the Address Block and Greeting Line buttons and experimenting with their available options to modify the document output.
Click below the Reference information (Ref: ABC/HRD/2020/1234-) and above the word Dear on the Letter.
Click on the Insert Merge Field option in the Mailings Menu and click on the First Name Field. Open the same list again and select the Last Name.
Click in the middle of both field codes and tap the space bar to insert a space between the FirstName and LastName segments of the Employee Name and position the insertion point on the following line.
Select Address from the Merge-Field list for the second line.
Select City for the third address line.
Insert State/Province and Country/Region on the next line, with a space in between.
Position the insertion point after the word Dear with a space, and insert the First Name and Last Name fields with a space in between.
After inserting the merge fields, it will look like one of the images below, based on the Field-Code display option setting ALT+F9.
OR
The second image is the expanded form of Merge Field Codes. You can toggle between these two states by pressing ALT+F9. See that the Preview Result button is in the deselected state. If the Preview Result button is active, then the actual data will be displayed.
Position the insertion point on the Designation line and insert the Job Title merge field.
On the next three lines, insert Pay, Grade, and Wef (stands for with-effect-from) Fields.
When completed, it will look like the following image:
We need two more pieces of data for the Reference Sequence Number and Letter Date.
Position the insertion point at the end of the existing reference number, immediately after the dash character (Ref: ABC/HRD/2020/1234-).
Select Page Number from the Insert Menu and select Current Position from the displayed list.
Position the insertion point after the word Date:.
Click on the Date & Time option, under the Insert Menu, and select one of the date format options displayed.
Press Ctrl+S Keys to save the Word Document with the changes.

Merge-Printing the Letter.

Now, we are ready to merge-print the Document for each selected employee on the Employees2 Table.

Click the Preview Results button, with the Zoom Glass image, under the Mailings Menu.
If it is still on Field-Code display, then press ALT+F9 Keys to fill in the first Employee data wherever we have inserted the Merge-Fields on the Document.
Now, you are viewing the sample Document you will get when you merge-print the Document, on the Printer, one Document per employee.
Now, click the Finish & Merge button and select Edit Individual Document..., and select All.

All the Documents are merged with Employee information, and you are allowed to edit the pages if needed. Can insert spaces between lines or the inserted information in Bold letters.

Once you are finished editing, select Print from the Office Button, then Preview or Print the Document on Paper.

If you have selected Send Email Message at Step 4 above, then you can send the Document as an Email Message.

Auto-Numbers in Query Column Version-2

a.p.r. pillaiJanuary 02, 2020Collection Object, Downloads, Functions, How Tos, Key, Query, Recordset, Reports No comments

Auto-Numbers in Query Column Version 2.

In January 2010, I published an article, “Function: QrySeq() – Auto-Numbering in Query Column,” on this website. Over the years, it has been well received by readers. While reviewing the article again, I thought the Function could be rewritten with less code and improved performance than a variant Array.

When the QrySeq() function is called for a record in the query, the program searches the Array of Unique Keys for the key value passed from the record as a Parameter. Once it finds a matching key, it retrieves the corresponding sequence number from the Array element and returns it to the calling record.

If the query contains a large number of records, this process can take considerable time because the program begins the search from the start of the Array each time it looks for a key value.

The New Version is Named: QryAutoNum()

Using a Collection Object instead of an Array.

You can find a detailed discussion of the Collection Object on the MS Access and Collection Object Basics Page.

Here, we will have a brief introduction to the Collection Object, including what it is and how it is used in VBA. The Collection Object is a versatile Object that can generally hold any type of value, including numeric or string values, Class Module Objects, or a collection of other Objects. The Collection Object is instantiated in VBA programs as follows:

'declare a Collection Object. Dim ABC as Collection 'create an instance of Collection Object in Memory Set ABC = New Collection 'We can Add built-in data types: Numeric, Strings etc ‘or Objects like Class Module Objects, ‘or other Collection Object as Items to the Collection Object.

'Use the Add method to add a Collection Item to the Object. ABC.Add 25 ABC.Add "ms-accesstips" 'When Collection Object Items added this way, ‘it can be retrieved only in the added order. For j = 1 to ABC.Count 'gets the count of Items Debug.Print ABC(J)’ retrieve in Item index Order. Next 'When ADDed an Item with a String Key 'we can use the Key value to retrieve the Items Randomly. 'But, usage of Key is optional. ABC.Add 25, "1" ABC.Add "ms-Accesstips", "2" x = "2" Debug.Print ABC(x) Result: ms-accesstips

We will use the Collection Object to store the Query Auto-numbers, with the corresponding Unique Key Values assigned as the Collection Object Keys. With this approach, we can retrieve the Auto-numbers directly, eliminating the need to work with Arrays and their more complicated data storage and retrieval steps.

The QryAutoNum() Function Code.

Option Compare Database
Option Explicit

Dim C As Collection

Public Function QryAutoNum(ByVal KeyValue As Variant, ByVal KeyfldName As String, ByVal QryName As String) As Long
'-------------------------------------------------------------------
'Purpose: Create Sequence Numbers in Query Column Ver.-2
'Author : a.p.r. pillai
'Date : Dec. 2019
'All Rights Reserved by www.msaccesstips.com
'-------------------------------------------------------------------
'Parameter values
'-------------------------------------------------------------------
'1 : Column Value - must be UNIQUE Numeric/String Type Values from Query Column
'2 : Column Name  - the Field Name in Quotes from where Unique Values taken
'3 : Query Name   - Name of the Query this Function is Called from
'-------------------------------------------------------------------
'Limitations - Function must be called with Unique Field Values
'            - as First Parameter
'            - Need to Save the Query, if changes made, before opening
'            - in normal View.
'-------------------------------------------------------------------
Static K As Long, Y As Long, fld As String
On Error GoTo QryAutoNum_Err

Y = DCount("*", QryName) ' get count of records for control purpose

'If KeyfldName Param is different from saved name in variable: fld
'or Value in K more than count of records in Variable: Y
'then it assumes that the QryAutoNum() is called from a different Query
'or a repeat run of the same Query. In either case the Control Variable
'and Collection Object needs re-initializing.
If KeyfldName <> fld Or K > Y Then
'initialize Control Variable
'and Collection Object
    K = 0
    Set C = Nothing
    'save incoming KeyfldName
    fld = KeyfldName
End If

'if KeyValue parameter is Numeric Type then convert
'it to string type, Collection Object needs it's Key as String Type.
If IsNumeric(KeyValue) Then
    KeyValue = CStr(KeyValue)
End If

K = K + 1
If K = 1 Then
Dim j As Long, db As Database, rst As Recordset
Dim varKey As Variant

Set C = New Collection

Set db = CurrentDb
Set rst = db.OpenRecordset(QryName, dbOpenDynaset)

'Add recordlevel AutoNumber with Unique KeyValue
'to Collection Object, in AutoNumber, KeyValue Pair
While Not rst.BOF And Not rst.EOF
    j = j + 1 ' increment Auto Number
    
    'Get key value from record
    varKey = rst.Fields(KeyfldName).Value
    
    'if numeric key convert it to string
    If IsNumeric(varKey) Then
      varKey = CStr(varKey)
    End If
    
    'Add AutoNumber, KeyValue pair to Collection Object
    C.Add j, varKey
    
    rst.MoveNext
Wend
    rst.Close
    Set rst = Nothing
    Set db = Nothing

'Retrieve AutoNumber from Collection Object
'using the KeyValue.  Works like Primary Key of Table
    QryAutoNum = C(KeyValue)
Else
    QryAutoNum = C(KeyValue)
End If

If K = Y Then 'All record level AutoNumbers are Returned
    K = K + 1 ' increment control variable
End If

QryAutoNum_Exit:
Exit Function

QryAutoNum_Err:
MsgBox Err & " : " & Err.Description, , "QryAutoNum"
Resume QryAutoNum_Exit

End Function

Sample Source Query SQL.

With the Northwind Products Table.

SELECT Products.ID, 
Products.Category, 
Mid([Product Name],18) AS PName, 
Sum(Products.[Standard Cost]) AS StandardCost, 
QryAutoNum([ID],"ID",
"Product_AutoNumQ") AS QrySeq
FROM Products
GROUP BY Products.ID, Products.Category, Mid([Product Name],18)
ORDER BY Products.Category, Mid([Product Name],18);

Review of VBA Code Line-By-Line.

In the Global Area of the Module, we have declared a Collection Object, the Object Variable C.

The QryAutoNum() Function declaration is the same as our earlier QrySeq() with three parameters.

Unique Key-Value, either Numeric or String, as the first Parameter.
The Key-Value Field’s Name in String Format.
The Query Name in String Format.

The returned AutoNumber is in a Long Integer format.

Three Static Variables, K and Y, are declared as Long Integers, and fld was declared as a String Variable.

All three Variables control the Code execution Paths, and determine when to initialize Collection objects and control variables.

The DCount() Function takes a count of records in the Query in Variable Y.

If the KeyFldName differs from the previously saved field name in the fld variable, the function assumes that the call originates from a new Query Record. If the field name is the same, but the value of the variable K is greater than Y, the function assumes that the earlier Query is calling QryAutoNum() again as part of a repeated run. In either case, the control variable K is reset to zero, and the Collection Object containing the existing Items is cleared from memory. The new Key field name from the KeyFldName variable is then saved in the fld variable for subsequent validation.

Next, if the KeyValue parameter is numeric, it is converted to String format using the statement KeyValue = CStr(KeyValue). The Item Key of the Collection Object must be in String format.

The variable K is then incremented by one. When K = 1, the function assumes that this is the first call to the function, originating from the first record of the Query. In this case, the main processing of the function begins.

The local temporary Variables are declared here, and their values are not preserved between calls of this function from different records of the query.

The Collection Object declared in the Standard Module’s Global area is instantiated in memory with the statement Set C = New Collection.

The Query recordset is opened to read records. The local variable J will create Auto-numbers and add them to the Collection Object for each record. The Unique Key-Value, read from the recordset into variable varKey, is added to the Collection Object as its Key Value.

If the varKey variable value is Numeric Type, then it is converted to String format.

The Auto-Number Value in Variable J and the string value in variable varKey are added to the Collection Object in the following statement, as its Item value, Key pairs:

C.Add J, varKey

This process is repeated for all the records in the Query. The Auto-Numbers are generated for all records and added to the Collection Object. All this work is done during the first call of the function from the first record of the query.

Did you notice that we are reading the Unique Key value of each record directly from the record set within the While . . . Wend Loop to add them to the Collection Object? After adding the Auto-Numbers for all records, the record set and Database Objects are closed.

Remember, we are still on the first call of the function from the first record of the query, and the first parameter variable KeyValue still holds the first record Key Value.

The next statement QryAutoNum = C(KeyValue) retrieves the Collection Object’s first Item Auto-Number Value 1, using the Unique Key Value in parameter variable KeyValue, and returns it to the function calling record. This will happen only once because the variable K will be greater than one on subsequent calls of this function.

Function calls from the second record onward take the Else path of the If K = 1 Then statement. They retrieve the AutoNumber from the Collection Object using the KeyValue passed as a parameter and return it to the corresponding record in the Query.

This process is very fast because the required Item can be retrieved directly using the Collection Object Key, rather than searching through the Array from the beginning each time to locate the required Key.

When the Auto-number values for all records have been returned, the value of the control variable K equals Y. The record count of the Query was obtained and stored in the variable Y at the beginning of the program. At this point, K is incremented by 1, making its value greater than Y. Because K and Y are Static Variables, their values are retained in memory after the last record call has completed. If the same Query is run a second time, these variable values can be tested to determine whether the variables need to be reset and the existing Collection Object cleared from memory, allowing the entire process to start afresh.

If the QryAutoNum() function is called from the same Query again, the Static Variables and Collection Object are cleared from memory, preparing for a fresh run of the Function for the same Query or for a different Query.

The sample Report image, generated using the above Query, is shown below for reference.

You can use the Query as the source for a Report or Form.

A sample demo database is attached.

Download AutoNumQuery2007.zip

Diminishing Balance Calc in Query

a.p.r. pillaiDecember 18, 2019Calculation, Dictionary Object, Downloads, Functions, Objects, Query, VBA No comments

Diminishing Balance Calc in Query.

This is an offshoot of the earlier Function Running-Sum in Query Column. With a few changes to the earlier Function RunningSum(), we can easily calculate the loan balance-to-pay amount after deducting the monthly amount paid at each record level.

The loan amount is payable in monthly installments. Our simple task is to show the diminishing balance of the loan amount against each record-level installment amount in a separate Column of the Query. The last record will have the remaining balance amount for payment.

Let us pretend that the Loan Repayable Total Amount is 1000.

Sample Query Recordset.

The sample installment payment detail records are taken from the earlier Post: Running-Sum in Query Column, as given below.

The Query SQL that calls the DiminishingBal() Function.

The SELECT Query SQL that calls the DiminishingBal() Function, in a separate Column of the Query.

SELECT Table_Units.ID, Table_Units.Units, DiminishingBal([ID],"ID","Units","DiminishingQ1") AS DiminishingBalance
FROM Table_Units;

The Query Recordset Image, with the result in the last column, is given below:

We are using the same Query recordset as the source for the earlier RunningSum() Function here for demonstration purposes. The Recordset should have a Unique value (Numeric or String) field and be used as the first parameter to the Function.

The Total Repayable Loan Amount is kept in a separate Table.

The Total Amount to be repaid to the Bank (1000) is kept in a separate Table with the following structure:

The DiminishingBal() Function VBA Code.

The VBA Code of the DiminishingBal() Function is given below:

Option Compare Database
Option Explicit

'Declare a Generic Object
Dim D As Object

Public Function DiminishingBal(ByVal IKey As Variant, ByVal KeyFldName As String, ByVal SumFldName As String, ByVal QryName As String) As Double
'-----------------------------------------------------------
'Function: DiminishingBal()
'Purpose : Calculate Diminishing Balance in a separate Column
'The Query can be used as source for other Processing needs,
'for Form View or Report
'-----------------------------------------------------------
'Author  : a.p.r. pillai
'Date    : December 2019
'Rights  : All Rights Reserved by www.msaccesstips.com
'-----------------------------------------------------------
'Parameter List, in the Order of it's placement
'1. Key Value Data Field
'2. Key-Field Name in String Format
'3. Field-Name for Calcuating Running Sum in String Format
'4  Query-Name in String Format
'-----------------------------------------------------------
'Remarks: The Key-Value Field should have Unique Numeric or
'String Values.
'-----------------------------------------------------------
Static K As Long, X As Double, fld As String, y As Long
Dim p As Variant

On Error GoTo DiminishingBal_Err

y = DCount("*", QryName)
'If the Function is not called by the same Query
'then initialize Dictionary Object and Variables
If SumFldName <> fld Or K > y Then
   fld = SumFldName
   Set D = Nothing
   K = 0
   X = 0
End If


K = K + 1
If K = 1 Then 'The major process of the function starts here
    Dim DB As Database, rst As Recordset
    
    'Create and instantiate the Dictionary Object
    Set D = CreateObject("Scripting.Dictionary")
    
    'Get Loan Repayable Amount
    X = DLookup("[LoanAmt]", "tblRepay", "[id] = 1")
    
    'Open the EMI Recordset
    Set DB = CurrentDb
    Set rst = DB.OpenRecordset(QryName, dbOpenDynaset)
    'Calculate cumulative record-level summary and
    'add the value into Dictionary Object as it's Item
    While Not rst.EOF And Not rst.BOF
    'read the record summary field value and add it to total
         X = X - rst.Fields(SumFldName).Value
    'read current record key field value
         p = rst.Fields(KeyFldName).Value
    'add the total value to dictionay object
    'as Key, Item pair
         D.Add p, X
    ' repeat this process for all records
         rst.MoveNext
    Wend
    
    'close recordset and remove the database objects
    rst.Close
    Set rst = Nothing
    Set DB = Nothing
    
    'Retrieve the first item from Dictionary,
    'using the first Key passed as parameter,
    'and return to the function calling record in the Query
    
   DiminishingBal = D(IKey)
Else
   'Subsequent calls with the record Key passed as parameter
   'will retrieve other record values from Dictionary and
   'returns to their corresponding records in the Query.
   
   DiminishingBal = D(IKey)
End If

'A control forcing to initialize the static variables
'when the program is rerun for the same query.
   If K = y Then
      K = K + 1
   End If

DiminishingBal_Exit:
Exit Function

DiminishingBal_Err:
MsgBox Err & ":" & Err.Description, vbOKOnly, "DiminishingBal()"
Resume DiminishingBal_Exit
End Function

How the Function Works.

In the Global declaration area of the VBA Module, Variable D is declared as an Object.

The DiminishingBal() Function needs four parameters:

A Unique Value Field (Numeric or String Values) is the first parameter. The parameter is declared as a Variant data Type.
The Unique Value’s Field Name is the second parameter in String format.
The Loan Installment Value Field Name.
The Query Name is the fourth Parameter.

Four Static Variables K, X, fld, and y are declared. They must be Static Variables to retain their values between repeated calls of the Function, from each record of the Query. The Variable p is declared as a Variant Type normal variable to hold the Key-Value (either Numeric or String) of each record.

The DCount() Function takes the Query record count in Variable y. The Value in this Variable is used as a control to check when to reset the Static Variable Values to their initial Values and to remove the Dictionary Object from memory. This control is necessary if the same Query is run more than once, consecutively.

If the control variable K value is more than the Query record count variable y, then the Static variables are reset to their initial values and the Dictionary Object is deleted from memory.
Or, if the installment value Field Name is different from the field name saved in Variable fld during the last call of the function, then it assumes that the Function is called from a different Query Column and resets the Static Variable Values. The Dictionary object is deleted from memory.

Next, Variable K is incremented by 1. When K=1, the main action of the Function starts. The Database and Recordset Objects are declared in the DB and the rst variables, respectively.

In the next executable statement, Set D = CreateObject("Scripting.Dictionary") creates the Dictionary Object with the CreateObject() method and assigns it to the Object variable D, which was declared in the Global Area of the Module.

There are other ways to declare and use this Object by adding the Microsoft Scripting Runtime File to the Microsoft Access Reference Library List. After that, you can create an instance of the Dictionary Object in the following way:

Dim D As Dictionary
Set D = New Dictionary

If you are new to the Dictionary Object and its usage, visit the Post: Dictionary Object Basics. There are five Posts on this Topic, and you can find the links at the end of this Page.

Next, we need the value of the repayable total Loan Amount and retrieve it from its Table tblRepay with the dlookup() function in the statement: X = DLookup("[LoanAmt]", "tblRepay", "[id] = 1"). There is only one record in the Table with ID 1, so omit the criteria part.

The Query Recordset is open to read records one by one. The first record’s amount paid to the bank is deducted from the Loan Amount (1000) in Variable X. The Unique Key value of the record is retrieved and saved in Variable p in the next statement.

The balance loan amount, calculated after deducting the repaid Amount, is added to the Dictionary Object with the Dictionary Object’s Add Method as its Item Value, with the Unique Key field value in the variable p as Dictionary-Key in the statement: D.Add p, X. The Dictionary’s Add method always adds its Item value in Key, Item pairs.

Note: If the Key-Value is not unique, the Add method fails with Errors.

With the rst.MoveNext statement moves to the next record and adds the result value to the Dictionary Object.

This way, the individual record value is deducted from the remaining loan balance amount at that level and added to the Dictionary Object as its Item.

Note: Here, you may ask why the Dictionary Object is chosen to hold all the calculated values rather than in an Array. Yes, it can be done, but that method needs more statements to store and retrieve the values in a two-dimensional array. It will become more complicated when the Query Record’s Unique Key Value is in String form. The Dictionary Object allows you to retrieve the value in either sequential or random order based on its Key. Here, the Random method works fine with the Key-Value Type in the Numeric or String form.

When all the record processing is complete, the record set and Database Objects are closed.

What you have to keep in mind at this point is that the value in variable K is still 1, and the first Parameter IKey retains the unique ID value. At the first record-level function call, DiminishingBal() calculated all record-level balance loan amounts and added them as Dictionary Object Items. The function parameter IKey still holds the first record’s Unique ID value. That is why we have used a separate variable p for the individual record key during the processing of all records.

So, the entire record-level processing is done during the first call of the function, initiated from the first Query record, and all record-level result values are held in temporary storage in the Dictionary Object.

The next statement DiminishingBal = D(IKey) retrieves the first value, adds it to the Dictionary using the unique parameter value IKey, and returns the value to the calling first record of the Query.

The next call from the second record of the Query increments the variable K by 1 (now K=2), and the program takes the ELSE path of the IF...Then the statement retrieves the second Item value from the Dictionary Object, using the IKey parameter, and returns it to the corresponding Query record.

The rest of the DiminishingBal() function call, from the remaining records, will route the program control only through the ELSE path because the value in Variable K is greater than one, retrieves the values from the Dictionary Item, and returns it to the calling record.

The Next If . . . Then statement checks whether the value in variable K = y. Variable y holds the total record count of the Query. If it is found to be True, then it assumes that the last call of the DiminishingBal() function has arrived. At this point, the K variable is made greater than the control variable y.

This is necessary to initialize the Static Variables during the Query rerun. If any change is made to the Source Data before the rerun, it will not be reflected in the balance amount calculated earlier, because it will keep taking the ELSE route in the If ... Then statement and retrieve the old value from the Dictionary Object.

The Demo Database, with all the necessary Objects and the Code Module, is attached for your convenience to download and try it out straight away.

Download DiminishingBalance.zip

Download DiminishingBalance2003.zip

Dictionary Object Links.

Opening Access Objects from Desktop

a.p.r. pillaiDecember 03, 2019Accesstips, External Links, Forms, How Tos, Objects No comments

Opening Access Objects from the Desktop.

Set the Form Name in the Display Form Option of the current database in Access Options.
BIG DEAL! This is the first trick any novice learns when he/she starts learning Microsoft Access.
Create an AutoExec macro with the FormOpen action and the required Form Name in the Property.

The above method opens the form when the database is launched.

Opening a Form Directly, without any Changes to the Database.

We would like to launch a specific Form tomorrow to continue updating data in that form, without making any changes in the database.

If you would like to print a particular Report, first thing in the morning without fail, then here is a simple trick.

Note: Your Database's Navigation Pane must be accessible.

Open the Database.
Click the Restore Window Control Button to reduce the Application Window size, so that the empty area of the Desktop is visible.
Click and hold the Mouse Button on the Form Name in the Navigation Pane, then drag the form to the desktop and drop it there.
Close the Database.
Double-Click on the Desktop-Shortcut. The Form will open when the Database is open.

You can open the following Objects directly with Desktop-Shortcuts:
- Report.
- Table.
- Select Query.
- VBA Module.
- Macro.

Try it out yourself.

Running Sum in MS-Access Query

a.p.r. pillaiNovember 11, 2019Auto-Number, Class Module, Collection Object, Dictionary Object, Functions, Object Reference No comments

Running Sum in an MS Access Query.

We are familiar with creating Auto-Numbers in a Query Column through an earlier User-Defined Function Named QrySeq(), published on this Website with the Post Title: auto-numbering Query Column, in January 2010. Hope you come across that Post; if not, you may visit the Page by following the link above.

The RunningSum() Function is written using similar logic and matches a few QrySeq() Function Parameters.

Before going into details, let us take a look at some sample images, before and after a run of the new Function in a test Query Column.

A small Table with two Fields: Table_Units and a few records.

The SELECT Query: RunningSumQ1 record set in datasheet view, with summary values in a separate column, with the column name RunningSum, from which the RunningSum() Function is called.

The SQL Code of the RunningSumQ1 Query.

SELECT Table_Units.ID, Table_Units.Units, RunningSum([ID],"ID","Units","RunningSumQ1") AS RunningSum
FROM Table_Units;

A Report Designed using RunningSumQ1:

The Query Preparation Note.

Before diving deep into the VBA code, I want you to check the data preparation procedure for the RunningSum() Function.

A unique ID Field, like a primary key, is required in the Query, with either Numeric or String Data, and ensure there are no duplicates.
If this is not readily available in the Source-Data, you may join (concatenate) two or more field values (any type) together to create unique values in a separate column, as a Key Field in the Query.
If this method is followed, then create a Test Query similar to the sample one below, using the first Query as the source, to find out whether any duplicates still exist in the Source Query or not.

Sample ID Field Record-Count Test Query:

SELECT RunningSumQ2.ID2, Count(RunningSumQ2.ID2) AS CountOfID2
FROM RunningSumQ2
GROUP BY RunningSumQ2.ID2;

The CountOfID2 Column result value should be 1.

When all the ID Field values are unique, the CountOfID2 Column will have a value of 1 in all records. A value greater than one in any record means that those records have duplicate keys and need to join some other field to eliminate duplicates.
Once you are sure that all records have unique IDs, you may add other required fields to the first Query for the purpose you plan to use, Form or Report Source.
Once you are ready with the Query data, then it is time to add the function in a new Column in the Query, like Summary: RunningSum([ID], ”ID”, ”Units”, ”MyQuery”).

The RunningSum() Function VBA Code.

Option Compare Database
Option Explicit

Dim D As Object

Public Function RunningSum(ByVal IKey As Variant, ByVal KeyFldName As String, ByVal SumFldName As String, ByVal QryName As String) As Double
'-----------------------------------------------------------
 'Function: RunningSum()
 'Purpose : Creates Running-Sum Value of a Field.
 'The Query can be used as Source for other Processing needs.
 '-----------------------------------------------------------
 'Author  : a.p.r. pillai
 'Date    : November 2019
 'Rights  : All Rights Reserved by www.msaccesstips.com
 '-----------------------------------------------------------
 'Parameter List, in the Order of it's placement
 '1. Key Value Data Field
 '2. Key-Field Name in String Format
 '3. Field-Name for Calculating Running Sum in String Format
 '4  Query-Name in String Format
 '-----------------------------------------------------------
 'Remarks: The Key-Value Field should have Unique Numeric or
 'String Values.
 '-----------------------------------------------------------
Static K As Long, X As Double, fld As String, y as Long
Dim p As Variant

On Error GoTo RunningSum_Err

y = DCount(“*”,QryName)

If SumFldName <> fld OR K > y Then
    fld = SumFldName
    Set D = Nothing
    K = 0
    X=0
 End If

K = K + 1
 If K = 1 Then
     Dim DB As Database, rst As Recordset

    Set D = CreateObject("Scripting.Dictionary")
 
    Set DB = CurrentDb
    Set rst = DB.OpenRecordset(QryName, dbOpenDynaset)
     
    While Not rst.EOF And Not rst.BOF
          X = X + rst.Fields(SumFldName).Value
          p = rst.Fields(KeyFldName).Value
          
          D.Add p, X
          
          rst.MoveNext
     Wend
     
     rst.Close
     Set rst = Nothing
     Set DB = Nothing
     
     RunningSum = D(IKey)
 Else
    RunningSum = D(IKey)
 End If

If K = y then
   K = K + 1
End If

RunningSum_Exit:
Exit Function

RunningSum_Err:
MsgBox Err & ":" & Err.Description, vbOKOnly, "RunningSum()"
Resume RunningSum_Exit
End Function

VBA Code Line by Line.

In the Global area of the Standard Module, an Object Variable is declared with the name D.

The function RunningSum() is declared with four parameters.

The Unique Key Field Value.
The Key-Field Name in String format.
The Summary Field Name in String format.
The Query-Name in String format.

The returned value from the function is a double-precision number.

Four Static Variables are declared:

K – is a control variable.
X – to hold the Summary Values, added to it at a record level.
fld – is the control variable to keep the Summary Field Name as a flag to ensure that the function runs for the same Query.

The Static Variables will retain their values during repeated calls of the Function.

Variable p is to hold the IDKey value retrieved from the record. It is declared as a Variant Type to accept either Numeric or String Key Values.

The Working Logic of the Function.

The statement If SumFldName <> fld Then checks whether the Key-Field name passed to the function is different from the last call of the Function. If it is different, then it assumes that a different Query is passed to the function.

The Dictionary Object D is erased from memory, and other variables are initialized.

In the next step, the K Variable is incremented by one. When K=1, the function’s main task is initiated.

The Database and Recordset Objects are declared.

The D Object variable is instantiated as a new Dictionary Object, with the Object creation statement: Set D = CreateObject(“Scripting.Dictionary”).

By default, the Dictionary Object Reference is not added to the list of Microsoft Access Library Files. If you add it manually, you can declare and instantiate a Dictionary Object, like the Class Module or Collection Object.

Note: If you are not familiar with the Dictionary, Class, or Collection Object, then we have all the information you need to learn the Fundamentals on this website. The links are given at the end of this page. You may visit them to learn using sample code and Demo databases, as working models to download.

Adding Dictionary Object Reference File.

To add the Dictionary Object to your Database’s Library Files List, do the following:

On the VBA Window, select Tools -- > References… and look for the file: Microsoft Scripting Runtime in the displayed list, and put the check mark to select it.

Once you do this, you can declare and instantiate a Dictionary Object as given below.

Dim D As Dictionary
Set D = New Dictionary

If you do this, you have the added advantage of displaying its Properties and Methods when you type a dot (.) after its Object name, by IntelliSense.

Next, the database object DB is set to the active database, and the Query is opened at a record set in the rst object.

Within the While. . .Wend Loop, the summary field and the unique key Field values are read from each record. The Summary field value is added to Variable X. The Key value of the record is written as a Dictionary Object Key-Value, and the current Value in X is written as a Dictionary Object Item, as a Key-Item pair.

The Dictionary Object Items are always written in this way. The Item can be a single value, an Array object, or a collection of objects. All of them should have a Unique Key Value to retrieve the Item values later.

The purpose of the Key in a Dictionary Object is similar to the function of the Primary Key in a Table. We can retrieve any value, randomly or sequentially, from the Dictionary Object using the Key: A = D(Key) or A = D.Item(Key).

In this way, the cumulative summary value, at each record level, is added to the Dictionary Object as its Item, with a unique Key. When all record-level processing is complete, the first record summary field value is returned to the function-calling record by executing the RunningSum = D(IKey) statement, from the first Dictionary Item. All the above actions take place while the control-variable K=1.

Subsequent function calls with the Key-Value parameter of each record retrieve the corresponding summary record value from the Dictionary Item and return it to the Query Column; that’s how it works.

Some Images of the sample run, done in the NorthWind Products table, are given below.

Sample Query Run (Key Values are String Type) Data on Form.

SELECT Trim(Str([ID])) & [Product Code] AS ID2, Products.[Product Code], Products.[Product Name], Products.[List Price], RunningSum([ID2],"ID2","[List Price]","RunningSumQ2") AS RunningSum
FROM Products;

The RunningSumQ2 Query is the Record Source of the Form.

The Sample Run Data of the Report.

The RunningSumQ2 Query is the Record Source of the Report.

Download the Demo Database.

Download ClsEmployees2007.zip

Download ClsEmployees2003.zip

CLASS MODULE

COLLECTION OBJECT

DICTIONARY OBJECT

MS-ACCESS EVENT HANDLING

Date2Text and Text2Date Functions

a.p.r. pillaiOctober 29, 2019Calculation, Custom Functions, Functions, Report No comments

Date2Text and Text2Date Functions.

We already have some frequently used Report Footer formatting simple functions, like Report Page Number formatting function =PageNo([page],[pages]), output: Page: 1/20 –> Page: 20/20, Report Period Function =Period([StartDate], [EndDate]), output: Period: 15/09/2007 To 30/09/2007 and =Dated() Function, output: Dated: 15/09/2007 on Report Footer. Even though they are simple Report Header/Footer formatting functions, they save time on report design. Check the following Links, if you have not yet come across those Functions earlier:

Useful Report Functions.

Continued on Page 2/- an indicator Label on the report page footer, on multi-page reports.

Our new Function formats the Date Value in the following sample Text form:

Sunday, 27th October 2019.

The Date2Text() Function Code.

Public Function Date2Text(ByVal dt As Date) As String
Dim txt As String, num As Integer

num = Day(dt)

   Select Case num
       Case 1, 21, 31
          txt = "st "
       Case 2, 22
          txt = "nd "
       Case 3, 23
          txt = "rd "
       Case 4 To 20, 24 To 30
          txt = "th "
   End Select
   
   Date2Text = WeekdayName(Weekday(dt)) & "," & Day(dt) & txt & MonthName(Month(dt)) & " " & Year(dt)
   
End Function

Copy and paste the Code above into the Standard VBA Module, save the code, and compile it.

Let us try out the Code directly from the Debug Window. Press Ctrl+G to display the Debug Window if it is not already visible on the VBA editing Window.

Sample Test Runs.

D = #27-10-2019#

? Date2Text(D)
Result: Sunday, 27th October 2019

D=Cdate("22/10/2019")

? Date2Text(D)
Result: Tuesday, 22nd October 2019

D=DateValue("11/10/2019")

? Date2Text(D)
Result: Friday, 11th October 2019

? Date2Text(Date)
Result: Thursday, 31st October 2019

Weekday 1 to 7 is Sunday to Saturday. This depends on your Computer's Regional Settings. If it is not correct in your case, then change it in the Regional Settings on your Computer.

Scope of this Function.

The Date2Text() Function can be placed in a TextBox on the Report Header, used on a Date-Field Query Column, or on the Main Form (Main Switchboard or Control Screen, etc.) as general info.

The Text2Date() Function.

The Date2Text() Function’s complementary function, Text2Date() VBA Code is given below.

Public Function Text2Date(ByVal txtDate As String) As Date
Dim S, dt As String
    
    S = Split(txtDate, " ")
    dt = Str(Val(S(1))) & "-" & S(2) & "-" & S(3)
    Text2Date = DateValue(dt)

End Function

The Date converted to Text form can be changed back to a valid date format with the Text2Date() Function. There is no validation check performed, and it expects the parameter value to be in the correct input format, similar to the Date2Text() Function.

Example:

? Text2Date("Thursday, 31st October 2019")

Result: 31-10-2019

Caution: If the parameter value is entered manually, then there should not be more than one space between each segment of the date text.

Call Function From MouseMove Event Property

a.p.r. pillaiOctober 17, 2019Class Module, Collection Object, Downloads, Event, Form, How Tos, WithEvents No comments

Calling a Function from the MouseMove Event Property.

This is about running a User-Defined Function (say =myFunction (Parameter)) on the TextBox MouseMove event property. The difficult part is that when the MouseMove event occurs, the TextBox Name that runs the event must be passed as a parameter to the function.

This question was asked by a member in the Access Users Forum (www.accessforums.net), in the Forms Category of Posts, seeking suggestions for a solution. A sample demo database was posted there twice on page 5, but the last one is the final version.

I thought this would be useful to our readers and have presented it here, with details of how this difficult task was solved using an Object-Oriented Programming approach.

Manual Option.

This is easy to set up if it is manually entered =myFunction("Text1") on each TextBox's Mouse Move Event Property.

But the requirement is to pass the TextBox Name as a parameter dynamically to the Function. It means we should somehow get the TextBox Name from the Mouse Move Event and pass it as a parameter to the function, placed on the same Mouse Move Event Property.

The real situation that demands this method is the following requirement of an Access Application:

The complexity of Requirements.

Assume that you are developing a database for a movie ticket booking Application and need around 350 or more text boxes on the Form, for a graphical seating arrangement design. Each TextBox represents a single seat in the cinema hall, in several rows & Columns (i.e., each row has several seat positions). Each seat has a unique identity number (that is, the TextBox name), indicating its position in the auditorium; for example: Row-A, Seat No.5 (A5) or B1, etc. The TextBox will display its current status either Booked or Vacant.

The idea is that when the mouse moves over the TextBox (Seat), it should display the Seat Number (A5, or B1, etc.) on a dedicated Label in the Header or Footer Section of the Form, to help the customer look for their choice of Seat Numbers and book the Seat(s).

A simple Form with several TextBoxes and labels for sample TextBox arrangements, and try out this method to solve the problem.

PS: The technical details presented above may have lapses or may form suggestions in the reader's mind. That is not important; the core point is how we could get the TextBox Name on the Mouse Move Event and pass the name as a string parameter to the =myFunction() Function, placed on the Mouse Move Event Property of the same TextBox.

Why Manual Method unacceptable.

So, writing =myFunction("A5") or =myFunction("B1") etc., in each one of the 350 Text Boxes' Event Property demands a lot of manual work. Besides that, if any change to the seating arrangement or Seat Numbering scheme becomes necessary, then all the TextBox Properties have to undergo manual changes.

Another option available is to set the Control Tip Text Property with the TextBox Name. When the mouse pointer rests on the TextBox, a brief delay occurs (not acceptable), then it displays the Seat Number from the Control Tip Text property. Modifying the Control Tip Text Property is easy and can be done dynamically in the Form_Load() Event Procedure.

But the database designer insists on passing the TextBox Name as a parameter to the Function. Besides displaying the TextBox Name on the designated Label Caption, the Function may have other issues in the program to take care of.

The Difficult Question.

Even though it sounds like a simple issue, the difficult question is: how do we get the TextBox name, say Text1, from the Name property of the running Form when the mouse moves over that TextBox, and pass the name as a parameter to the Calling Program? Remember, the Mouse Move Event fires repeatedly, at every mouse-point coordinate on the text box (or on any other control it moves) and this Event has some default parameters: Button, Shift, X, and Y coordinates of the Mouse Pointer on the Control. But not the Control Name among them.

The Programming Roadblocks.

There are times when we face roadblocks in solving issues, when conventional programming solutions don't work. But such issues can be easily handled by a few lines of code through Object-Oriented Programming. This is a classic example, easy to understand, and does the job with a few lines of code.

Access Class Module Objects.

We have already covered earlier the fundamentals of Access Class Modules and Objects-based programming. If you are not familiar with stand-alone Access Class Modules and Objects, then the Page links at the bottom of this page to start learning the basics.

The Easy Solution.

To solve the above-narrated issue, we have used a few lines of code in the Access Class Module Objects (both Form and stand-alone Class Modules) and used a Collection Object to organize several instances of the Class Module Objects, rather than using Arrays.

The General-purpose TextBox Object Class Module: ClsTxt Code:

Option Compare Database Option Explicit Private WithEvents txt As Access.TextBox Private frm As Access.Form Public Property Get pFrm() As Access.Form Set pFrm = frm End Property Public Property Set pFrm(ByRef vNewValue As Access.Form) Set frm = vNewValue End Property Public Property Get pTxt() As Access.TextBox Set pTxt = txt End Property Public Property Set pTxt(ByRef vNewValue As Access.TextBox) Set txt = vNewValue End Property Private Sub txt_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single) '------------------------------------------------ 'The first MouseMove Event, of each TextBox, 'comes into this sub-routine. 'The MouseMove Event Property is set with the Function: '"=RunMouseOver('Textbox_Name','Form_Name')" 'with the TextBox & Form Names as Parameters. 'Subsequent MouseMove Events Calls the Function 'directly from Standard VBA Module1, 'control will not come into this sub-routine, any more. '------------------------------------------------

txt.OnMouseMove = "=RunMouseOver('" & txt.Name & "')" End Sub

Two Class-Module Properties, the Access.TextBox and 'Access.Form' Objects are declared in txt and frm object variables, respectively, with Private scope. The txt Property is declared with the WithEvents keyword to capture Events originating from TextBoxes on the form. The next twelve lines of code assign and retrieve objects in TextBox and Form Properties with Set/Get Property Procedures. This will prevent direct access to the Class Module Properties txt and frm from outside. Up to this point, it is the TextBox Object's common feature of assigning and retrieving values to and from the Object Variables. The frm Property is not used here.

The sub-routine part is what we are interested in. Any number of Text-Box-based Event Procedure subroutines can be written here rather than directly on the Form's Class Module.

The txt_MouseMove() Event.

The Text Box's first Mouse Move Event transfers control into the txt_MouseMove() Subroutine. There is only one executable statement in the subroutine that overwrites the Text Box's Mouse Move Event Property Value.

txt.OnMouseMove = "=RunMouseOver('" & txt.Name & "')"

We can get the TextBox name from the txt Property. The TextBox Mouse Move Event Property (initially set as "[Event Procedure]" in the Form_Load() Event Procedure) is replaced with the Function "=RunMouseOver('" & 'txt.name' & "')", and the TextBox name as a string Parameter. The subsequent Mouse Move Events will call the RunMouseOver() Function in the Standard Module from the Mouse Move Event Property, and never come back to the above sub-routine txt_MouseMove() anymore.

So, the first Mouse Move Event on any Text Box will do the trick, and other TextBoxes will wait for their turn for a Mouse Move Event to take place.

The simple RunMouseOver() Function Code will be presented later on this page.

Form3 Class Module Code.

The Form's (Form3) Class Module VBA Code is given below:

Option Compare Database
Option Explicit

'Declare Class ClsTxt as Object F
Private F As ClsTxt
'Declare Collection Object as C
Private C As Collection

Private Sub Form_Load()
Dim ctl As Control

Set C = New Collection 'instantiate Collection Object

For Each ctl In Me.Controls 'scan through the controls
   If TypeName(ctl) = "TextBox" Then ' Take only Text Boxes
        Set F = New ClsTxt 'instantiate ClsTxt Class Object
        
            Set F.pFrm = Me 'Assign Form to pFrm Property
            Set F.pTxt = ctl 'Assign TextBox to pTxt property
            'enable mouse move event
            F.pTxt.OnMouseMove = "[Event Procedure]"
            
        C.Add F 'add ClsTxt Object to Collection
        
        Set F = Nothing 'remove the ClsTxt object instance from memory
    End If
Next

End Sub

Private Sub Form_Unload(Cancel As Integer)
    'Erase the Collection Object when Form3 is closed.
    Set C = Nothing
End Sub

In the global declaration area, Class-Module ClsTxt is declared as Object F and a Collection Object as C.

In the Form_Load() Event Procedure, we scan through Form3 controls and take only TextBox controls. The Form Object and TextBox controls are assigned to the F.pFrm and F.pTxt Properties of the ClsTxt Object.

The F.pTxt Object's OnMouseMove() Event Procedure is enabled so that, when it happens, the control goes to the txt_MouseMove() sub-routine of the Class Module instance of ClsTxt for the first time. In the next step, the ClsTxt Object instance F is added to the Collection Object, as its Item. In the next step, the ClsTxt Object instance F is cleared from memory. A new F object instance is created for the next Text Box. This is necessary to identify each instance of the TextBox Object with a different internal reference of each TextBox added to the Collection Object as its Item.

This process repeats for all the TextBoxes on Form3.

When the Form is closed, the Form_Unload() Event executes, and the Collection Object is cleared from Memory.

When Form3 is open, these initialization steps are performed, and all the TextBox Controls are enabled with the Mouse Move Event, added to the Collection Object, and stay in memory till Form3 is closed. Each Text Box's Mouse Move Event is handled by its respective ClsTxt Object instance added to the Collection Object.

The RunMouseOver() Function Call.

When the user moves the mouse over a Text Box (say Textbox name A1) for the first time, the Mouse Move Event executes and calls the txt_MouseMove() Event Procedure in the ClsTxt Object instance for that Text Box, in the Collection Object item. In this procedure, the TextBox's MouseMove Event Property is modified and set to the =RunMouseOver("A1") Function, with the Text Box name A1 as a parameter.

From the second MouseMove event onwards, the event calls the RunMouseOver() Function from the Standard Module1. The VBA Code of this Function is given below.

Option Explicit

Public Function RunMouseOver(strN As String)
    Screen.ActiveForm.Controls("Label0").Caption = strN

End Function

The RunMouseOver() Function receives the textbox name as a parameter. The statement addresses the Label0 control directly through the Screen Object ActiveForm route and changes the Label's Caption with the Mouse Moved Textbox Name.

The RunMouseOver() Function can be modified to pass the Form's Name as a second parameter and can be used to address the Label0 control as Forms(strForm).Controls("Label0").Caption = strN. This is avoided to keep the parameter expression simple.

When the Mouse is moved over other TextBoxes, the same procedure is repeated for that TextBox object instance in the Collection Object.

When Form3 is closed, the Collection Object instance C, containing all TextBoxes' ClsTxt Class Object instances, is cleared from memory.

The Function RunMouseOver() assigned to the TextBox's Mouse Move Event Properties is cleared (as they are assigned dynamically), and the Property will remain empty.

Next time Form3 is opened, everything falls into place again and is ready for action. So everything is controlled by Object-oriented Programming and happens dynamically. This sample database is uploaded as a solution to the Access User's Forum Page 5, where several alternative options are suggested by other members.

The Demo Database is attached and may be downloaded; try it out yourself.

Download MouseMoveEvent.zip

Class Module Tutorials.

ROUNDDOWN Function of Excel

a.p.r. pillaiSeptember 24, 2019Custom Functions, Excel, function, Functions No comments

'RoundDown' Excel Function.

We introduced the Excel ROUNDUP(Number, num_digits) Function in Microsoft Access earlier. It works like the ROUND() Function except that the number is always rounded up.

Exmple-1: Greater than >0 (>0): ROUNDUP(3.14159, 3) = 3.142

Example-2: Equal to 0 (=0): ROUNDUP(3.2,0) = 4

Example-3: Less than <0 (<0): ROUNDUP(31415, -2) = 31500

Visit the following link for more details on the ROUNDUP () Function:

The ROUNDUP() Function of Excel in Access.

The ROUNDDOWN() Function of Excel does the opposite of the ROUNDUP() Function. When the number of digits specified is greater than 0 (zero), then the number is rounded down to the specified number of Decimal Places. If the number of digits specified is 0 (zero), then the number is rounded down to the nearest integer.

Syntax:

ROUNDDOWN(Number, num_digits)

Number: Required, Any Real Number.

num_digits: Required, Number of Digits the Number to Round Down to.

Positive Number (>0): ROUNDDOWN(3.149, 2) = 3.14

Zero (0): ROUNDDOWN(8.9, 0) = 8

Negative Number (< 0): ROUNDDOWN(258, -1) = 250

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

The ROUNDDOWN() Function is not field-tested for accuracy; use it at your own risk.

The Function is developed based on the sample output given in the Microsoft Help Document. The Microsoft Excel Help Document extract is reproduced below for your information.

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