VBA SQL Strings - Tutorial for Beginner

the instructor with strings and VBA particularly with sequence strings we look at that childhood watch this video I am fitted from Kosovo net.com today I would like to talk about strings and VBA how to handle strings inside the VBA editor how to work with them if you need to embed literal string values inside your BBA code if you want to make that readable as but along the strings how to include the values of variables and return values from functions inside those strings how to use all this later on in sequel and yeah I think we will will cut quite a lot cover today and I will start with a very very basic and simple example because I can show you the important stuff there in a single context without all those additional additional complications of sequel and once we got the basics covered we will move on to to the sequel stuff so even if that looks a little bit too basic in the first couple of minutes stay tuned it will all fall into place later here we are now I start with a really really simple example I'll use the message box function because that way you will see the output quite clearly now we need to use a string as a prompt as a text for the message box so we start a string inside VBA with the quotation mark if I would just enter this then would not work would give me an variable map define general so I need to start my string with a quotation mark and then I can type happily away this is a text message quite simple and now is the end of the string and I need to end this string with another quotation mark so now we can run this and we'll see the string that's pretty pretty simple now we know the quotation mark encloses the string it tells the video editor where does a string begin and where does it end so obviously it is a problem if we want to use the quotation mark inside the string if I say this is the test message about the widget and I want to use quotation marks around the foot widget and just type them there and run this I obviously get an syntax error and that is because BBA thinks the string ends here and then there's something after that string and it does not know what to do with it and hence a syntax error if you want to use quotation marks inside a string we need to double them so I type two quotation marks here and they will end up as one quotation mark inside the string and then I need another two quotation marks here for the ending quotes inside the string and then I need to add another quote mark to properly end the string so you see the full widget here is enclosed in quotes inside the string and they are only there only one quotation mark at the beginning one at the end if you look at the text this looks already a little bit messy and I repeat these are two quotation marks that should end up inside string and another one to really properly end the string okay so far so good so good this works now let's get rid of this and now we want to incorporate a date or any value inside the string and choose a date and you know or you should know you can use the gate function to get the date in VBA now I just write gate and follow my breakfast inside my string and I run that and you see it is inside the string and it's just treated as text so if we want to use the date inside the string we need to end our string and then concatenate the return value of the function and to cook concatenate strings in vdab use the embers end symbol this one and if I now run this I see the date and if you notice the date format I'm in Central Europe that is our date format so it's day month year it will display the date format configure for your system so as that is if you are in the United States or anywhere else with a different date format it will automatically use your default date format okay so if we if we want to end some tanks after that message we have to use the MS end again and start another string here and add some additional text so we now concatenate it to constant string values with a date and here comes the second string value so far so good now what if we need to use any variable inside string that is absolutely essentially the same if I declare a state and take and then assign it as a value of the date function now can just copy that variable name in this position run it and the result will be exactly the same and of course we can use any day here to not try to that function value we can say this is a date and works all the same now what about long strings in VBA get rid of this and now i will copy a very very long string inside here just the lorem ipsum sample text and i just copied it now I can run it and you'll see it's a proper locks pretty long text now so it absolutely works but if you look at the VBA window you see a problem you can't read the full text here you have to scroll and scroll and scroll to reach the end of the spring and read the whole message text that is quite annoying if you look at the code and want to see what is at the end of it you almost have to scroll and that is a bit of a pain if you look at the VBA code what we can do now we can end the string here use the concatenation sign and defend and we use the underscore that tells VBA that this instruction is going to continue on the next line then it would just put the rest of the string here and we do that again here and one more time here and now I can run that the output message is still exactly the same but I can read it without scrolling horizontally here in the VBA editor now you should always pay attention if there are spaces you should make sure they are somewhere in the spring he is always that space does not matter if I put that space and the next line here it will end up all the same and the message does not make any difference but you need to make sure the space is there so what about this I just add the next line of that central text and it is supposed to go on a new line in the resulting message now look at the text of the 9th Rivera or however that is proposed pronounced so it just is added here and NASA space missing and it's on the same line that it's supposed to be on in a new paragraph now what we need to do now is explicitly tell VBA to make a line break inside that string and the proper way to do that in my opinion is to use the BB crlf constant that is just telling be gay use line feed and CIS carriage return and LS is for line feed because on Windows we we use those two symbols for a proper line braid so you see this constant is recognized and now I can run that code and you see here's line break and that start on a new line as I intended okay that was the very simple sample we have learned some very important facts about frills and VBA you enclose strings in quotation marks if you want to use quotation marks inside the printer to double them if you want to break up a string inside Visual Basic you need to end this frame with a quotation mark use the envers end to concatenate it with whatever is coming next and then if the next part is a variable or function you just write the function name or the variable name inside the code and if you want to add additional text after that you will again use the endless end and another string after that to concatenate all that to one continual string if we want to break lines or to rather want to break one long instruction in VBA and want to display that on multiple lines we need to use the underscore and we will put individual strength on each line and finally if you want to use a real line break in the output of the message we have to use the VDC are less constant to concatenate to insert that line break into that string okay let's look at some real sequel examples now and prepare the little database with a customer table with from customer data in there and I prepare the order table with some order data in there we are going to use all the data first so I just show you that an order ID that the numeric primary key meant that the customer ID that is numeric as well and it references the customer that's an order date a date obviously and there's order status and this order status is text it's real text inside the table and that might not be ideal in a real-world solution but for the sake of this sample I really wrote text status values inside the table now let's switch to the VBA environment I prepare the tiny function I declare string sequel and I assign a sequel command a very simple query to that spring and then I pass that to a helper function show data set not going to show that now that is just displaying the data and we're just running this and this result this message box it's just a helper for me to quickly switch between the query results and the VBA environment now you see just the order data as it was in the table so so far that is pretty plain forward now we want to reduce the data in the query and only displays order that are currently in progress so we say where all the data equals in progress now if I run that I get a syntax error and that is because we need to tell the database engine who's running the query that this in progress should be treated as a literal value as one string inside the string and we can do that by using the quotation marks inside our strings but you remember from the simple demo in the beginning we need to double the quotation mark for this to work that we finally end up with single quotation marks in our string now let's run it it works we only get orders with order status in progress now let's assume we don't want to write this in progress right inside of string but in technique variable order pages and we assign this to our order status variable and now we want to use the variable in here we learned how to do that we need to concatenate the strings or we concatenate the string with our variable value so we need to end the string here and you see we already got two quotation marks here we need to put another one in here to end the string now here goes our variable order status we use the ampersand to continue the string and now we need one quotation mark to tell a BBA now here's another string then we need two quotation marks to put one quotation mark in the resulting string and we need another quotation mark to end that tiny string here now we run that it still works but obviously these quotation marks here they are pretty messy and it's pretty hard to see is that correct have I the correct number of quotation marks here so that is not ideal in my opinion now the quotation mark works inside and sequels bring to delimit literal string values in criteria expressions but sequel originally uses another value to delimit the literals and that is the single quotation open it he has the single quotation and we can use the single quotation here and now we see it still works and this looks much cleaner we we see what's happening here we have our string and there's a quotation mark here the single quotation mark that ends up in the string when we end our video string concatenated with the variable to embed a variable value inside the string and then we concatenate another string that just contains the single quotation to delimit the literal inside the string and whenever you are not perfectly sure what is your final sequel string looking like I recommend you output the string first that is the reason I declare a string variable and find the string sequel string to that variable and then use that variable to for further processing because now I can just say debug debug print sequel and run this and it still works but I get the finished string in here and I can now much more easily see how is the string looking if we want to query all the count orders for one particular customer it's actually a bit easier we just can query for customer ID and we just can coffee can just put a literal value inside the string for customer fall can just run that and we will see the orders for custom ID for now if we want to use a variable here we can use numeric variable because it's a long value and long integer and now we we concatenate that here so that is quite easy no messing with the string termination mass inside the sequel string we can just run that and it still works now let's look at another data type the date and I actually prepared a little bit here now just insert that I declare a variable first a current month as date and then I assign the first day of the current month to that variable obviously I used the day serial function and that takes the year and I supply the current date for after month once again for the current date and a literal for the day and I just have told it one here so that will always them get the first day of the current month now we are going to use the first day current month variable to filter our order by order date and we only want to see orders that are entered in our current month so we changed the comparison here for greater equal and if this value is a date we need to change the delimiter access sequel expects the hash sign for date value so we use that here and enter it and now we run this query and we get an syntax error and that is because of my date format as mentioned earlier I've got the European date form of the German date format configured here which is day month year separated by dots and the exes database engine only understands the United States date format or the international date format so if you've got your computer configured for the United States this might actually work this way but nevertheless I strongly recommend that you explicitly format your date to work regardless of the current local configuration of the computer to do that we are going to use the format function and I'm not going to use the United States date format because that is fairly confusing for me instead I use the international date format which is a four digit year - two digit month - to dig it day and now let's rerun the query and it works and you see we get only orders that have been entered in the current month there's one data type left we should look at in more detail and to do that we need to use the other table in the database and I'm going to come copy this value here the total order value and that is obviously a currency value now what I'm going to show you why use the data type currently as a sample but it does apply to all decimal data so we add this in here and you might spot the problem with what we are going to deal right now right away when we write decimal values right into the code we need to use the dot of of the decimal separator and so we we need to change the table name here and the column name over here and we get rid of this so as with numeric data before we can just concatenate our strings using the variable we do not need any delimiter but if I run this it is not working and now once again you see how well you able it is to see the resulting string you can spot the error right here when converting that decimal value from number to string when concatenating it with the spring by default my local settings for decimal for the decimal separator were used and that is the comma not the dot so we got the problem here that it creates a value that cannot be properly processed by the Access database engine so we could use in theory we could use the format function to change that formatting of the decimal value but unfortunately in practice it does not work even if I explicitly define a format you using the dot s decimal separator it will not work because that is overwritten by by my machine settings and that will end up a comma in the string anyway so we need to use another function here now if the function STR for spring and that function is converting a numeric value to a string and it's doing that totally ignoring the decimal separator for configured for the computer it always will use the dot at the decimal separator so now if we run this you see the query works and if you pay close attention to this value here that is the value we just wanted to include in the results but there was a another record with a slightly lower value of 100 1965 that is excluded so you see it really worth filtering on decimal digits and if we look at the query string here then meshed up a little bit you see now the correct value is used with the Dom F decimal separator until now I just wrote all sequel queries right into the VBA code editor but I'm totally aware that this is probably not what you are going to do you would probably rather create your query inside the access query designer like this berry here and now you want to use an existing theory or the sequel of an existing theory inside your VBA code so you can do that pretty easily by switching the query designer to sequel you and then just copy the sequel text inside the VBA editor now I'm going to insert it here and not going to need this anymore now I've got this query inside the sequel inside the VBA editor and assigned to my sequel spring and I can run this query and now you see act before the results here just a query joining all the customer data to the order data and there are several customers in here multiple times because they had multiple orders pretty simple now you see an obvious problem right here I need to scroll far far to the right to see the whole text of that query so I'm going to apply the the changes to the string as I did before the simple message box example I'm going to wrap the lines inside the VBA editor by terminating the strings here using the embers end and underscore to indicate that this statement continuous on the next line and so I'm going to work on the statement until this is all in plain view without any scrolling necessary so I can run it still works but it is readable on first glance inside the VBA editor window now I would recommend that you do some additional formatting I usually format my sequel strings to be more readable and so on would just add an additional line break here and an additional line break right here after that on statement and I would indent that a little bit further inside the string obviously he has an arrow here in lifting and ascend and now I see at least the the joint expression in here much more structure than I can much more easily recognize what the critical part of that query is about and all the same if I act and wear condition here we're just very far customer ID then I would write the where condition on a new line and all the same if I would add additional additional criteria in here like this one and wants to be to total order value like this so all the the critical part of the query are lined down here so I can much easier recognize what the query is about if I would write a sequel query inside a sequel editor I would probably put all the fields on on line on its own each but inside the VBA editor I think it's enough to just wrap the critical expressions of the query in in this way that all critical expressions start on a new line so it's much more readable now I want oh let's run this very first because I spot an error here it's not going to work now the customer ID are used here that is basically correct but you see the customer ID is used here from the table customer and it is as well in the table order and I can't use it just without any table prefix here because it's ambiguous the database engine does not know which table that customer ID column is coming from I'm referencing in the criteria so I add the TBL customer in front of that now that query works except that I obviously did did something wrong I mistyped here total order value now this way it's going to work so easy to copy an existing sequel string inside the VBA editor and enhance it in there now you remember the thing I told you about the blanks inside the sequel string so pay attention because that is an error that I see quite frequently if I remove that space here and remove the space here then it still looks valid inside the VBA editor because I might not recognize the missing spaces but if I run this query I'm going to get an error just delete all that stuff now there is an syntax error and if we look at the completed statement we have to scroll here quite a bit as well it's easy to spot that has been concatenated and there's a space missing in our blank nothing and then it's a frequent error so I would suggest that you insert a blank on the end of each line and you insert another space at the beginning of each line so you can spot errors more easily and you get a bit of a safety net by using two spaces here but does no harm at all using an additional space but if you miss the space then it obviously results in a syntax error so now all working again great I think that's all what I wanted to show for today so we're almost done just want to do a quick recap we learn today how to incorporate literal values in your strings and with numeric integer values that is string values and date values we learned how to easily copy a sequel strings inside the VBA editor and reformat that sequel text to be properly readable we learned a little bit on how to debug strings and I think that's quite a lot for today thank you very much for watching and I hope you liked like the video and please if so do me a favor and hit the like button or subscribe to my channel on best you do both and thanks for watching have a nice day bye [Music] Oh [Music]

Channel: codekabinett.com/en

Views: 56,134

Keywords: VBA, SQL, Query, Access VBA, data types, String concatenation, SQL String, VBA String, Criteria, Date Ctriteria, String Criteria, SELECT Query, SQL Query

Id: c2_fEdFBj_Q

Length: 36min 13sec (2173 seconds)

Published: Wed Mar 22 2017