Originally posted by Stockton.S

However I belive that the ASCII code for a single quote is 39 and if you are going to use ascii codes then you may as well represent ; with ascii code 59 making the line of code as follows

strSQL = "SELECT uzdload.dload_item, uzdload.dload_descext" & " FROM uzdload" & " WHERE (uzdload.dload_item = " & Chr(39) & DataCombo.Text & Chr(39) & Chr(59)

What is the need for the additional brackets?

Simon
Yes, the ASCII Code for a single quote is 39; however, the ASCII code for a double quote is 34. If any of your parameter text strings contain a single quote then your SQL statement will produce an error that will crash your program if you do not trap for it. Either quote is acceptable for SQL in ACCESS, the double quote taking precidence over the single.
The "Extra" Brackets are a programming convention I use because I often write code that builds SQL statements with multiple conditional WHERE and ORDER BY Statements. It is easier for me to write if I begin with the largest number of opening parentheses that might occur....Example:

Print txtSQL ' Would Print
.....WHERE (((Plu.MFG = 19) OR (Plu.Mfg=198)) AND (([Plu].[DESC] Like "*Light*") OR ([Plu].[DESC] Like "*Ultra*")) AND (Plu.Cat=7)).....

Produces a very different recordset than:
.....WHERE Plu.MFG = 19 OR Plu.MFG=198 AND ([Plu].[DESC] Like "*Light*" OR [Plu].[DESC] Like "*Ultra*" AND Plu.Cat=7 ....

The actual code that produces the above statement can handle a variable number of WHERE conditions for each of Five Fields And the sort order is also Conditional. In developing and debugging this rather complex SQL generator I found that containing each potential group of conditions within () made my life a great deal easier. Each segment of the WHERE clause is built in its own subroutine that can be used in other parts of the App. and then assembled with the other components of the SQL statement in a local routine that tests the various validity of each of the potential parameters. There is no downside to building in extra () as long as there is a closing ) for each opening (. If you are testing only for one or two fixed number of parameters then there are, as you pointed out, simpler solutions; the particular case above being one of them. I am sorry if my conventions seem odd or wasteful.


Hunter