SQL Syntax Problem

  • Thread starter Thread starter DS
  • Start date Start date
D

DS

Having a problem with SQL statement,I think its with the [FName]& "
" &[LName] Section....
Any Help Appreciated
Thanks
DS

With Me.ListToGo
.RowSource = "SELECT PCenter.TableID,[FName]& " " &[LName] AS
Expr, Sales.SalesID, PCenter.TableName, Sales.Cancelled,
Sales.SalesServer, Sales.Paid " & _
"FROM PCenter INNER JOIN (Customers INNER JOIN Sales ON
Customers.CustomerID = Sales.CustomerNum) ON PCenter.TableID =
Sales.TableNumber " & _
"GROUP BY PCenter.TableID, [FName] & " " & [LName], Sales.SalesID,
PCenter.TableName, Sales.Cancelled, Sales.SalesServer, Sales.Paid " & _
"HAVING (((PCenter.TableName) Like 'Delivery') AND
((Sales.Cancelled)=0) AND
((Sales.SalesServer)=[Forms]![PCenter]![Server]) AND ((Sales.Paid)=0));
End With
 
DS said:
Having a problem with SQL statement,I think its with the [FName]& "
" &[LName] Section....
Any Help Appreciated
Thanks
DS

With Me.ListToGo
.RowSource = "SELECT PCenter.TableID,[FName]& " " &[LName] AS
Expr, Sales.SalesID, PCenter.TableName, Sales.Cancelled,
Sales.SalesServer, Sales.Paid " & _
"FROM PCenter INNER JOIN (Customers INNER JOIN Sales ON
Customers.CustomerID = Sales.CustomerNum) ON PCenter.TableID =
Sales.TableNumber " & _
"GROUP BY PCenter.TableID, [FName] & " " & [LName],
Sales.SalesID, PCenter.TableName, Sales.Cancelled, Sales.SalesServer,
Sales.Paid " & _ "HAVING (((PCenter.TableName) Like 'Delivery')
AND ((Sales.Cancelled)=0) AND
((Sales.SalesServer)=[Forms]![PCenter]![Server]) AND
((Sales.Paid)=0)); End With
Click to expand...

Change the occurrences of

[FName] & " " &[LName]

to

[FName] & ' ' &[LName]

(that is, replacing the double-quotes with single-quotes), to keep
Access from getting those quotes confused with the ones that define the
literal string you're building for the SQL statement.
 
Anytime you need to put quotes inside of quotes, you need to double up the
inside ones.

.RowSource = "SELECT PCenter.TableID,[FName]& "" "" &[LName] AS ....
 
Dirk said:
Having a problem with SQL statement,I think its with the [FName]& "
" &[LName] Section....
Any Help Appreciated
Thanks
DS

With Me.ListToGo
.RowSource = "SELECT PCenter.TableID,[FName]& " " &[LName] AS
Expr, Sales.SalesID, PCenter.TableName, Sales.Cancelled,
Sales.SalesServer, Sales.Paid " & _
"FROM PCenter INNER JOIN (Customers INNER JOIN Sales ON
Customers.CustomerID = Sales.CustomerNum) ON PCenter.TableID =
Sales.TableNumber " & _
"GROUP BY PCenter.TableID, [FName] & " " & [LName],
Sales.SalesID, PCenter.TableName, Sales.Cancelled, Sales.SalesServer,
Sales.Paid " & _ "HAVING (((PCenter.TableName) Like 'Delivery')
AND ((Sales.Cancelled)=0) AND
((Sales.SalesServer)=[Forms]![PCenter]![Server]) AND
((Sales.Paid)=0)); End With
Click to expand...


Change the occurrences of

[FName] & " " &[LName]

to

[FName] & ' ' &[LName]

(that is, replacing the double-quotes with single-quotes), to keep
Access from getting those quotes confused with the ones that define the
literal string you're building for the SQL statement.
Click to expand...
That Worked! Thanks
DS
 
Douglas said:
Anytime you need to put quotes inside of quotes, you need to double up the
inside ones.

.RowSource = "SELECT PCenter.TableID,[FName]& "" "" &[LName] AS ....
Click to expand...
That also worked!
Thanks
DS
 
Back
Top