Strings in SQL Statements in VB (1 Viewer)

[saross]

Can someone explain to me the rules I need to follow when using strings in an SQL statement in VB. I've written the following code which works perfectly when it uses an int or long but not for a string... why is that?

Private Sub Command3_Click()
Dim strSector As String
Dim strSQL As String

If IsNull(Me.Combo9) Then
MsgBox "Please select a sector", vbOKOnly
Else
strSector = Forms!FrmSelectSector!Combo9.Value
strSQL = "Sector = " & strSector & ""

DoCmd.OpenForm "frmOrgsBySector", , , strSQL
DoCmd.Close acForm, "FrmSelectSector"
DoCmd.Close acForm, "FrmMain"
DoCmd.Maximize
End If

End Sub

 

[WayneRyan]

saross,

SQL at times wants delimiters around variables.

Numbers - Nothing
Strings - single-quotes
dates - #

' Number
Select SomeField 
From   MyTable
wHERE  OtherField = " & Me.SomeControl
' String
Select SomeField 
From   MyTable
wHERE  OtherField = '" & Me.SomeControl & "'"
' Date
Select SomeField 
From   MyTable
wHERE  OtherField = #" & Me.SomeControl & "#"

Wayne

 

[Mile-O]

A numerical value is simply that, a numerical value.

A text value requires that it is enclosed within inverted commas (whether it be ' or " )

When building SQL with strings you must also bear in mind that the values of your string may include apostrophes (if you are enclosing within apostrophes i.e. 'O'Donnell'

A single apostrophe can be represented by two quotation marks i.e ""

My preference is to use the two quotation marks create the criteria...

strSQL = "Sector = """ & strSector & """"

Dates, similarly have their own symbol that encloses them. For dates, you'd use the hash (#) symbol.

i.e.

strSQL = "MyDate = #" & dteExample & "#"

 

[saross]

Thanks Wayne

I wish I could understand why that is, I'll have to print off your guide and keep it!

Sophie

 

[fuzzygeek]

Aid

Go into the VB editor in your module and in the help, look up string. Under string, look up Quotation Marks in Strings. Print out this also. It is MS's explanation of the use of " and '.

 

[AncientOne]

Re: Thanks Wayne

I wish I could understand why that is,

For very much the same reason as you say "I eat my lunch" instead of "Lunch eat I my" No-one would understand you! Computing languages have their fixed rules just like our language. The difference is, you are so familiar with your own language that you automatically put everything in its right order with correct syntax

Understand you now?me

 

[saross]

Thank you Oh Ancient One.
Understand Well Do I

 

[Pat Hartman]

The key to understanding when delimiters are needed is knowing when you are using a literal value. "Smith" is a literal value and LastName is a column name or variable.

What is happining in this statement -
strSQL = "Sector = " & strSector
is that you are asking VBA to extract the content of a variable and embed it within a string. So essentially you end up with a string within a string. If you were to type the literal string yourself, you would type -
strSQL = "Sector = 'Private'"
Notice how the embedded literal is delimited. Therefore, when you use VBA to build the string in pieces, you need to also supply the delimiters -
strSQL = "Sector = """ & strSector & """"

Now, I personally find the above statement confusing and I never get the number of quotes right the first time. Therefore, I add a constant to all of my databases. I create a separate code module that holds only public variables or constants so that they are all in one place.

Public Const QUOTE As String = """"

That would make the statement -
strSQL = "Sector = " & QUOTE & strSector & QUOTE

 

[SilentBreaker]

For code readability, you could try this one out:

strSQL = "Sector = Chr(34) & strSector & Chr(34)

 

[SilentBreaker]

Please ignore my previous reply:

This is the correct syntax:

strSQL = "Sector =" & Chr(34) & strSector & Chr(34)

 

[saross]

Ok - what about...

... when you need to use a wildcard?

This isn't working:

strLetter = "A"
strSQL = "SELECT TblGuest.FName, TblGuest.LName FROM TblGuest WHERE TblGuest.LName = '"
strSQL = strSQL & strLetter & "*'"
strSQL = strSQL & "ORDER BY TblGuest.LName;"

 

[namliam]

Try:

strLetter = "A"
strSQL = "SELECT TblGuest.FName, TblGuest.LName FROM TblGuest WHERE TblGuest.LName like '"
strSQL = strSQL & strLetter & "*'"
strSQL = strSQL & "ORDER BY TblGuest.LName;"

 

[Mile-O]

Be careful with spaces when building SQL over numerous lines.

Your SQL reads thusly:

SELECT TblGuest.FName, TblGuest.LName FROM TblGuest WHERE TblGuest.LName = 'A*'ORDER BY TblGuest.LName;


Note the need to put a space in between your criteria and your ORDER clause. That should be all.

 

[namliam]

Nope sorry,

SELECT TblGuest.FName, TblGuest.LName FROM TblGuest WHERE TblGuest.LName = 'A*'ORDER BY TblGuest.LName;

Allthough it might not be fully correct and nicely readable IT WORKS, why ? Because its a string terminator...

e.g. will not work:
FromTblGuest
BYTblGuest
etc.

Increasing the readabilty would also be:

strSQL = "SELECT TblGuest.FName, TblGuest.LName " & _
         "FROM TblGuest " & _
         "WHERE TblGuest.LName like '" & strLetter & "*' " & _
         "ORDER BY TblGuest.LName;"

Regards

 

[saross]

Thanks Guys

I applied both of your advice - changed = to Like (should have spotted that!) and put a space before ORDER BY (should have spotted that too!) only to find I hadn't declared the database properly!
OOPS
Ah well, I'm learning I guess...

 

[SilentBreaker]

Try this one out

strLetter = "A"
strSQL = "SELECT FName, LName FROM TblGuest WHERE LName = " _
& Chr(34) & strLetter & Chr(42) & Chr(34) _
& " ORDER BY LName;"

Cheers

 

[saross]

That didn't work. The others did though.

 

[SilentBreaker]

Perhaps you forget to insert the space character before the underscore

I have used them heaps in my big projects for code readability insteads of this crappy code strSQL = strSQL & "....etc...