🥳 GOSKILLS TURNS 10: Get 10 days of free access with code 10YEARS

GoSkills
Help Sign up Share
Back to course

Data Type Review

Compact player layout Large player layout

Locked lesson.

Upgrade

  • Lesson resourcesResources
  • Quick referenceReference
  • Transcript
  • Notes

About this lesson

A review of Simple, Special and Complex Data Types.

Exercise files

Download this lesson’s exercise file.

Services_17_DataTypes.accdb
1.4 MB

Quick reference

   Data Type Review

Login to download
  • 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.

Date/Time Data Type
05m:22s
Relationships and Relationships Diagram
07m:30s
Share this lesson and earn rewards

Facebook Twitter LinkedIn WhatsApp Email

Gift this course
Give feedback

How is your GoSkills experience?

I need help

Your feedback has been sent

Thank you

Back to the top

© 2023 GoSkills Ltd. Skills for career advancement