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.