Access Instr Function

Posted on October 6, 2016 by Wyman W

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

Access Excel INT Function to get the integer of number

Posted on October 4, 2016 by Wyman W

This Access / Excel tutorial explains how to use INT Function to get the integer part of a number. Access Excel INT Function to get the integer of number When you have a number with decimal places, you can separate the number into integer part using INT Function, and then work around to get the decimal part. INT Function can be used in Access and Excel, and also VBA. Syntax of INT Function INT(number) INT Function only contains one argument, which is the number from which you want to get th...

Access Mid Function to extract substring

Posted on September 26, 2016 by Wyman 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 (...

MS Access SQL select MAX record of each group

Posted on July 19, 2016 by Wyman W

This MS Access tutorial explains how to use Access SQL to select MAX record (MAX row) of each group. Access SQL select MAX record In Microsoft Access, you can select fields you need to Group and then use MAX to get the max record of each Group. However, the result only returns the grouped fields but not all the fields in the original table. In this tutorial I will show two methods to select all fields. Example - Select Max data Table1 below stores the performance rating of each employee in d...

Access Excel VBA delete all workbooks in folder

Posted on June 2, 2016 by Wyman W

This VBA tutorial explains how to delete all workbooks in folder using Access VBA and Excel VBA. Access Excel VBA delete files in folder Suppose you want to write a Macro to export files to a folder. There could be problem if the folder already contains the files you want to export. To avoid this, you probably want to delete the folder and create a new folder before exporting the files. Alternatively, you can also delete all files in the folder. In order to delete files, you can use FSO Fil...

Access Excel VBA delete folder if folder exists

Posted on June 1, 2016June 2, 2016 by Wyman W

This tutorial explains how to use Access / Excel VBA to delete folder if folder exists already. Access Excel VBA delete folder if folder exists Suppose you want to write a Macro to export files to a folder. There could be problem if the folder already contains the files you want to export. To avoid this, you probably want to delete the folder and create a new folder before exporting the files. Alternatively, you can also delete all files in the folder. In order to delete a folder, you can u...

Access VBA check if Query is empty

Posted on May 26, 2016May 26, 2016 by Wyman W

This Access tutorial explains how to check if table or Query is empty in Access VBA. You may also want to read: Microsoft Access VBA Export all Query to Text File Access VBA check if Query is empty In Access VBA, there are SQL related Functions that simulate SQL. Access DCOUNT Function is one to simulate SQL COUNT. To count the number of records in a Query, we can use * in the first argument of DCOUNT Function. For example, the below VBA counts the number of records in Query1 DC...

Access SQL SELECT DISTINCT Clause

Posted on May 16, 2016 by Wyman W

This Microsoft Access tutorial explains how to use SELECT DISTINCT Clause in Access Query. Microsoft Access SQL SELECT DISTINCT Clause SELECT DISTINCT can be used in Microsoft Access SQL to select unique values in a field. Note that you cannot use this keyword in Query Design View, you can only use it directly in SQL View. The outcome of DISTINCT is exactly the same as GROUP BY if you only try to find the unique values, but GROUP BY can be used together with other Aggregate Functions such a...

Access VBA NPV Function to calculate Net Present Value

Posted on May 8, 2016May 14, 2016 by Wyman W

This Access tutorial explains how to use NPV Function in Access VBA to calculate Net Present Value. You may also want to read: Excel NPV Function Net Present Value Net Present Value is to calculate the present value of future cash flow. For example, your client will give you $10000 when the project completes after 1 year, assume there will be inflation, the future $10000 will be less than the present $10000. Assume the inflation is 4%, the present value of $10000 is 10000/(1+4%) = 9615.3...

Access Report group by date problem

Posted on March 25, 2016 by Wyman W

This Access tutorial explains how to group by date (one date for each group) in Access Report and explain the issue. You may also want to read: Access Report keep a group together in one page Access Report group by date problem When you create an Access Report, the default setting isn't group by date. To illustrate, assume that we have an employee database as below. Table1 Hire Date Employee ID 20/12/2016 001 20/12/2016 002 21/12/2016 003 21/12/2016...