SQL Quotes

[paulS30berks]

I wondered if someone could explain something for me.

In SQL the difference between ' Single Quotes and " Double Quotes and when and when not to use either.

Thanks

Paul

 

[Mile-O]

It's my preference to use double quotes at all times.

Let's take the following SQL statement.

SELECT UserID, Forename, Surname
FROM Table1
WHERE Surname = 'Reilly';

This, of course, gives all users whose surname is Reilly. The single quotes ' are fine as delimiters. However, if the person's name was O'Reilly then the SQL statement would look like this:

SELECT UserID, Forename, Surname
FROM Table1
WHERE Surname = 'O'Reilly';

The problem here is that the single quote is acting as a delimiter and thus the criteria would be seen as 'O' with an erroneous Reilly hanging after it, causing an error.

The solution, then, is to use double quotes. This works for examples of Reilly and O'Reilly and is, in my opinion, the safer option.

SELECT UserID, Forename, Surname
FROM Table1
WHERE Surname = "O'Reilly";

Or, is your problem more akin to building SQL in VBA using strings?

 

[paulS30berks]

Many thanks for your reply

Yes more towards building SQL in VBA using strings. In particular I have written some VBA (shown below) within a Form:

I know there is Data within my report by itself, but when I run through the Form there is no data just a blank report. I wondered if the quotes I was using could affect my output.

Thanks

VBA CODE

Private Sub Command12_Click()
On Error GoTo Err_cmdRunPLSLIPS_Click

Dim stDocName As String
Dim strSQL As String
Dim qdf As QueryDef

Set qdf = CurrentDb.QueryDefs("Missing Client Codes")
strSQL = "EXECUTE CODAClientCodes @organisationCode=""" + cmpcode.Value + """"

MsgBox "SQL code : " & strSQL


qdf.SQL = strSQL
qdf.Close

stDocName = "Missing Client Codes"
DoCmd.OpenReport stDocName, acPreview
DoCmd.Maximize

Exit_cmdRunPLSLIPS_Click:
Exit Sub

Err_cmdRunPLSLIPS_Click:
MsgBox Err.Description
Resume Exit_cmdRunPLSLIPS_Click
End Sub

 

[Mile-O]

strSQL = "EXECUTE CODAClientCodes @organisationCode=""" + cmpcode.Value + """"

First thing, replace any + with &. & is the correct concatenation character.

When you want to use a " in a string, you need to use "". Rather than ' in a string I also use "".

I don't know how to explain it, really, as it's just something I can do without thinking about it. I suppose it can be like brackets (i.e. open and close) but the delimiter is identical on each side.

strSQL = strSQL & "WHERE Surname = 'Reilly';"

Okay, here's a string. We don't want to hard-code the name Reilly into it. Instead we want to use a textbox called txtSurname.

First thing, I suppose is to include the textbox.

strSQL = strSQL & "WHERE Surname = '" & Me.txtSurname & "';"

You'll notice that I have not changed the single quote. This is a string where the double quotes mark its opening and closing. It would work fine if text didn't have single quotes within i.e. surnames like O'Reilly.

So, we substitute the single quotes with double quotes. But, because these are to appear within a string, we need to remember to double them up.

strSQL = strSQL & "WHERE Surname = """ & Me.txtSurname & """;"

Sorry, I can't do much better than that.

 

[Mile-O]

I know there is Data within my report by itself, but when I run through the Form there is no data just a blank report. I wondered if the quotes I was using could affect my output.

Based on that code, however, it would appear you may be trying to assign an action query to a report, which is not possible. You should be using the SELECT keyword.