Compare difference between two tables in MS Access Query

Posted on March 28, 2015March 28, 2015 by Wyman W

This Access tutorial explains how to compare difference between two tables in MS Access Query Compare difference between two tables in MS Access Query Access does not have a built-in Function to compare difference, but since we can write SQL and design Query in Access, we can make use of the properties of SQL JOIN to compare difference. There are three kinds of JOIN in SQL - Inner Join, Left Join, Right Join. Assume that you have two tables, table1 and table2, where you join them using a...

Access error Type Num Div/0 Num

Posted on March 28, 2015 by Wyman W

This tutorial explains the cause of Access errors in Access Query, Report, Form. These include #Error #Type! #Num! #Div/0! #Name. Access error #Error #Type! #Num! #Div/0! #Name There are several very common errors you may encounter in Access Query, Report or Form. It is very useful to understand the error in order to fix it, otherwise you will need to spend a lot of time to find out what Access is trying to tell you. Access error #Error! Access error #Error! occurs in an Expression where you...

Access Report keep a group together in one page

Posted on March 27, 2015March 25, 2016 by Wyman W

This Access tutorial explains how to keep a group together in one page in Access Report. You may also want to read: Access Report group by date problem Access Report - keep a group together in one page In Access Report, you can group a Field and then display the grouping in Group Header, and display the value in Details. Sometimes the Details may be so long that it extends to the next page, breaking up the same group into two pages. Access Report has a function called "group together i...

Excel VBA Rnd Function to generate random number

Posted on March 27, 2015March 17, 2016 by Wyman W

This Excel tutorial explains how to use Excel VBA Rnd Function to generate random number and create custom Function for random decimal number. You may also want to read: Excel RAND RANDBETWEEN Function to generate random number Access Excel Generate random HKID Hong Kong ID card number Assign unique random number using Excel Excel VBA Rnd Function to generate random number Rnd Function is for use in Access, Access VBA, Excel VBA, it is used to generate random number larger than 0 and s...

Access Rnd Function generate random number and decimal

Posted on March 26, 2015March 27, 2015 by Wyman W

This Access tutorial explains how to use Access Rnd Function to generate random number and create custom Function for random decimal number. Access Rnd Function to generate random number Access Rnd Function is for use in Access, Access VBA, Excel VBA, it is used to generate random number larger than 0 and smaller than 1 (1 and 0 exclusive). Syntax of Access Rnd Function Rnd[(number)] Number Rnd generates Not supplied or any positive number Generate a new random number 0 R...

Access Excel assign number for custom sorting sequence

Posted on March 26, 2015April 20, 2015 by Wyman W

This Access Excel tutorial explains how to assign number to Text for custom sorting sequence and how to use Excel Custom Lists. You may also want to read: Sort data using custom Function by Bubble Sort Sort data using Range Sort Method Sort data using Table AutoFilter Sort data using Worksheet AutoFilter Access Excel Assign number to Text for custom sorting sequence Suppose your company has departments IT, HR, Finance. Normally if you sort the sequence in ascending order, it wou...

Access Excel VBA group number with larger than smaller than (age group)

Posted on March 26, 2015December 4, 2015 by Wyman W

This Access Excel tutorial explains how to create custom Function to categorize /group number with larger than smaller than symbol, such as age group. Access Excel categorize / group number with larger than smaller than symbol Sometimes when you have a group of numbers, you may need to categorize / group them into different categories for analysis. For example, you may need to make age group for employees, such as <20 >=20 to <30 >=30 to <40 >=40 In order ...

Excel convert column letter to column number vise versa

Posted on March 26, 2015March 26, 2015 by Wyman W

This tutorial explains how to convert column number to column letter, and convert column letter to column number in Excel. Excel column letter to column number / column number to column letter conversion Column number refers to the column expressed in integer, while column letter refers to column expressed in alphabet. For example, for Cell D1, column number is 4, while column letter is D. Excel convert column number to column letter To convert column number to column letter, make use o...

Difference between ByRef ByVal in Access Excel VBA

Posted on March 25, 2015April 21, 2015 by Wyman W

This tutorial explains difference between ByRef ByVal in Access Excel VBA. You may also want to read: Difference between Dim and Set Excel VBA Option Explicit to force declaration of variables Function, Sub, Method, Property Differences Difference between ByRef ByVal in Access Excel VBA ByRef means to pass a variable or Object by Reference (memory address). ByVal means to pass a variable or Object by Value. When you define a Function with arguments, you can define the variable ...

Excel Vlookup second matched value or specific occurrence

Posted on March 25, 2015September 17, 2017 by Wyman W

This tutorial explains how to write a custom VBA Excel Function to vlookup second matched value or specific occurrence. You may also want to read: Case sensitive lookup Excel Lookup Function lookup multiple criteria not in the first column Excel lookup partial text using Vlookup with Wildcard Excel lookup Function lookup multiple criteria Excel Vlookup second matched value or specific occurrence In traditional Vlookup, you are allowed to vertically lookup the first value that ma...