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...
Read More
Chapter 9 – Access VBA
Access VBA change Query criteria using QueryDef.SQL Property
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,...
Read More
Access VBA create Query using CreateQueryDef
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...
Read 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...
Read 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
...
Read 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 ...
Read More
Access Excel VBA Array
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...
Read More
Access Instr Function
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...
Read More
Access Excel INT Function to get the integer of number
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...
Read More
Access Mid Function to extract substring
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 (...
Read More