MS-Access / Getting Started

---

Variables and Constants

In addition to using Visual Basic code to work with the controls on any open forms or reports (as you can with user interface macros), you can declare and use named variables in Visual Basic code for storing values temporarily, calculating a result, or manipulating any of the objects in your database. To create a value available anywhere in your code, you can define a global variable, as you can find in the modGlobals module in the Conrad Systems Contacts sample database.

Another way to store data in Visual Basic is with a constant. A constant is a data object with a fixed value that you cannot change while your application is running. You've already encountered some of the built-in constants in Access 2010-Null, True, and False. Visual Basic also has a large number of intrinsic constants-built-in constants that have meaningful names-that you can use to test for data types and other attributes or that you can use as fixed arguments in functions and expressions. You can view the list of intrinsic constants by searching for the Visual Basic Constants topic in Help. You can also declare your own constant values to use in code that you write.

In the following sections, you'll learn about using variables to store and calculate data and to work with database objects.

Data Types

Visual Basic supports data types for variables and constants that are similar to the data types you use to define fields in tables. It also allows you to define a variable that is a pointer to an object (such as a form or a recordset). The data types are described in Table-1.

Table-1 Visual Basic Data Types

Data Type	Size	Data-Typing Character	Can Contain
Boolean	2 byte	(none)	True (-1) or False (0)
Byte	1 byte	(none)	Binary data ranging in value from 0 through 255
Integer	2 bytes	%	Integers from -32,768 through 32,767
Long	4 bytes	&	Integers from -2,147,483,648 through 2,147,483,647
LongLong	8 byte	^	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 (Valid on 64-bit platforms only.)
LongPtr	4 bytes on 32-bit systems and 8 bytes on 64-bit systems	(none)	-2,147,483,648 to 2,147,483,647 on 32-bit systems -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 on 64-bit systems
Single	4 bytes	!	Floating-point (imprecise) numbers from approximately -3.4 x 1038 through 3.4 x 1038
Double	8 bytes	#	Floating-point (imprecise) numbers from approximately -1.79 x 10308 through 1.79 x 10308
Currency	8 bytes	@	A scaled integer with four decimal places from -922,337,203,685,477.5808 through 922,337,203,685,477.5807
Decimal	14 bytes	(none)	A precise number with up to 29 digits and up to 28 decimal places from -79.228 x 1027 to 79.228 x 1027 (Visual Basic in Access supports the Decimal data type only as a type within the Variant data type.)
String	10 bytes plus 2 bytes per character	$	Any text or binary string up to approximately 2 billion bytes in length, including text, hyperlinks, memo data, and "chunks" from an ActiveX object; a fixed-length string can be up to 65,400 characters long
Data	8 bytes	(none)	Date/time values ranging from January 1, 100, to December 31, 9999
Object	4 bytes	(none)	A pointer to an object-you can also define a variable that contains a specific type of object, such as the Database object
Variant	16 bytes through approximately 2 billion bytes	(none)	Any data, including Empty, Null, and date/time data (Use the VarType function to determine the current data type of the data in the variable. A Variant can also contain an array of Variants. Use the andto determine whether a Variant is an array.)
User-Defined	Depends on elements defined	(none)	Any number of variables of any of the above data types

You can implicitly define the data type of a variable by appending a data-typing character, as noted in Table-1, the first time you use the variable. For example, a variable named MyInt% is an integer variable. If you do not explicitly declare a data variable that you reference in your code and do not supply a data-typing character, Visual Basic assigns the Variant data type to the variable. (See "Declaring Constants and Variables," later in this tutorial, to learn how to explicitly declare data variables.) Note that although the Variant data type is the most flexible (and, in fact, is the data type for all controls on forms and reports), it is also the least efficient because Visual Basic must do extra work to determine the current data type of the data in the variable before working with it in your code. Variant is also the only data type that can contain the Null value.

The Object data type lets you define variables that can contain a pointer to an object. See "Collections, Objects, Properties, and Methods," later in this tutorial, for details about objects that you can work with in Visual Basic. You can declare a variable as the generic Object data type, or you can specify that a variable contains a specific type of object. The major object types are AccessObject, Application, Catalog, Column, Command, Connection, Container, Control, Database, Document, Error, Field, Form, Group, Index, Key, Parameter, Procedure, Property, QueryDef, Recordset, Relation, Report, Table, TableDef, User, View, and Workspace.

You can request that Visual Basic generate all new modules with an Option Explicit statement by selecting the Require Variable Declaration check box on the Editor tab of the Options dialog box. If you set this option, Visual Basic includes an Option Explicit statement in the Declarations section of every new module. This helps you avoid errors that can occur when you use a variable in your code that you haven't properly declared in a Dim, Public, Static, or Type statement or as part of the parameter list in a Function statement or a Sub statement. When you specify this option in a module, Visual Basic flags any undeclared variables it finds when you ask it to compile your code. Using an Option Explicit statement helps you find variables that you might have misspelled when you entered your code.