Locked lesson.
About this lesson
A review of Simple, Special and Complex Data Types.
Quick reference
- 00:07 Data Types are the building blocks of fields.
- 00:10 Fields define tables.
- 00:12 Tables are the most important objects in your database.
- 00:16 Understanding data types
- 00:18 and choosing the right data types is important.
- 00:21 Hi, this is Crystal.
- 00:23 In the past four lessons,
- 00:25 we have covered common data types in detail.
- 00:27 You have learned how to define them
- 00:29 and how they work when you enter information.
- 00:32 This lesson is a review of the data types we have discussed,
- 00:36 and an introduction to other data types
- 00:38 you can choose in Access.
- 00:40 This chart shows simple data types.
- 00:43 The first five data types are chosen
- 00:46 by setting the value in the Data Type column to Number,
- 00:49 and then choosing the real data type in the Field Size property
- 00:53 in the lower pane of the design window.
- 00:56 Byte is the smallest number Access can store.
- 00:59 It can hold positive whole numbers from 0 to 255.
- 01:05 Access will return an error if an attempt is made
- 01:08 to store a bigger number, or a negative number.
- 01:11 Byte typically means the number of bits
- 01:14 it takes to encode a single character, which is hardware dependent.
- 01:18 In Access, however, the byte data type holds 8 bits.
- 01:23 It is often best to avoid using the Byte data type.
- 01:26 Integer and Long Integer are both whole numbers.
- 01:30 The difference is their storage size.
- 01:33 Integers take 2 bytes to store and can hold values
- 01:37 between minus 32 thousand to plus 32 thousand.
- 01:41 Long Integers take 4 bytes to store
- 01:44 and can range from -2 billion to +2 billion.
- 01:48 Long Integers are often used to relate data in one table to another.
- 01:53 Single and Double precision numbers
- 01:55 are stored in floating point format.
- 01:58 Use floating point numbers when you need
- 02:00 a large range of data and exact precision is not important.
- 02:04 You can see by the limits that you can get really close to 0,
- 02:09 but then there is a microscopic gap to zero.
- 02:12 Strange things happen with Single or Double precision numbers.
- 02:16 For instance, this equation returns 0.125,
- 02:21 which is obviously not the right answer.
- 02:24 Single precision numbers have 6 to 9 significant figures.
- 02:29 CSng is a function to convert
- 02:32 the number in the parentheses to a single precision number.
- 02:35 If you wrap the numbers with the CDbl function to convert
- 02:39 numbers to double-precision, the answer is 0.099999…
- 02:45 which is closer to the right answer, but still not exactly right.
- 02:49 Double precision numbers have 15 to 17 significant figures.
- 02:54 With floating point numbers, after the significant figures,
- 02:58 random digits will be created, causing floating point numbers
- 03:02 to be unreliable for exact comparisons.
- 03:06 Because floating point numbers are not precise,
- 03:09 they should not be used for Primary Keys
- 03:12 or Foreign Keys or to relate data.
- 03:14 When you need precision and have no more than 4 decimal places,
- 03:18 choose the Currency data type.
- 03:20 When the numbers are wrapped in the CCur function, which
- 03:24 converts numbers to currency, the correct answer is returned.
- 03:28 Currency can hold up to 15 digits before the decimal point,
- 03:32 and 4 digits after the decimal point.
- 03:35 Currency is the most accurate numeric data type with decimal places.
- 03:40 Currency is a fixed-point number that is displayed with
- 03:44 the currency symbol specified in the Windows Region settings.
- 03:48 The Format property can be used to not show a currency symbol.
- 03:53 Decimal is another Number data type available when you design tables,
- 03:57 but there is no equivalent in VBA,
- 04:00 which is the programming language,
- 04:02 so it is better not to use the Decimal data type in Access.
- 04:06 Yes/No can only be one of 2 possible values:
- 04:11 -1 represents True, or Yes, or On
- 04:15 0 represents False, or No, or Off
- 04:19 Internally, the Yes/No data type is stored
- 04:22 as a 8-bit signed number except the capabilties of its storage
- 04:26 have been crippled to not to allow any value except -1 or 0.
- 04:31 Yes/No cannot store null, or nothing;
- 04:34 it must be one of the 2 values.
- 04:37 Unlike the VBA boolean data type, however,
- 04:40 it is actually possible to return null
- 04:43 for a yes/no field when joining tables.
- 04:46 Null means no value, and can cause issues
- 04:49 as the JET engine in Access assumes that a Yes/No field
- 04:53 will always be -1 or 0 and will never be null.
- 04:58 As a workaround, Allen Browne suggests
- 05:00 using Integer and assigning a checkbox as the display control.
- 05:04 Many times, however, instead of storing Yes or No,
- 05:07 something else should be stored.
- 05:10 For instance, instead of a checkbox if something was received or not,
- 05:14 it is better to use a date field and store WHEN an item was received.
- 05:19 If the date field is not filled,
- 05:21 then it can then be assumed the item has not yet been received.
- 05:25 Short Text can store letters, numbers, and special characters.
- 05:29 Choose this data type when you want to
- 05:31 store alphanumeric characters such as names.
- 05:34 As a general Rule, also use Short Text when you are storing a number
- 05:38 that you do not need to increment or calculate,
- 05:41 such as a phone number or an account number.
- 05:44 When you choose Text as your data type,
- 05:47 it is also important to consider how many characters will be needed.
- 05:51 Although variable length text is stored, how many characters
- 05:54 are allowed to be stored can impact performance.
- 05:58 Short Text is delimited with quotes.
- 06:00 Delimiters are necessary so Access knows where the value starts and ends.
- 06:07 In VBA, text must be delimited with double quotes.
- 06:10 In SQL, either single quotes or double quotes can be used.
- 06:15 VBA is Visual Basic for Applications and is the
- 06:19 programming language that Access desktop databases use.
- 06:24 SQL is Structured Query Language
- 06:27 and what queries use to get information from tables.
- 06:31 Unless the Allow Zero Length property is set to No,
- 06:34 it is possible that what looks like nothing is stored,
- 06:38 could actually be a zero length string (ZLS).
- 06:41 For those that are curious,
- 06:43 type declaraction characters are shown.
- 06:46 The last column displays the size in bytes
- 06:49 that each data type takes to store.
- 06:51 Special data types are stored using various data types.
- 06:55 AutoNumber is a special data type
- 06:58 that can be a Long Integer or a Replication ID
- 07:02 that automatically gets its value when new records are created.
- 07:05 The most common use of AutoNumber is to use it
- 07:09 as a Long Integer that automatically increments
- 07:11 sequentially as new values are created.
- 07:14 AutoNumbers are often used for Primary Keys.
- 07:17 In a related table, the matching Foreign Key
- 07:20 would be a Long Integer data type.
- 07:22 Date/Time is the most problematic data type,
- 07:26 largely because of how it is displayed.
- 07:29 Internally, a Date/Time is stored as a number, which is why
- 07:32 the number sign is used to delimit date/time values.
- 07:36 A Date/Time value shows several pieces of information
- 07:40 and, depending on what country you are in,
- 07:43 the information is displayed differently.
- 07:45 The earliest date Access can reference
- 07:48 is January 1, 100, which is -657,434
- 07:56 The maximum date Access can store
- 07:58 is 31 Dec 9999, which is 2,958,465.
- 08:07 The Time part of a Date/Time is stored as a fraction.
- 08:10 Noon is really just halfway through a day.
- 08:14 Date and Time both measure the same thing,
- 08:17 just in different terms.
- 08:19 Hyperlink is a special form of short text that will act as a link
- 08:24 to open a file when you click its value.
- 08:26 A Hyperlink is a reference to any file
- 08:29 that is accessible by the computer.
- 08:32 It can be a file on the computer you are using,
- 08:35 on your network, or an internet address.
- 08:38 Hyperlink fields can contains four parts separated by octothorpes (#),
- 08:43 which are also called pound signs, number signs,
- 08:46 and what we use to play tic-tac-toe.
- 08:49 The first part of a hyperlink is the text to display.
- 08:53 The second part of a hyperlink is the actual address of the file.
- 08:58 The third part of a hyperlink can reference a named section within the specified file,
- 09:03 The fourth part is the screen tip.
- 09:06 I prefer to store the actual address (and named section, if specified)
- 09:11 of hyperlinks in short text fields and use an event procedure
- 09:15 to navigate to the specified address when requested.
- 09:19 When you choose Calculated... for the data type,
- 09:21 Access prompts you for an expression, or an equation.
- 09:25 Use the Expression Builder to lookup functions
- 09:28 and help construct equations.
- 09:31 The top box is for your expression.
- 09:34 Below, on the left is a tree showing types of expression elements.
- 09:39 In the middle are expressions that can be chosen
- 09:42 If you double-click on a listed field,
- 09:44 Access adds it to the equation.
- 09:47 To see functions, choose Functions, Built-In Functions
- 09:50 on the left in the lower pane.
- 09:52 The middle box then shows categories of functions
- 09:55 such as Conversion, Date/Time, Math, and Text.
- 09:59 The box on the right will then
- 10:01 list the built-in functions in that category.
- 10:04 To pick one, double-click.
- 10:06 The function and placeholders for its argument(s) will go into the top box.
- 10:11 Choosing Lookup Wizard ... for data type
- 10:14 enables you to lookup values from another table or query.
- 10:17 Unless you are building an Access web database,
- 10:20 it is best to avoid using lookup fields.
- 10:23 Instead, implement choices when you design forms
- 10:27 using combo boxes and list boxes.
- 10:31 Complex data types are not stored in the table with other values.
- 10:35 The Attachment data type stores the contents of files.
- 10:38 There can be multiple attachments
- 10:40 stored in each attachment field.
- 10:43 Attachments are often used to store pictures,
- 10:45 and point-in-time information such as scans of invoices and receipts.
- 10:50 Storing attachments in the database
- 10:53 can cause the database file to get really big really fast.
- 10:57 Rather than storing attachments IN a database,
- 11:00 consider leaving them in external files
- 11:03 and store the filenames in a text field.
- 11:05 Another advantage to this is that you can
- 11:08 reference the same file in multiple records.
- 11:11 So that the files are easy to identify and back up with the database,
- 11:16 put a copy of the attachment files in a folder below the database.
- 11:21 Attachments aren't generally dynamic, like data,
- 11:24 and data does not necessarily have to be kept in the database.
- 11:28 It just needs to be accessible by the database.
- 11:32 Long Text, which used to be called the Memo data type,
- 11:36 can be used to store large amounts of information.
- 11:39 Long Text can store up to 2 gigabytes,
- 11:42 which is the limit for the size of an Access database.
- 11:46 You can also set the Text Format property to Rich Text,
- 11:50 allowing formatting to be stored as well.
- 11:53 Again, if you can find a way to store
- 11:56 this information externally, that would be wise.
- 12:00 You will have more control over it,
- 12:02 keep your database size smaller,
- 12:04 and reduce chances of corruption.
- 12:07 The Multi-Value data type allows you to
- 12:09 store multiple values in a single field.
- 12:12 In my opinion, it is best to create a related table for multiple values.
- 12:17 Multi-Value fields are hard to query and report.
- 12:21 Not using complex data types results in
- 12:24 lower chances for corruption,
- 12:26 and gives you greater control and flexibility.
- 12:31 Other data types listed here are not recommended when they can be avoided.
- 12:36 Data Structure is the foundation of all you build.
- 12:40 Choose data types and properties wisely.
- 12:43 I want to give a special thanks to Dirk Goldgar,
- 12:47 also known as 'Yoda' by those who revere him,
- 12:49 for his valuable input on this lesson.
- 12:52 In the next lesson, we will discuss
- 12:54 Relationships between tables,
- 12:56 and using the Relationships Diagram in Access.
Lesson notes are only available for subscribers.