Looking at how to deal with code characters in strings including quotation characters and wild card characters that you want to treat as literal text and the final part of the video looks at how to use some basic string functions in your criteria and we'll perform some examples such as comparing string lengths and making case sensitive comparisons so quite a lot to do in this one let's get started if you've been watching previous parts of this series you should be fairly familiar with the basic setup by now so you may prefer just to skip to the.
Next chapter of this video if you haven't seen previous parts of the series here's a quick overview of the basics of how things work we've got a macro enabled workbook which allows us to run a query by clicking the fairly obviously labeled button here on the menu sheet and when we do that it's going to extract some information from a separate excel file called movies the movie's workbook contains several different worksheets and tables with a variety of information about different films i'm going to keep the movies workbook open for the duration of the video just.
To make it easier to point out which bit i'm talking about but you're welcome to close down the movies file it doesn't need to be open for the code to work i've saved both of these files in the same folder and i'll pop a link in the video description so that you can download these files yourself and follow along and write the code if you'd like to i've already written a lot of code in the text criteria and the like operator workbook and a lot of that relies on microsoft activex data objects to establish the connection to the movies file i'm not going to talk about activex data objects in this video.
If you are interested in that part then we do have a separate playlist with a bunch of videos which explain how to do that i'd recommend starting with the one that says how do i get data from a closed excel file using vba just to give you a very brief look at the code i've already written in this main workbook the text criteria workbook if i head into the visual basic editor i've already set a reference to the microsoft activex data objects library if i head up to the tools menu and choose references you can hopefully see in here i've referenced microsoft activex data.
Objects 6.1 i've got a very basic subroutine that's triggered by clicking the run query button on the menu sheet and really all this does is constructs the string which represents our sql query and then passes that string into a separate procedure which deals with all the complicated stuff such as establishing the connection to the movies workbook it uses our query to set the source property of our record set object and then the rest of the code deals with writing that information out into a.
Let's have a recap of the basic way to write text criteria for the first example i'd like to return a list of films belonging to the science fiction genre so to do that we'll need to add a where clause to our query to ask where the value of the genre field is equal to the text science fiction if i head back to the visual basic editor i'm sure you probably already know how to do this if you've watched the previous video in the series you definitely know how to do this already we can add a where clause to our select statement and then i can write to the name of the.
Field genre i'll put this in some square brackets to indicate that it's an identifier the name of a thing then i can ask if it's equal to the text science fiction i've got a couple of different text delimiter characters i can use here when we're using the provider to connect to an excel workbook one way to do this is to write the word science fiction in a set of double double quotes i need the double double quotes because of syntax rules in vba if i try to contain these in just a single set of double quotes.
I generate a syntax error so the double double quotes are required in this case to make that work and then if i switch back to my menu sheet and i run my query i'll get a list of all the science fiction films you'll see if i head back to the visual basic editor i'm writing out the query we're using to return the set of results as well as the number of results into the immediate window if you don't have that displayed already head to the view menu and choose immediate window from there so you'll see the double double quotes that i've written in the code have been.
Translated into single double quotes i still think that's a little confusing to look at so i prefer to use single quotes or apostrophes to enclose my strings when i'm writing sql queries so if i replace my double double quotes with single apostrophes i can head back to the menu sheet and i can run my query again and i'll return exactly the same set of end results and you'll see the number of rows that's been returned is the same as well just one other basic thing to remember about text criteria.
They're not case sensitive it doesn't matter what combination of uppercase and lowercase letters i use to write these criteria it will still return the same number of results it's based on the spelling of the word rather than the case of the characters within it so just heading back to the menu sheet and running that one more time we'll see all the science fiction films again now we're going to talk about case sensitivity and how to write case sensitive criteria a bit later on in the video.
But for now we'll just accept that text criteria aren't case sensitive
You can also use different operators in your text criteria it doesn't always have to be an equals operator let's say i wanted to find a list of all of the truly awful films in the database i'll look for films whose genre equals awful and if i head back to the menu sheet and i run that query i'll return all the twilight films basically i think we're all agreed by now that these are truly awful films it's far more likely i'd want to exclude those films from my set of results.So rather than saying equal to i can change that to say not equal to with the open and closed angle brackets so heading back to the menu sheet and running that query again i'll return a list of all of the films except for those in the awful genre so it's 5 less than the total number of films in the database we can also write criteria to ask if one string of text is greater than or less than another string of text so to demonstrate that.
Let's change our criteria to reference the title column this time and i want to know if any film title is greater than or equal to x if i head back to my menu sheet and run my query again what we're basically seeing now is a list of all the films whose title begins with any character from the letter x onwards i think this is a little easier to understand if we sort our query results alphabetically if i head back to the the visual basic editor and i add an order by clause to my query i can say.
Order by title that will automatically go in ascending order but i can optionally say asc to make sure that's explicit and then if i head back to my menu sheet and run the query again if you could imagine your entire list of all the films sorted alphabetically wherever the letter x would appear in that list anything from that point to the end of the list is what you'd return it doesn't just have to be a single letter either you could have an entire word in there so let's head back to the visual basic editor.
Let's say we wanted to know uh we wanted to find a list of films that are greater than predator which of course is a nonsensical statement to make because as everyone knows there is no film greater than predator however we can ask to see any film in our list whose title is alphabetically greater than the text predator just to clarify what i mean by this so heading back to the menu sheet we can run the query again and they're all sorted alphabetically i'm absolutely positive that pretty woman is.
Not greater than predator but alphabetically pretty woman is the first film in the list which appears after the film predator we can also if we can say things are greater than or less than another string of text we can ask if text falls between two bits of text so if we head back to the visual basic editor rather than saying greater than this time let's change the greater than symbol to say between then i'm going to use two.
Different bits of text i'm going to go with we could just do single letters let's do something a bit more interesting let's say between the devil and then and as a separate bit of text a deep blue sea or a rock and a hard place or me and you uh whatever you prefer your two bits of text to be alphabetically anything which falls between those two bits of text is what will be returned to our list of results if we head back to the menu.
Sheet run the query again if the the deep blue sea was a film in our list that would appear above the deer hunter and if the devil was a film in our list that would appear below the departed we can also use the in operator with text values and this is useful when you want to check if the value of a column is equal to at least one of several different bits of text so let's just return to the example of genres for the time being i'll say where genre and not between i'm going to say.
In and then in a set of round brackets i could type in a comma separated list of bits of text i'm just going to get rid of the and the deep blue c part here and the first bit of text i'm going to look for is science fiction just as we looked at earlier on if i just close the round brackets there at that point that's essentially the same as saying genre equals science fiction if i however added an extra item inside the round brackets separated by commas.
I could see all the films where the genre was science fiction or fantasy
And then i could type in another comma and i could go for another genre altogether let's go for action that was supposed to be typed in in single quotes let's try that again there we go and you can have as many different items in that list as you like it's a lot shorter than writing where genre equals science fiction or genre equals fantasy or genre equals action which is what we would have to do ordinarily so heading back to the menu sheet bring.The query again and get all the actions science fiction and fantasy films in the list you can reverse that as well by applying the not operator in front of in so i could ask where the genre not in one of those three and if i head back to the menu sheet and run that query again i'll get everything except for action science fiction or fantasy i could have applied the not operator to the between option as well so we could say not between the final thing we did with text.
Criteria in the previous video was to have a brief look at the like operator and wildcard characters now that's going to be a big focus for the things we'll do in this video so let's again have a quick recap of the basics of how those work i'm going to change my criteria to look at the title column again and then rather than saying not in i'm going to change that to use the equals operator then i'm just going to take away the set of brackets and the comma separated list of values and i'm going to look for any film whose.
Title equals the word dragon so having done that if i head back to my menu sheet and i run my query again there's one single film whose title exactly matches that bit of text what i'm now going to do is change the operator that i'm using there from equals to like now at this point it won't make any difference to the end results when you're working with text and you're using basic bits of text like this you can happily change the equals and like operator and they will do essentially the same.
Thing if i switch back to my menu sheet and run the query again it's still returning the one single film that exactly matches that string but using the like operator means you can also include wildcard characters in your strings the main wildcard character you're likely to encounter is the percentage symbol if i add a percentage symbol after the word dragon the percentage symbol represents any number of any characters including no characters at all so if i head back to my menu sheet it's.
Going to essentially return any film whose title begins with the word dragon even if dragon is the only word in the film title i can change the position of the wild card character and i can also ask for things that are not like that pattern as well so heading back to the visual basic editor rather than saying like i could say not like and that would return if i run my query again that would return all the films that do not begin with the word dragon so it should be 4 less than the 1200.
Films we return from the entire list i can change the position of the wildcard character as i said let's take away the knot and replace or place the percentage wildcard at the beginning of the string rather than at the end so that's anything that begins with any string of text and ends with the word dragon so again heading back to the menu sheet i can run that query again and find any film whose final word is dragon i can include multiple wild card characters in the same string if i like as well so heading back to the visual.
Basic editor i can put a percentage wildcard both before and after that specific word and then if i head back to the menu sheet i can run that query again and you can even come up with more complicated patterns of text the wild card characters can appear anywhere in the string so let's say you wanted to i know play a party game or feel like you're on a game show or something like that we could say find me any films whose title matches the pattern of the something of the.
Something so using wildcard characters one in the middle and one at the end of the string and if i head back to the menu sheet and run the query again anything which matches that pattern with beginning with the word the followed by anything at all before the phrase of the and then finishing with anything at all as well again there's no real limit on the number of wild cards and the complexity of the string you build so let's just change this a little bit say.
The something there's something and the something how many films can you think of whose title matches that pattern if we head back to the menu sheet run the query one more time for the time being there we go two films in our list which match that particular pattern now let's look for films whose titles contain one of two different words we've looked for dragons previously now let's look for tigers as well sadly when we're using the like operator and wild card characters there isn't a.
Convenient way to do this in a single criterion like we can with the in operator just to demonstrate what i mean by that let's try changing our criteria here so i'm going to take away the word of the from the beginning i'm going to look first of all for the word dragon inside one set of double quotes sorry inside one set of percentage symbols and then within the next set of percentage symbols look for the word tiger as well now that's going to look for any films whose title contains the sequence of words dragon followed by tiger.
In some place in the full title if we head back to the menu sheet and we run that query we'll see we get all the films that contain dragon followed by tiger there's just the one of them there could be any number of characters between the words dragon and tiger of course they just happen to appear right next door to each other let's try going back to the visual basic editor and let's change the position of the two words so i'm going to change dragon so that that sits at the end of the string so let's take away dragon.
From the beginning so we'll go for tiger first and then dragon at the end make sure there's just one single percentage symbol in between the two and then if we head back to the menu sheet and run that one again there are a couple of extra results this time so there are multiple films with the word tiger followed by the word dragon in the title but what i'm really trying to do here is i'm trying to find any films whose title contains either dragon or tiger so in order to make that work.
Back in the visual basic editor i don't have any choice here i can't use the in operator to do this so i couldn't for example say title in and then in a set of round brackets have tiger followed by dragon each in their own set of single quote characters so tiger dragon wrapped with percentage symbols this doesn't work what this is going to try to do is find any film whose title.
Is equal to percentage tiger percentage or equal to percentage dragon percentage and of course as you might expect there aren't any films in my database with those exact titles so the query return no results to make this work there's no combination of in and like that we can use here so i can say like in or in like it simply doesn't work the choices i have or the one choice i have i suppose is to ask for films where the title.
Like tiger or and then a completely separate criterion i can say title like dragon and just get rid of that extra closed around bracket there so two completely separate criterion dealt with individually if i head back to the menu sheet and i run my query again i'll get any film containing one of those two words if i wanted to use that same technique to find films that contain dragon or tiger in any order i like.
Then i can head back to the visual basic editor and then simply change the word or to the word and and as long as the word tiger and the word dragon appears in the film title then that will return a result and i don't need to worry about which order i've done that in i get dragon tiger gate and crouching tiger hidden dragon other thing you may need to be careful of when you're using wildcard characters is distinguishing between a sequence of characters and an actual word to demonstrate what i mean by that i'm going to take back my where clause to.
Just a single criterion and i'm going to look for any films that contain k-i-n-g or king if i head back to my menu sheet and i run my query i return any film which has that four-letter sequence somewhere in its title so as well as films which do contain the actual word king like king arthur king kong etc i've also got the four-letter sequence k-i-n-g wrapped up in other words like smoking and talking if i wanted to find films that contain the actual word king then i've got a few things i could.
Try to do to make this work back in the visual basic editor one simple thing to do would be to stick a space either side of the word king and that guarantees that my character sequence must isolate those four characters into a separate word k-i-n-g surrounded by spaces so if i head back to the menu sheet and i run this query again that does indeed return only films that have the four-letter sequence k-i-n-g with spaces either side of it but then i.
Lose things like king kong and king arthur so if i wanted to bring those back as well then i have to consider that the word king might be at the beginning of my film title it could also be at the end of my film title and it could be the case that the word king was the entire film title just like the word dragon was earlier on so to make that work we've got quite a few different individual criteria to test so what i'd need to do is check where the title is like king as i've done so.
Already i'm just going to copy that criterion and then say or and then for the next one i could check that the title begins with the word king so that would be k-i-n-g followed by a space then a percentage symbol then i could type in the or keyword again and type and paste in the same criteria so this time i could check that the word king fell at the end of the film title so that would be percentage space king and just to be thorough.
Let's add one more or paste this in again and if i wanted to check that the film's title was just the word king i could take away the wild card characters entirely as well as the spaces and then say either like king or equal to king so four separate criteria being tested contains king begins with king ends with king or is king it's up to you whether you.
Prefer like or equals here and they will both do the same thing okay so one last quick check of that if i head back to the menu sheet and i run that query again there's all of the films that contain the word king either at the beginning somewhere in the middle or at the end or is just solely consisting of the word king as our criteria are becoming a bit longer at this point it may be worthwhile taking the time to separate.
The different keywords of our select statement the different clauses onto separate lines in our code just to make it easier to modify the where clause so let's take a little bit of time to just do that i'm going to use a space underscore character after the equals operator and then take the select keyword down to its own separate line just before the from clause so i'm going to include a space here after the asterisk i'm going to close some double quotes and then concatenate a space underscore character before taking the from clause down to its own separate line.
Then again just before the where clause including the space after the film table reference or film worksheet reference i should say i'll close some multiple quotes and then concatenate a space underscore continuation character then i have the where clause on its own separate line it's still a little bit too long at this point but we'll solve that in just a moment i'm going to head over to the end of the uh where clause just before the order by clause close some double quotes concatenate a space underscore and then open the double.
Quotes at the beginning of the next line finally i'm just going to take my where clause back down to just a single condition so i'm going to take away everything after the first like king and then we're ready to start modifying the where clause and have a slightly easier time of things by being able to modify that one single line rather than work out which bit of the entire select statement we need to change so we've seen a few examples of using the percentage wild card the other wild card character you're.
Likely to encounter in sql is the underscore the percentage wild card represents any number of any characters including no characters at all as we've seen each underscore wildcard character you type in represents any one single character so it could be a letter a number or a punctuation character if i type in two underscore characters in a sequence the difference with the underscore character is that there must be a character in that position the underscore can't.
Represent no characters at all so this one here with two underscores together is asking to show me any film whose title contains exactly two characters if i head back to the menu sheet and i run that query i'll find any films with exactly two characters in their name and that could be both letters both numbers or a combination of the two and if there were any with punctuation characters in there they would be included as well heading back to the visual basic editor let's add an extra while.
An extra underscore wild card so that's three now if i head back to my menu sheet and i run that query again that's going to return all the films with exactly three characters in their name we can also include other specific characters along with the wild cards so currently this is showing any films with exactly three characters so that could be any character at all or any three characters at all the fourth character i want to be a space so i'll type in a space and then i'm.