Access VBA loop through Table records

Posted on March 2, 2017June 18, 2017 by Wyman W

This Access VBA tutorial explains how to loop through Table records or Query records in Microsoft Access. You may also want to read: Access VBA loop through all Tables using DAO.TableDef Access VBA loop through Table records Suppose we have an Acess Table or Query and you want to loop through the records (in Excel terms, to loop the values in each row), we have to make use of Recordset objects, which has different Methods to help us manipulate data in a database at the record level. I...

Access VBA change Query criteria using QueryDef.SQL Property

Posted on February 1, 2017 by Wyman W

This Access VBA tutorial explains how to change Query criteria in VBA using QueryDef.SQL Property. You may also want to read: Create Query using CreateQueryDef Access VBA - change Query criteria It is easy to change Query criteria in Query View, but sometimes your criteria may depend on a variable that is not a constant. In Access VBA, you can change the Query criteria using QueryDef.SQL Property. In fact, QueryDef.SQL does not really update just the update the criteria of the statement,...

Access VBA create Query using CreateQueryDef

Posted on January 18, 2017February 1, 2017 by Wyman W

This Access tutorial explains how to create Query in Access VBA using CreateQueryDef Method. You may also want to read: Change Query criteria 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 expres...

Access Excel VBA copy workbook or file

Posted on January 15, 2017 by Wyman W

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

Access Excel capitalize first letter in a sentence

Posted on December 19, 2016March 17, 2019 by Wyman W

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

Access Excel remove text in brackets

Posted on December 11, 2016December 28, 2016 by Wyman W

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

Access Excel VBA Array

Posted on October 24, 2016May 6, 2018 by Wyman W

This VBA tutorial explains how to create and use VBA Array in Access and Excel VBA. You may also want to read: Store unique array item with Scripting Dictionary Access Excel VBA Array In Excel worksheet, you can convert a formula arguments to Array by pressing CTRL+SHIFT+ENTER, click here to see example. In VBA, Array is a variable that can store multiple data of the same data type. There are two types of Array in VBA One-dimensional Array Two-dimensional Array For most...

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