Access VBA create Query using CreateQueryDef

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 expression.CreateQueryDef(Name, SQLText) Name Require...
More

Access Excel VBA copy workbook or file

This Access Excel VBA tutorial explains how to copy workbook or file. You may also want to read: FSO File Methods Worksheets.Copy Method to copy worksheet Access Excel VBA copy workbook or file There are two common ways to copy workbook or file FSO.CopyFile Method FileCopy Function I have explained how to use different FSO Methods in my previous post, click to see more details. Using FSO.CopyFile Method to copy workbook You should be able to run FSO in Excel 2013. If you fa...
More

Sort Data in Access Report

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. Example Suppose we have the below table that contains employee data. employee_tbl Employee ID Employee Name Department Team Salary 001 David HR Compensation 10000 ...
More

Create Matrix Report in Access Report

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...
More

Access Crosstab Query

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...
More

Access Update Table Records

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...
More

Access Excel capitalize first letter in a sentence

This Access Excel tutorial explains how to capitalize first letter in a sentence using VBA custom Function. Capitalize first letter in a sentence In Excel spreadsheet, there is a Function called Proper, which converts all the first letter of each word to capital letter. Example  Using Proper Function he is a boy. she is a girl. He Is A Boy. She Is A Girl. he is a boy. his name is John. mary is a girl. He Is A Boy. His Name Is John. Mary Is A Girl. Mr.  smith is happy ...
More

Access VBA change Report Record Source

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...
More

Access Excel remove text in brackets

This Access Excel VBA tutorial explains how to remove text in brackets using custom Function. You may also want to read: Change text color in brackets Extract text in brackets Access Excel remove text in brackets I was inspired to create this post because the HR system I am using (Workday) is very annoying, some data fields are actually a combination of two different fields, the additional data field is displayed in brackets in the prefix. For example,  an employee has a English name ...
More

Access delete table records

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...
More