This Access tutorial explains how to create Query in Access VBA using CreateQueryDef Method.
Access VBA create Query
Sometimes we don't want to create Query in Query Design View, because you may create a Query with criteria depending on a variable. To create Query in Access VBA, you can directly write a SQL statement in Access VBA, and then create a Query using the SQL statement with CreateQueryDef Method.
Syntax of CreateQueryDef
This Microsoft Access tutorial explains how to sort data in Access Report, including sort data in custom order.
Sort Data in Access Report
In Access Report Design View, you can sort data at the Group, Sort, and Total section, in this tutorial I will explain how to sort data in Detail and sort Grouped data.
Suppose we have the below table that contains employee data.
This Microsoft Access tutorial explains how to create Matrix report in Access Report using Crosstab Query and Expression.
Access Matrix Report
Matrix Report is like an Excel Pivot Table, where you can place fields in row and column, and then apply Aggregate Functions on a field value such as Count, Sum, Max. In newer version of Microsoft Access, Pivot Table function has been removed, you can only create Matrix Report through Crosstab Query, or write expression in Access Report.
In this po...
This Microsoft Access tutorial explains how to create Crosstab Query and demonstrate how to add grand total.
You may also want to read:
Create Matrix Report in Access Report
Access Crosstab Query
Crosstab Query is a Matrix Report, the most commonly known Matrix Report is Excel Pivot Table, where you can place fields in row and column, and then apply Aggregate Functions on a field value such as Count, Sum, Max.
In newer version of Microsoft Access, Pivot Table function has been removed...
This Microsoft Access tutorial explains how to update table records from another table or query.
Access update table records
You can always update table records manually by viewing the table and then enter the value. With Update Query, you can update multiple table records that meet defined criteria, or update table records from another table.
Note that you may update data in an Access table, but you cannot update data in linked table. For example, if your Access table is linked to Excel sp...
This Access tutorial explains how to change Access Report Record Source using VBA in order to modify Report criteria automatically.
Change Report Record Source
When you create an Access Report, you can specify the data source of the report in Report Record Source, which can be a Query or Table.
Type the Table / Query name in the Record Source as below.
To add criteria to the Report, there are three ways
Method 1 - Write SQL
Type the full SQL statement directly in Record S...
This Access tutorial explains how to delete table records and delete records that exist in another table in Microsoft Access.
Access delete table records
You can always modify or delete table data manually by viewing the table and then press the delete button in keyboard. With Query, you can delete specific rows of record that meet the criteria. Note that you may delete data in an Access table, but you cannot delete data in linked table. For example, if your Access table is linked to Excel spr...
This Access tutorial explains how to use Access INSTR Function in Expression and VBA, and explain difference among vbUseCompareOption, vbBinaryCompare and vbTextCompare.
Access INSTR Function
Access INSTR function is used to search a substring (part of the string) within a string (string is a text) and return the position of the first occurrence.
For example, in the string “FinanceDepartment”, the substring “Department” can be found at 8th position within “FinanceDepartment”, the function w...
This Access tutorial explains how to use Access MID Function to extract substring from a string.
Access MID Function
MID Function is quite similar to LEFT Function and RIGHT Function, where RIGHT Function extracts a substring on the right, LEFT Function extracts a substring on the left, while MID extracts a substring in the middle of the string. Other than Access, Mid Function can also be used in Excel worksheet, Excel / Access VBA.
Access MID Function is commonly used with Instr Function (...
This MS Word VBA tutorial explains how to add footer to all word documents in a folder automatically.
MS Word VBA add footer to all word documents in a folder
A couple of years ago, my manager asked me to add the keyword Public / Confidential / Restricted to all Word documents footers to meet compliance requirement (this is a common practice), I painfully did it manually for hundreds of documents. In fact it could have been done automatically. In some of my previous posts, I have demonstrate...