Access Report Section Property

Posted on April 27, 2015 by Wyman W

This Access tutorial explains how to use Access Report Section Property to control Report Section such as Page Header, Footer. Access Report Section Property In Access Report VBA, you can directly access the Controls in the Report, it is also possible to access the Report Sections using Report Section Property, such as Page Header, Group Header, Page Footer, etc. This is especially useful to hide the whole Section. Syntax of Report Section Property Me.Section(Index) Index Constant D...

VBA Difference between Dim Set

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

This Access/Excel VBA tutorial shows the difference between Dim and Set keyword in variable declaration. You may also want to read: Excel VBA Option Explicit to force declaration of variables Difference between ByRef ByVal Function, Sub, Method, Property Differences VBA Difference between Dim and Set In VBA, we can always see the use of Dim and Set, but in some occasions we don't. Example 1: Dim rng as Range 'Optional Set rng = Range("A1") Example 2: Dim s as String 'Op...

Access Excel Scripting Dictionary store unique array item

Posted on April 7, 2015July 4, 2018 by Wyman W

This Access Excel tutorial explains how to use Scripting.Dictionary in VBA to store unique array item. You may also want to read: Access Excel VBA Array Remove duplicates in text using Dictionary Excel VBA filter value then copy data to new worksheet Access Excel VBA Scripting.Dictionary to store unique array item An associative array, map, symbol table, or dictionary is an abstract data type composed of a collection of (key, value) pairs, such that each possible key appears just once ...

Access generate random letter A to Z using Rnd

Posted on April 5, 2015 by Wyman W

This Access tutorial explains how to generate random letter A to Z / a to z or a mix using Rnd Function. You may also want to read: Excel generate random letter A to Z using RANDBETWEEN Access generate random letter A to Z using Rnd In order to generate random letter, we have to make use of two Functions. - Rnd - Chr In the below sections, I will briefly go going through these two Functions. For details, click on the link above. Access generate random number using Rnd Access R...

Access Excel Generate random HKID Hong Kong ID card number

Posted on April 5, 2015March 17, 2016 by Wyman W

This Access Excel VBA tutorial demonstrates how to generate random HKID / Hong Kong ID card number. You may also want to read: Access Excel Function HKID Check Digit (last digit) Excel VBA Rnd Function to generate random number Excel generate random letter A to Z using RANDBETWEEN Assign unique random number using Excel Access Excel Generate random HKID (Hong Kong ID card number) The purpose of this article is to generate a random HKID (Hong Kong ID card number) that has va...

Access count substring in string or text

Posted on March 29, 2015September 15, 2015 by Wyman W

This Access tutorial explains how to count substring in string (text). Access count substring in string (text) Access does not have a buit-in Function to count substring in string (text), instead you need to work around to get the job done. Alternatively, I have made a VBA solution for your reference. Access count substring in string using Len and Replace Function Access Len Function is to measure the length of text. Access Replace Function is to replace a substring in a text. To cou...

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

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