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

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

MS Access SQL select MAX record of each group

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