SQL Quotes

[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;

 

[Ken Snell [MVP]]

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

 

[Douglas J. Steele]

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)

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/

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;

 

[Douglas J. Steele]

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!)

"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;

 

[Graham Mandeno]

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

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!)

"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;

 

[Douglas J. Steele]

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!)

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

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!)

"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;

 

[Scott]

thank you for the code example.

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)

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/

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;

 

[Ken Snell [MVP]]

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"

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