Access datatypes - a definition of datatypes in Access 2007 2010 2013 2016 tutorial for beginners

next I want to talk about the different data types in a Microsoft Access uh table when you're making a table you're going to supply the field name and then on the next column it's going to uh ask for the data type so here's the data types I'll give you some examples of each one a text field is the most common field that you're going to use it's for normal General types of fields like uh names or address or the city or the state a text field could be any combination of numbers letters and special characters on the keyboard and they can be up to 256 characters long the text field is the one that you use most of the time now when you need longer Fields bigger than 256 then you're going to use a memo field a memo field is uh going to be it's is also can uh be text or numbers or special characters but a text fi can be up to 65,000 characters long so you're thinking why would I ever need a feeli that's 65 car 65,000 characters long well you'll probably never use the full length of that however if you need a feeli that's bigger than a 256 then you have to go with a memo field because a text field only goes up to 256 characters so examples of a memo field would be like a notes field or a history or a log or a description field next you have a number field a number field is exactly what it says says it's going to be some kind of number any number that you might use in a mathematical calculation like a tax rate tax rate is going to be a number field so uh a number can be a positive or A negative number it could be a whole or a decimal number so uh notice down here the the examples are a tax rate miles per gallon quarts units square feet you know anything that uh is measuring something or anything that's going to be used in a mathematical calculation would be a number field uh next we have a date and time field so this is going to be a field that contains either a date or a time or both so a birth date an anniversary date an invoice date a start time and end time all of those would be the daytime data type there's advantages to using the daytime data type let's say you have a date field like a birth date and when you try to do data entry into there access will not accept an invalid date so if you try to type in February 30th for example there's there's no such date not even on a leap year so access won't accept that when it's a date and time field so it has that validation built right in another great example of when you want to use a date and time field is let's say you want to compare two different dates like a start date and an end date if they're both date and time type of fields then we can calculate how many days or weeks or months or years are between those two dates so you definitely want to use the date and time data type for your dates and times now uh these these examples that I'm showing you are actually from a book that I wrote called getting real results with micros with uh with access queries and uh or it's actually the official title is getting Real Results Microsoft Access and you can actually purchase that from the skill caddy website that gives you maybe you know the text of a lot of the access videos that we're seeing here and plus it goes a little bit further the next data type is a currency data type so a currency is exactly what it's going to say it's going to be some kind of money uh maybe um a t a a salary or a unit price or an invoice amount anything that would involve uh money so it's going to be similar to a number field except it it's going to have dollar signs and also decimal places uh and then we do have ways to change that to the international currency symbols as well all right so anytime you're thinking about money uh as as a as a field then you would use a currency data type the next one is an auto number the Auto number is going to give you a different sequential record uh excuse me a different sequential number in each record so the first record would be one then two then three then four um it's uh so that might be used for like a customer number an employee number or invoice number it also gives the record a unique identifier a unique identifier would be like something like a social security number or a part number if your record doesn't already have a unique identifier it's a good way it's a a good idea to have one and a good way to add that in there would be an auto number field that way we know that each of those will be different for each record then we have a field type that's called yes and no yes and no is like a true or false or an on or off type of situation so let's say you have a customer table back at work and each customer you want to see if they're active or inactive so what I would do is maybe call the field active and make that a yes or no data type or here we have is the product discontinued is it seasonal yes or no uh so those come up a lot uh the next one I really wouldn't use to much it's kind of outdated but it's for the Legacy databases it's called an ol uh o object o is an acronym it means object link and bet it now what that means is maybe you can put a different file into your database maybe an Excel spreadsheet a JPEG or bitmap or a PDF even or a Word document now the the reason uh that I wouldn't recommend that you use them is that it's actually going to incorporate that entire file into your access database making your database a lot bigger in size in general the larger your database is uh in physical size the less efficient it's going to run so if you have an early object it's going to make your database a lot bigger in size make it less efficient a better way to do the same thing is the next one which is called a hyperlink a Hy hyperlink is a clickable field which um can be a website address a file name or an email address and then when you click on that hyperlink either it'll either start an email to that person it'll open up uh the file on your computer or it'll be um going out to the internet to that website so here's the difference the Olay object is going to uh import that entire file into your database making your database a lot larger the hyperlink is just the file's name so then when you click on that it'll be able to open up that file but the file is still external to the database that's a lot better than using the Olay object sometimes you will see the Olay objects in Legacy databases but for newer databases I would use a hyperlink so um now there's going to be a data type that's called the Lookup Wizard and uh there'll be a video about how to use that uh in this series so that gives you an idea of how to use your data types in Microsoft Access