Follow Us

Fix Quotes in Text Strings - Part 2

The previous post discussed how single quotes (like apostrophes) can confuse SQL code and cause syntax errors.  Single quote errors are perhaps more common (emphasis on "perhaps") but they do not hold an exclusive right to the Heartache and Discontent trophy. Double quotes appear in text strings, as well, and though the issues they cause are similar, they deserve separate attention.

DOUBLE QUOTES IN TEXT STRINGS

For this second example, suppose you have a bit of code that includes text from a Comments field.  Because single quotes are common, especially in comments where contractions (don't or isn't) and possessive nouns (Fred's or customer's) are used, you may choose to wrap such text strings in double quotes.  MS Access allows double quotes in SQL.  (SQL Server... not so much.)

strSQL = "SELECT * FROM tblCustomers WHERE Comments= " & """" & me.txtComments & """"

First of all, I typically create a public constant in my declarations module for the entire project that includes the following:

Const Q As String = """"

Thus I can rewrite my code this way:

strSQL = "SELECT * FROM tblCustomers WHERE LName = " & q & me.txtComments & q

Let's further suppose that the comment includes the following:

 The customer quoted Tom as being "a top-notch salesman"

Now we have a similar issue as in the last post.  The resultant SQL code has an early termination of the quote and then it has a few incorrect command words and then another opening quote without a closing quote.  We've got more errors than a little league game.

The FixQuotes() function, used in conjunction with the Quote() function, can help here, too.

The FixQuotes() function escapes the single double quote using four double quotes.  SQL sees the four double quotes as an actual double quote (again, NOT the same as two single quotes) instead of the termination of a quote string.  

Thus we can rewrite our code to look like this:

strSQL = "SELECT * FROM tblCustomers WHERE Comments = Quote(me.txtComments, true)

Note the inclusion of the optional boolean argument.   This tells Quote (and, in turn, FixQuotes) to replace double quotes instead of single quotes.  

I hope this helps!  Happy coding...

Fix Quotes in Text Strings - Part 1

I mentioned the FixQuotes function in the last blog, but it deserves a bit more focused attention.  The issue of quotes in text strings that ultimately get passed as variables to query strings is one that has caused much consternation in my experience. Since single quotes and double quotes can both appear in code for various reasons, the FixQuotes() function will get TWO doses of attention.  

SINGLE QUOTES IN TEXT STRINGS

For this first example, suppose you have a bit of code that constructs a WHERE clause based on a customer's last name:

strSQL = "SELECT * FROM tblCustomers WHERE LName = '" & me.txtLName & "'"

This snippet will work swimmingly as long as the customer's name isn't something like "O'Neil".  In such a case, the resultant SQL code that gets generated will have a critical flaw that will cause a syntax error:

SELECT * FROM tblCustomers WHERE LName = 'O' Neil'

The statement now tells the query engine to look for someone who's last name is "O" and then perform the "Neil" command.  Oh, and then open another quote, but don't close it.

The query engine responds with, "Wha...??"  or some similar "I can't do that, Dave" -type response.

The FixQuotes() function, used in conjunction with the Quote() function, attempts to mitigate such issues.   First, let's talk about the FixQuotes() function.

Function FixQuotes(strToFix As Variant, Optional DoubleQuote As Boolean = False) As String

    If DoubleQuote Then

        FixQuotes = Replace(Nz(strToFix), """", "'")

    Else

        FixQuotes = Replace(Nz(strToFix), "'", "''")

    End If

End Function

The FixQuotes() function simply escapes the single quote using two single quotes.  SQL sees the pair of single quotes (NOT the same as a single double-quote!!) as an actual single quote instead of the termination of a quote string.  We will address the optional DoubleQuotes argument in the next segment.

Function Quote(StringToQuote As Variant, Optional DoubleQuotes As Boolean = False) As String

    Quote = "'" & FixQuotes(StringToQuote, DoubleQuotes) & "'"

End Function

The Quote() function is just a handy programming keystroke saver that incorporates the FixQuotes() function to make it easier to wrap strings in quotes.  Thus:

FixQuotes("O'Neil") returns O''Neil and Quote("O'Neil") returns 'O''Neil'

For example, the following snippets show the graduating simplification of the code:

x = "O'Neil"

This one returns an error

strSQL = "SELECT * FROM tblCustomers WHERE LName = '" & x & "'" 

This one is OK, but a bit cumbersome:

strSQL = "SELECT * FROM tblCustomers WHERE LName = '" & FixQuotes(x) & "'"

Quote() wraps the string variable in quotes, fixing any quotes in the string

strSQL = "SELECT * FROM tblCustomers WHERE LName = " & Quote(x)

Adding these two functioins to your utility module will save you from SQL errors caused by spurrious quotes in your text strings plus simplify and streamline your code.  Look for Part 2 where we'll get into the double quote replacement aspect of these functions.

Formatted Message Box in Access

Fancy Message Box

How many times have you wished that Microsoft had not done away with the fancy formatted Message Box that we loved in Access 97?

I cannot count the times, myself.

Here is my solution - a replacement function that performs an Eval() on the classic MsgBox() function as a string expression. The Eval function then returns the formatted message box like we all love and miss (at least those of us who used it all the time).

The code is fairly simple. Taking a cue from a forum entry on the Microsoft Access Help Center, I created the function below that replaces the Prompt with appropriate code to create the formatted message.

A couple of important notes:

First, I used the tilde (~) instead of the at (@) sign because e-mail addresses are often included in my messages.  Substitute your favorite seldom-if-ever-used character if the tilde doesn't work for you.

Second, I have VERY often encountered issues with single quotes in string variables.  Apostrophes appear in contractions, Irish names, possessive nouns, and a variety of other situations that may end up as part of a text string that you want to put in quotes.  The Quote() function is a simple one-liner that references another short function called FixQuotes().  These two functions (also included below) ensure that quotes inside quotes don't confuse the issue and cause premature termination of quoted text because of a spurrious quote in the middle of your text string.

Happy coding!

' Replace MsgBox with a formatted version like the old-style messages

' The message box at the top used the following code:

' Msg_Box "Formatted message boxes were great!~Why did Microsoft get rid of them?", vbOKOnly Or vbExclamation, "FORMATTED MESSAGE BOX"

Function Msg_Box(Prompt As String, Optional Buttons As VbMsgBoxStyle, Optional Title As String = "Microsoft Access")

    Dim strPrompt As String
    Dim aPrompt As Variant

    ' If there is a delimiter, then replace the delimiter with the appropriate symbols
    ' otherwise, just send the prompt out as it is.

    If InStr(Prompt, "~") > 0 Then
        aPrompt = Split(Prompt, "~")
        strPrompt = aPrompt(0) & "@" & aPrompt(1) & "@@"
    Else
        strPrompt = Prompt
    End If
    ' Wrap the prompt and other parameters in an Eval to get the appropriate
    ' formatted output. The Quote function ensures that any apostrophes or
    ' other single-quotes are handled without crashing the output.

    Msg_Box = Eval("MsgBox(" & Quote(strPrompt) & "," & Buttons & "," & Quote(Title) & ")")

End Function

 

Function Quote(StringToQuote As Variant, Optional DoubleQuote As Boolean = False) As String

    Quote = "'" & FixQuotes(StringToQuote, DoubleQuote) & "'"

End Function

 

Function FixQuotes(strToFix As Variant, Optional DoubleQuote As Boolean = False) As String

    If DoubleQuote Then
        FixQuotes = Replace(Nz(strToFix), """", "'")
    Else
        FixQuotes = Replace(Nz(strToFix), "'", "''")
    End If

End Function

Recursive Functions and Microsoft Minesweeper

Recursive Functions

There are quite a few tutorials on the web about recursive functions - I've even posted one myself - that use the old standby Factorial of 5 explanation. It's a great example and illustrates the concept very well.

OK, fine, so we can use recursive functions to do factorials, but there are calculators and pre-written functions that handle that, so why do I need to understand recursion?

Minesweeper opening grid

Opening game


Minesweeper after clicking a square

After first move

What might help is another less common real-world example. Let's take a look at Microsoft's® standby game, Minesweeper®. It's a game that most people are at least somewhat familiar with. This quick tutorial will hopefully help you see how recursion works in the code behind this great little game.

Minesweeper starts it's beginner game with a 9x9 grid of squares. When the user clicks on the center square (red "X"), the program must look at all 8 adjacent squares (in yellow) and determine how many mines surround that square. It happens that no matter where you click first, the eight surrounding squares will not contain mines. It's just the way the game is programmed. Live with it. However, many of those squares may have one or more mines surrounding them. Now we begin to get recursive...

Here's how it works:

The eight squares surrounding the red "X" each have squares surrounding them. Each one of them must be examined in turn to see how many mines surround them. In the case of our example, the square directly below the red "X" had zero mines surrounding it. Now the program must check each of the squares surrounding that square.

So... first we must have a function that checks surrounding squares for mines. It could happen that one of the squares we're checking needs to have its surrounding squares checked for mines. The same function that checks adjacent squares for mines needs to include a function call to check adjacent squares for mines - it may need to call itself.

The code is a bit more involved than what we can go into in a short blog, but here is a recap of the concept:

If one started on the square with the black dot, coordinates (5,3) in an (x,y) grid, one would need to check square (4,2) to see if it's a bomb. If it isn't, one would need to see how many bombs surround (4,2), so the program would call itself with new coordinates. This time, the program would check (3,1) to see if it's a bomb. If it isn't, the program would need to see how many bombs surround (3,1), so the program would call itself with new coordinates to check (2,1) to see if it's a bomb. If it IS, it would increment the counter in the stack iteration for (3,1) and move to (4,1) to check to see if it's a bomb. Once all the squares surrounding (3,1) have been checked and all the bombs surrounding it accounted for, we can return a value to the stack iteration for (4,2) and move on to (5,2) which is directly above the black dot and start the whole process over again.

Yes, it's more complex than that, really. A lot of head-scratching went into programming MineSweeper, simple game though it is. It still makes for a good example of how Recursive Functions work and what they can be used for besides finding factorials.

College Funding Services

There are shameless plugs and there are valuable tips. This blog constitutes a little of both because although I do stand to profit from the success of the company I am featuring today, I also firmly believe that the services they offer are extremely valuable, especially if you have a child that is preparing to enter the phenomenally expensive world of secondary education.

Are you getting all the college money you are eligible for?

The odds are strongly against it, regardless of how deeply you’ve researched the subject. The fact is, the best ways to get money for college are not broadly publicized. In fact, they are rarely published in any format at all. It’s not that college funding consultants and high school college counselors are not honest, hard-working professionals. Rather, it’s that they are generally under-informed. (There is, of course, a healthy proportion of them that are less than ethical in their practices and offer things that they can deliver, but that are not worth what you pay for them). Many simply do not know how to help you position yourself for the most financial aid possible.

That is the job of Simple Solutions College Funding – http://simplesolutionscollegefunding.com. Wayne Hamrick has been a financial consultant for 35+ years and understands intimately the best ways to get the most bang for your buck. He has partnered with the College Planning Network, a N.A.C.F.A. approved organization with a pristine BBB history of helping parents and students prepare for college, both financially and academically. The synergy of this union is obvious – the wisdom of a seasoned financial planning professional coupled with a network of experienced and knowledgeable college prep experts results in a dynamic relationship that can save you thousands – and more likely tens of thousands – of dollars over the course of your child’s higher education experience.

You can learn more by visiting his website at http://SimpleSolutionsCollegeFunding.com or by calling Wayne directly at 858-753-1902. It will result in a free consultation that could save you a boatload of cash…

CSS3 Introduction

There is always a need for learning new tricks, and with the Internet and the Web, there is never a dirth of new information.  CSS3 has been around awhile, but since not all browsers support the features yet, it has become a hit-and-miss proposition as to whether the new tricks will work.  This makes it supremely difficult for developers whose project managers or clients want the new features, but also demand backwards compatibility. At some point you just want to throw up your hands in frustration and say, "Get a new browser!!!" 

Unfortunately, although CSS3 adds a significant number of new features, it is still under development by the W3C, which means not all browsers support all the features, and some (most notably Microsoft IE6) support none of them.  To get the new CSS3 features in all their glory, you'll need to upgrade your browser to the newest version of whatever browser you're using, and if you're a Microsuck... er, Microsoft fan, you may just have to do without. 

A quick test for Firefox users:  if the following paragraph has a gradient border, your Firefox browser supports CSS3.  If not, you're due for an upgrade!  As of this writing, Chrome, Safari, Opera, and of course Microsuck... er, Microsoft do not support the border-colors (plural) feature.

This box should have a gently fading gray border on updated Mozilla Firefox browsers

Some of the most significant features of CSS3 are as follows:

  • Selectors
  • Box Model
  • Backgrounds and Borders
  • Text Effects
  • 2D/3D Transformations
  • Animations
  • Multiple Column Layout
  • User Interface

To learn more about CSS3 and how to implement it in your web design projects, visit http://www.w3schools.com/css3/css3_intro.asp

For a really interesting treatise on how to use CSS3 in lieu of JQuery for a cool slide-out panel, see this tutorial:

http://www.1stwebdesigner.com/tutorials/slide-out-panel-css3/?goback=%2Egde_1851900_news_145224950

Page 2 of 3

HostGator Web Hosting
RoboForm: Learn more...