SQL Quotes

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

I'm trying to turn the below access query into a string variable so I can
set a combo box rowsource in code. Can someone help me re-write the sql
statement as a string? I'm having trouble with the quotes.


CODE:
*******************

SELECT ad.detailID, a.artID, [artItem] & " - " & [artName] & " ($" &
[detailPrice] & ")" AS Name
FROM t_status AS s INNER JOIN ((t_artDetails AS ad INNER JOIN t_art AS a ON
ad.artID = a.artID) INNER JOIN t_artist AS [at] ON a.artistID = at.artistID)
ON s.statusID = ad.statusID
ORDER BY [artItem] & " - " & [artName] & " ($" & [detailPrice] & ")",
ad.detailDate;
 
Replace all " characters with four " characters (""""), surround entire
string with " character pair, and set equal to a variable.
 
That doesn't sound right, Ken. This should be all that's required:

strSQL = "SELECT ad.detailID, a.artID, [artItem] & "" - """ & _
"[artName] & "" ($"" & [detailPrice] & "")"" AS Name " & _
"FROM t_status AS s INNER JOIN ((t_artDetails AS ad " & _
"INNER JOIN t_art AS a ON ad.artID = a.artID) INNER JOIN " & _
"t_artist AS [at] ON a.artistID = at.artistID) " & _
"ON s.statusID = ad.statusID " & _
"ORDER BY [artItem] & "" - "" & [artName] & "" ($""" & _
"[detailPrice] & "")"", ad.detailDate"


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ken Snell said:
Replace all " characters with four " characters (""""), surround entire
string with " character pair, and set equal to a variable.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Scott said:
I'm trying to turn the below access query into a string variable so I can
set a combo box rowsource in code. Can someone help me re-write the sql
statement as a string? I'm having trouble with the quotes.


CODE:
*******************

SELECT ad.detailID, a.artID, [artItem] & " - " & [artName] & " ($" &
[detailPrice] & ")" AS Name
FROM t_status AS s INNER JOIN ((t_artDetails AS ad INNER JOIN t_art AS a
ON ad.artID = a.artID) INNER JOIN t_artist AS [at] ON a.artistID =
at.artistID) ON s.statusID = ad.statusID
ORDER BY [artItem] & " - " & [artName] & " ($" & [detailPrice] & ")",
ad.detailDate;
Click to expand...
Click to expand...
 
While you're correct that Name is a reserved word, so that square brackets
should be used, I don't believe you can refer to the alias like that in the
ORDER BY clause. I believe you need to repeat the calculation, as Scott was
doing:

"ORDER BY [artItem] & "" - "" & [artName] & "" ($"" & [detailPrice] & "")"",
ad.detailDate"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


AccessVandal via AccessMonster.com said:
"Name" is a reserved word in Access, if it's possible to rename it, do so
because it will give you lots of trouble in the future.

As the double qoutes are cover by others, you need to use the ORDER BY
correctly.

ORDER BY [Name]

Use the square brackets to enclose the reserved name.
I'm trying to turn the below access query into a string variable so I can
set a combo box rowsource in code. Can someone help me re-write the sql
statement as a string? I'm having trouble with the quotes.

CODE:
*******************

SELECT ad.detailID, a.artID, [artItem] & " - " & [artName] & " ($" &
[detailPrice] & ")" AS Name
FROM t_status AS s INNER JOIN ((t_artDetails AS ad INNER JOIN t_art AS a
ON
ad.artID = a.artID) INNER JOIN t_artist AS [at] ON a.artistID =
at.artistID)
ON s.statusID = ad.statusID
ORDER BY [artItem] & " - " & [artName] & " ($" & [detailPrice] & ")",
ad.detailDate;
Click to expand...
Click to expand...
 
Something the Access query builder does is make it very eacy to use an
unnecessary expression in the ORDER BY clause. If you click "Ascending" in
a calculated field then that entire expression is reproduced in the ORDER
BY.

I believe that this query:

Select CustID, FirstName & " " LastName as CustName from Customers
order by FirstName & " " LastName;

is less efficient than:

Select CustID, FirstName & " " LastName as CustName from Customers
order by FirstName, LastName;

In the OP's case, I would suggest:

"ORDER BY [artItem], [artName]"

would probably be sufficient.

--
Graham Mandeno [Access MVP]
Auckland, New Zealand

Douglas J. Steele said:
While you're correct that Name is a reserved word, so that square brackets
should be used, I don't believe you can refer to the alias like that in
the ORDER BY clause. I believe you need to repeat the calculation, as
Scott was doing:

"ORDER BY [artItem] & "" - "" & [artName] & "" ($"" & [detailPrice] &
"")"", ad.detailDate"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


AccessVandal via AccessMonster.com said:
"Name" is a reserved word in Access, if it's possible to rename it, do so
because it will give you lots of trouble in the future.

As the double qoutes are cover by others, you need to use the ORDER BY
correctly.

ORDER BY [Name]

Use the square brackets to enclose the reserved name.
I'm trying to turn the below access query into a string variable so I can
set a combo box rowsource in code. Can someone help me re-write the sql
statement as a string? I'm having trouble with the quotes.

CODE:
*******************

SELECT ad.detailID, a.artID, [artItem] & " - " & [artName] & " ($" &
[detailPrice] & ")" AS Name
FROM t_status AS s INNER JOIN ((t_artDetails AS ad INNER JOIN t_art AS a
ON
ad.artID = a.artID) INNER JOIN t_artist AS [at] ON a.artistID =
at.artistID)
ON s.statusID = ad.statusID
ORDER BY [artItem] & " - " & [artName] & " ($" & [detailPrice] & ")",
ad.detailDate;
Click to expand...
Click to expand...
Click to expand...
 
Yeah, I think you're right.

Of course, you could also use ORDER BY 2, 1

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Graham Mandeno said:
Something the Access query builder does is make it very eacy to use an
unnecessary expression in the ORDER BY clause. If you click "Ascending"
in a calculated field then that entire expression is reproduced in the
ORDER BY.

I believe that this query:

Select CustID, FirstName & " " LastName as CustName from Customers
order by FirstName & " " LastName;

is less efficient than:

Select CustID, FirstName & " " LastName as CustName from Customers
order by FirstName, LastName;

In the OP's case, I would suggest:

"ORDER BY [artItem], [artName]"

would probably be sufficient.

--
Graham Mandeno [Access MVP]
Auckland, New Zealand

Douglas J. Steele said:
While you're correct that Name is a reserved word, so that square
brackets should be used, I don't believe you can refer to the alias like
that in the ORDER BY clause. I believe you need to repeat the
calculation, as Scott was doing:

"ORDER BY [artItem] & "" - "" & [artName] & "" ($"" & [detailPrice] &
"")"", ad.detailDate"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


AccessVandal via AccessMonster.com said:
"Name" is a reserved word in Access, if it's possible to rename it, do
so
because it will give you lots of trouble in the future.

As the double qoutes are cover by others, you need to use the ORDER BY
correctly.

ORDER BY [Name]

Use the square brackets to enclose the reserved name.

Scott wrote:
I'm trying to turn the below access query into a string variable so I
can
set a combo box rowsource in code. Can someone help me re-write the sql
statement as a string? I'm having trouble with the quotes.

CODE:
*******************

SELECT ad.detailID, a.artID, [artItem] & " - " & [artName] & " ($" &
[detailPrice] & ")" AS Name
FROM t_status AS s INNER JOIN ((t_artDetails AS ad INNER JOIN t_art AS a
ON
ad.artID = a.artID) INNER JOIN t_artist AS [at] ON a.artistID =
at.artistID)
ON s.statusID = ad.statusID
ORDER BY [artItem] & " - " & [artName] & " ($" & [detailPrice] & ")",
ad.detailDate;
Click to expand...
Click to expand...
Click to expand...
 
thank you for the code example.

Douglas J. Steele said:
That doesn't sound right, Ken. This should be all that's required:

strSQL = "SELECT ad.detailID, a.artID, [artItem] & "" - """ & _
"[artName] & "" ($"" & [detailPrice] & "")"" AS Name " & _
"FROM t_status AS s INNER JOIN ((t_artDetails AS ad " & _
"INNER JOIN t_art AS a ON ad.artID = a.artID) INNER JOIN " & _
"t_artist AS [at] ON a.artistID = at.artistID) " & _
"ON s.statusID = ad.statusID " & _
"ORDER BY [artItem] & "" - "" & [artName] & "" ($""" & _
"[detailPrice] & "")"", ad.detailDate"


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ken Snell said:
Replace all " characters with four " characters (""""), surround entire
string with " character pair, and set equal to a variable.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Scott said:
I'm trying to turn the below access query into a string variable so I
can set a combo box rowsource in code. Can someone help me re-write the
sql statement as a string? I'm having trouble with the quotes.


CODE:
*******************

SELECT ad.detailID, a.artID, [artItem] & " - " & [artName] & " ($" &
[detailPrice] & ")" AS Name
FROM t_status AS s INNER JOIN ((t_artDetails AS ad INNER JOIN t_art AS a
ON ad.artID = a.artID) INNER JOIN t_artist AS [at] ON a.artistID =
at.artistID) ON s.statusID = ad.statusID
ORDER BY [artItem] & " - " & [artName] & " ($" & [detailPrice] & ")",
ad.detailDate;
Click to expand...
Click to expand...
Click to expand...
 
Douglas J. Steele said:
That doesn't sound right, Ken. This should be all that's required:

strSQL = "SELECT ad.detailID, a.artID, [artItem] & "" - """ & _
"[artName] & "" ($"" & [detailPrice] & "")"" AS Name " & _
"FROM t_status AS s INNER JOIN ((t_artDetails AS ad " & _
"INNER JOIN t_art AS a ON ad.artID = a.artID) INNER JOIN " & _
"t_artist AS [at] ON a.artistID = at.artistID) " & _
"ON s.statusID = ad.statusID " & _
"ORDER BY [artItem] & "" - "" & [artName] & "" ($""" & _
"[detailPrice] & "")"", ad.detailDate"
Click to expand...

Yep. Overcomplicating my answer..... < s >
 
Back
Top