An SQL Statement built in VBA is basically a string.
When you build strings in VBA you can place variables within them using the concatenation character, the ampersand "&". The ampersand can also be used for joining strings together, it is not just for adding variables, but it is mostly used for adding variables.
Variables are handy for adding extra bits into a string programmatically, as indeed their name implies they contain - “variable values”.
When you pass something into the string through the variable you have to put delimiters around a variable, except in the case of numbers. “Number” is the default, therefore does not need delimiters.
The text delimiters are quotation marks, " " and if you were to pass in a date then you would use the hash as a delimiter. #
One of the major problems for people when they first start building SQL Statement “strings” is adding these delimiter characters.
In particular the quotation marks. This is because quotation marks are also used to identify strings. For instance if you want to add two strings together you would put them in your code like this :-
“This is string one” & “This is string Two”
The result of this string would be:- This is string one This is string Two
Look carefully at the strings above, the first set and the second set, notice In the result string all of the quotation marks and the ampersand have gone.
The reason they have gone is that in the original two strings the quotation marks are identifiers to identify the different sections as being strings, and the ampersand is the joining character to join the two strings together. In other words they do not constitute part of the string, they are delimiters for the string, and an instruction as to what should be done to the strings.
The difficulty in constructing an SQL Statement arises from the fact that an SQL Statement actually needs quotation marks within it. Quotation marks are a necessary part of the SQL Statement.
However when you construct a string in VBA, the quotation marks are delimiters for text. When you construct an SQL Statement the quotation marks are delimiters for the text variables.
This means the SQL Statement must include a quotation mark each end of the text variable. They are after all it's delimiters in the SQL Statement.
To complicate matters further you can also use apostrophes as text delimiters, so you have both quotation marks and apostrophes!
As you can see from my concatenation example above two quotation marks are removed. However if it's an SQL Statement you don't want the quotation marks removed because they constitute part of the SQL.
One way is to surround surround the quotation marks in more quotation marks and then these extra quotation marks are removed when the string is constructed, leaving the quotation mark you want in place.
As you can imagine this is a complicated and difficult process! As hinted out above, one alternative is to replace the quotation mark with an apostrophe.
But again this is difficult, because if you are building SQL Statements to handle text, then occasionally the text might include and apostrophe. So now you've built your SQL Statement and the text you pass in, let's say you pass in the name “O'Brien” you've suddenly got an extra apostrophe!
Instead of interpreting your SQL Statement as intended, it ends up dividing it up into different pieces of string at unpredictable places. This is the essence of the hackers Trick of SQL injection.
Many of the questions I see on this forum are based around trying to solve this problem.
For my own use I avoid using quotation marks and I use the ASCII character Chr(34) instead.
The following SQL Statement is based on the customer table from the Northwind sample database and filters for a customer with first name “Anna” - The SQL statement was built in the query designer grid. This is a brilliant place to start once you start messing around with SQL statements in VBA. However you need to pick up on the issue of the quotation marks!
Code:
SELECT Customers.ID, Customers.Company, Customers.[Last Name], Customers.[First Name]
FROM Customers
WHERE (((Customers.[First Name])="Anna"));
If you were to try and use that string directly in VBA then you would get problems because of the string delimiters around Anna…
You could try a rearranging it like this
Code:
"SELECT Customers.ID, Customers.Company, Customers.[Last Name], Customers.[First Name] FROM Customers WHERE (((Customers.[First Name])=" & "Anna" & "));"
That looks more promising but now it passes through Anna as a variable in other words if you were to pass this into your form as a record source it would pop up a box asking you to type in something to search for.
The problem is the quotation marks around Anna "Anna"
Code:
"SELECT Customers.ID, Customers.Company, Customers.[Last Name], Customers.[First Name] FROM Customers WHERE (((Customers.[First Name])=" & "'Anna'" & "));"
Now this statement is fixed, notice the single quotation marks adjacent to Anna >>> "'Anna'"
The following SQL statement won't work because the text passed in O'brien contains an apostrophe and that breaks it.
Code:
"SELECT Customers.ID, Customers.Company, Customers.[Last Name], Customers.[First Name] FROM Customers WHERE (((Customers.[First Name])=" & "'O'Brian'" & "));"
Here here we are dealing with very short SQL Statements however some can be extremely long and complicated. I handle these by putting them inside a function and passing the parameters in as parameters of the function. To make Managing SQL Statements a very easy simple task, I keep a ready-made template that I just drop in to my module for building SQL Statements.
This is a generalized version of it which you are welcome to use:-
Code:
Private Function fSQLX(strVar As String, lngVar As Long) As String
Dim strSQL0 As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As String
''Format Text Correctly for the SQL Statement
'strVar1 = Chr(34) & strVar1 & Chr(34) ' "
''Format Date Correctly for the SQL Statement
'strVar2 = Chr(35) & strVar2 & Chr(35) ' #
strSQL1 = ""
strSQL2 = ""
strSQL3 = ""
strSQL4 = ""
strSQL0 = strSQL1 & strSQL2 & strSQL3 & strSQL4
fSQLX = strSQL0
End Function 'fSQLX
This is the above template filled out ready for use. It returns the “Anna” variable..
This is the template filled out with an SQL statement created in the query Builder Grid.
Call it Like This:-
Code:
Private Sub Command93_Click()
Dim strSearch As String
strSearch = "Anna"
Me.RecordSource = fSearchSQL(strSearch)
End Sub
Code:
Private Function fSearchSQL(strVar As String) As String
Dim strSQL0 As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
''Format Text Correctly for the SQL Statement
strVar = Chr(34) & strVar & Chr(34) ' "
'This SQL Statement created simply and easily in the Query Builder Grid
'SELECT Customers.ID, Customers.Company, Customers.[Last Name], Customers.[First Name]
'FROM Customers WHERE (((Customers.[First Name])=
'Anna
'))
strSQL1 = "SELECT Customers.ID, Customers.Company, Customers.[Last Name], Customers.[First Name] "
strSQL2 = "FROM Customers WHERE (((Customers.[First Name])="
'Anna
strSQL3 = "))"
strSQL0 = strSQL1 & strSQL2 & strVar & strSQL3
fSearchSQL = strSQL0
End Function 'fSearchSQL
Below I explain how you can use this feature yourself which is one of the best features of Microsoft Access coding, and one which I don't think many people know about I also provide a video explaining its use.
Insert File
MS Access provides a folder which is accessed quickly and easily through the — >>>Insert >>>File — menu. This menu is shown when you are in the VBA interface. When you open it, you have access to this folder. You can have subfolders in any/all of those folders. You can have text files containing your code, Code Modules, Code Snippets, Class Modules Anything Textual you Want! Clicking on any of the text files automatically copies the contents of that text file straight into your Code/Class Module. Follow this link to my website:-
Where I demonstrate in a video the fantastic improvement to your coding technique this tip will give you.
I have also created a set of videos explaining how to create a "Search Form" which covers quite a bit of what I've explained above in video format. It's on my website here:-
