we are at video three of our query series in microsoft access databases and we're going to be looking at the null and the like operators or features in queries so let's take this scenario where we've got a field which has different types of car models for a particular scenario and as you can see it's a text field it's got a lot of information it's got both the the year the car was made as well as the type of car it is as well as what type of fuel it uses so it's a whole lot of information all in one field and that's great but the problem is like if i want to find just the cars in 2019 that's going to be difficult because i can't say where the car model equals 2019 because there is no field that equals 2019 it's not 2019 exactly it's got some stuff after it and that becomes very difficult and i don't worry about because the 2019s all have something different after it so if that's the case we're going to have to use the like operator with wild cards now first of all let's learn what a wild card is the first the main role card that we can learn is the star now star is the symbol for nothing or anything so when we put in a star in a query we are basically saying that star can be replaced by anything or nothing we don't care what it is it could be anything it doesn't matter how many characters is it doesn't matter what it is that is the symbol for anything or nothing so if i am looking for all the cars in 2019 i know that the field starts with the 2019 the values start with 2019. so i want to 2019 in the beginning but i don't care what's after it so that could be anything that could be star so 2019 star would be all the fields that have a 2019 in the beginning and something or nothing after it now if you type that into a criteria or condition for for query it will put in automatically the like for you and the double quotes around it so just let you know what it's going to look like you can't type it in yourself but that's what the like does it doesn't equal 2019 star it's like 2019 star so the like will say we use the wild card what happens if i want to find all the cars that run on petrol well i know that the word ends with the word petrol so i don't care what's in front of the word i just want the back of the word to have the word petrol well if that's the case then i'll just have a star for anything and then the word petrol so that's how you can get all the fields that end in the word petrol and then the like will be put in for you with the quotes around it okay now so we know how to start with something and we know how to end with something but what happens if i want all the toyotas there you can see geotag no field starts with the gto the no field ends with you it's in the middle there somewhere but i know it's in the middle so i don't mind if there's something before it or something after the word toyota there could be anything before it there could be anything after it as long as the word teaod is in it well in that case we'll put a star then toyota then star and that way we will find all the fields that have the word toyota in it so there we go and it puts the like and the double quotes in for you so basically in summary if you want something or starts with something you have that something star if you want to end with something you have star something and if you want something in somewhere you will say star something so those are your three basic scenarios when you're using the luck and the wild cards so let's try a couple of examples i'm going to go to create for creating a query we're going to go to the design and i want the data table and so what do we want we want some surnames and some first names i'm going to go back to the data table and i want you to look at you see city we've got a whole bunch of cities here and they all have the states at the end and you'll see there's a couple of states there's like texas there's a couple of places here in texas and they'll end with the t and x at the end well that's if i want all the cities in texas and i don't i don't care what parts in the middle in the beginning of it i just want the the word texas at the end let's go back here we can go to city in my query and i'm going to go and say the criteria is i don't care what it is in the beginning so i'm going to put a star followed by the tx and then if i click away you see it puts the like in for me so now it's finding all of the fields that have something and then a tx at the end and there we go but list of all the cities that have a tx in it fantastic now what happens if i want all the names that begin with the letter b so then i want a b in the front and then a star after that and that should give me a list of all the names that begin with the letter b so there we go now what about in the middle well we can do let's say the surnames i want all the surnames that have an a n in it so star a n i don't care if what if it's if there's nothing in front of it then it could start with an a n i don't care if it's a at the end because the star at the back could have nothing or it could have something before and after the and then it's in the middle so let's start and start click away there puts the liking for us as i run there we go we've got a whole bunch of surnames there you can see there's a n in front and here the a n is in the middle and then a n is at the end and it does it for all of them you'll notice that it doesn't matter about the caps even though it was capital a it still found all of those even if it was a small a so it's not case sensitive so there you go so that's how you use the lock operator with wild cards sometimes you might have a field that you want to check if it's empty or not if it's got nothing in it now in computers we have a term called null and null means having no value now you can't say something is equal to null that doesn't make sense you can't say equals no value so that's not the way that we use the null operator you need to say is null so that's what you want to check so the fields or the criteria if you want to see if a field is empty which means it can it has nothing in it all the records that have nothing for that particular field you will then use is null maybe they left out a particular filter that's when you would use it now sometimes you want to check if when you want to get the fields that have something in it you don't want to consider the ones that are empty or in that case you want like the opposite of is null did we learn about a operator that we use that does the opposite well yes we did that was the not option so we can say is not null if we are checking if something is not empty it's got something so if we want all the people that definitely entered in this field we would use is not null so let's go back to our query i'm going to change this query to say emails i want to find all those that did not enter in an email how do i know if they didn't enter an email well the email will be have no value in other words it's not equal to null you can't do that it must be is null so you say is no so i want to find all the emails where the it is not where there's nothing in it so let's run the query and there we can see a whole bunch of people including myself mr long that did not fill in their email so there are four of us so if we go over here if we go back i want to find all those that definitely did have an email then i would say is not null so all those emails where there's something there and not nothing so now let's run it and there we can see all the emails are filled in there's 97 of them and how do we know if we got the right well we know there are four that didn't enter emails and there's 97 that did so technically if i take this out and i say just give me all those whether they've got emails or no emails there should be if i click go down see the records when it pops up boom how many records there's 101 records which is 97 plus 4 which makes it's correct remember through these videos i'm not actually saving my queries but it's also a good idea that you save your query so make sure that you save them and remember your your names your qry with whatever you want to save so this would be our null example if we wanted to do that okay so don't forget to save your queries say is no there we go so there we go there's my null example and then we can just save any changes for more videos on access as well as videos in this video series um go to our youtube channel subscribe like we'd love to hear from you so leave a comment follow us on twitter and facebook and remember don't do it the long way do it the mr long way