SQL Woes

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

DS

I have this SQL statement that works as a Query, but when I make it an
SQL statement my listbox keeps drawing a blank. Could it be a Syntax
Error? Any help appreciated.
Thanks
DS

With Forms!CheckPreview!ListOrders
..RowSource = "SELECT SalesDetails.SalesID, SalesDetails.LineID, " & _
"IIf(SalesDetailsMoney.QtyPrice=1,"",SalesDetailsMoney.QtyPrice) AS QTY,
" & _
"SalesDetailsMoney.ItemID, IIf([Sub]=1,Items.ItemName,IIf([Sub]=2,""
"" & Items.ItemName,IIf([Sub]=3,"" "" & Items.ItemName))) AS
EName, " & _
"Format([SalesDetailsMoney].[BasePrice]*[SalesDetailsMoney].[QtyPrice],""$0.00"")
AS Price, " & _
"Items.ItemType, Items.Tax " & _
"FROM (SalesDetails LEFT JOIN SalesDetailsMoney ON (SalesDetails.LineID
= SalesDetailsMoney.LineID) " & _
"AND (SalesDetails.SalesID = SalesDetailsMoney.SalesID)) " & _
"INNER JOIN Items ON SalesDetails.ItemID = Items.ItemID " & _
"GROUP BY SalesDetails.SalesID, SalesDetails.LineID, " & _
"IIf(SalesDetailsMoney.QtyPrice=1,"",SalesDetailsMoney.QtyPrice), " & _
"SalesDetailsMoney.ItemID, IIf([Sub]=1,Items.ItemName,IIf([Sub]=2,""
"" & Items.ItemName,IIf([Sub]=3,"" "" & Items.ItemName))), " & _
"Format([SalesDetailsMoney].[BasePrice]*SalesDetailsMoney.QtyPrice,""$0.00""),
" & _
"Items.ItemType, Items.Tax " & _
"HAVING (((SalesDetails.SalesID) = [Forms]![CheckAction]![TxtSalesID]))
" & _
"ORDER BY SalesDetails.LineID, Items.ItemType;"
..Requery
..ColumnCount = 8
..ColumnWidths = "0 in;0 in;0.2 in;0 in;2.5 in;0.5 in;0 in;0 in"
End With
 
Hi Booty,

Just a small point, might be a decided though, in code you must refer
to " as ' if they are part of the sql statement.

You only close the quotes at the begining and end of the statement or
for external references.

This is not the case when you execute queries.

Good luck,

Nick
 
Nick said:
Hi Booty,

Just a small point, might be a decided though, in code you must refer
to " as ' if they are part of the sql statement.

You only close the quotes at the begining and end of the statement or
for external references.

This is not the case when you execute queries.

Good luck,

Nick
I have this SQL statement that works as a Query, but when I make it an
SQL statement my listbox keeps drawing a blank. Could it be a Syntax
Error? Any help appreciated.
Thanks
DS

With Forms!CheckPreview!ListOrders
.RowSource = "SELECT SalesDetails.SalesID, SalesDetails.LineID, " & _
"IIf(SalesDetailsMoney.QtyPrice=1,"",SalesDetailsMoney.QtyPrice) AS QTY,
" & _
"SalesDetailsMoney.ItemID, IIf([Sub]=1,Items.ItemName,IIf([Sub]=2,""
"" & Items.ItemName,IIf([Sub]=3,"" "" & Items.ItemName))) AS
EName, " & _
"Format([SalesDetailsMoney].[BasePrice]*[SalesDetailsMoney].[QtyPrice],""$0.00"")
AS Price, " & _
"Items.ItemType, Items.Tax " & _
"FROM (SalesDetails LEFT JOIN SalesDetailsMoney ON (SalesDetails.LineID
= SalesDetailsMoney.LineID) " & _
"AND (SalesDetails.SalesID = SalesDetailsMoney.SalesID)) " & _
"INNER JOIN Items ON SalesDetails.ItemID = Items.ItemID " & _
"GROUP BY SalesDetails.SalesID, SalesDetails.LineID, " & _
"IIf(SalesDetailsMoney.QtyPrice=1,"",SalesDetailsMoney.QtyPrice), " & _
"SalesDetailsMoney.ItemID, IIf([Sub]=1,Items.ItemName,IIf([Sub]=2,""
"" & Items.ItemName,IIf([Sub]=3,"" "" & Items.ItemName))), " & _
"Format([SalesDetailsMoney].[BasePrice]*SalesDetailsMoney.QtyPrice,""$0.00""),
" & _
"Items.ItemType, Items.Tax " & _
"HAVING (((SalesDetails.SalesID) = [Forms]![CheckAction]![TxtSalesID]))
" & _
"ORDER BY SalesDetails.LineID, Items.ItemType;"
.Requery
.ColumnCount = 8
.ColumnWidths = "0 in;0 in;0.2 in;0 in;2.5 in;0.5 in;0 in;0 in"
End With
Click to expand...
Click to expand...
Thanks Nick. How would I do this?
DS
 
Well, you need to through the sql statement from begining to end either
replacing a " with a ' or leaving as it is which ever is appropriate!

Good luck,

Nick
Nick said:
Hi Booty,

Just a small point, might be a decided though, in code you must refer
to " as ' if they are part of the sql statement.

You only close the quotes at the begining and end of the statement or
for external references.

This is not the case when you execute queries.

Good luck,

Nick
I have this SQL statement that works as a Query, but when I make it an
SQL statement my listbox keeps drawing a blank. Could it be a Syntax
Error? Any help appreciated.
Thanks
DS

With Forms!CheckPreview!ListOrders
.RowSource = "SELECT SalesDetails.SalesID, SalesDetails.LineID, " & _
"IIf(SalesDetailsMoney.QtyPrice=1,"",SalesDetailsMoney.QtyPrice) AS QTY,
" & _
"SalesDetailsMoney.ItemID, IIf([Sub]=1,Items.ItemName,IIf([Sub]=2,""
"" & Items.ItemName,IIf([Sub]=3,"" "" & Items.ItemName))) AS
EName, " & _
"Format([SalesDetailsMoney].[BasePrice]*[SalesDetailsMoney].[QtyPrice],""$0.00"")
AS Price, " & _
"Items.ItemType, Items.Tax " & _
"FROM (SalesDetails LEFT JOIN SalesDetailsMoney ON (SalesDetails.LineID
= SalesDetailsMoney.LineID) " & _
"AND (SalesDetails.SalesID = SalesDetailsMoney.SalesID)) " & _
"INNER JOIN Items ON SalesDetails.ItemID = Items.ItemID " & _
"GROUP BY SalesDetails.SalesID, SalesDetails.LineID, " & _
"IIf(SalesDetailsMoney.QtyPrice=1,"",SalesDetailsMoney.QtyPrice), " & _
"SalesDetailsMoney.ItemID, IIf([Sub]=1,Items.ItemName,IIf([Sub]=2,""
"" & Items.ItemName,IIf([Sub]=3,"" "" & Items.ItemName))), " & _
"Format([SalesDetailsMoney].[BasePrice]*SalesDetailsMoney.QtyPrice,""$0.00""),
" & _
"Items.ItemType, Items.Tax " & _
"HAVING (((SalesDetails.SalesID) = [Forms]![CheckAction]![TxtSalesID]))
" & _
"ORDER BY SalesDetails.LineID, Items.ItemType;"
.Requery
.ColumnCount = 8
.ColumnWidths = "0 in;0 in;0.2 in;0 in;2.5 in;0.5 in;0 in;0 in"
End With
Click to expand...
Click to expand...
Thanks Nick. How would I do this?
DS
Click to expand...
 
Nick said:
Well, you need to through the sql statement from begining to end either
replacing a " with a ' or leaving as it is which ever is appropriate!

Good luck,

Nick
Nick said:
Hi Booty,

Just a small point, might be a decided though, in code you must refer
to " as ' if they are part of the sql statement.

You only close the quotes at the begining and end of the statement or
for external references.

This is not the case when you execute queries.

Good luck,

Nick

DS wrote:


I have this SQL statement that works as a Query, but when I make it an
SQL statement my listbox keeps drawing a blank. Could it be a Syntax
Error? Any help appreciated.
Thanks
DS

With Forms!CheckPreview!ListOrders
.RowSource = "SELECT SalesDetails.SalesID, SalesDetails.LineID, " & _
"IIf(SalesDetailsMoney.QtyPrice=1,"",SalesDetailsMoney.QtyPrice) AS QTY,
" & _
"SalesDetailsMoney.ItemID, IIf([Sub]=1,Items.ItemName,IIf([Sub]=2,""
"" & Items.ItemName,IIf([Sub]=3,"" "" & Items.ItemName))) AS
EName, " & _
"Format([SalesDetailsMoney].[BasePrice]*[SalesDetailsMoney].[QtyPrice],""$0.00"")
AS Price, " & _
"Items.ItemType, Items.Tax " & _
"FROM (SalesDetails LEFT JOIN SalesDetailsMoney ON (SalesDetails.LineID
= SalesDetailsMoney.LineID) " & _
"AND (SalesDetails.SalesID = SalesDetailsMoney.SalesID)) " & _
"INNER JOIN Items ON SalesDetails.ItemID = Items.ItemID " & _
"GROUP BY SalesDetails.SalesID, SalesDetails.LineID, " & _
"IIf(SalesDetailsMoney.QtyPrice=1,"",SalesDetailsMoney.QtyPrice), " & _
"SalesDetailsMoney.ItemID, IIf([Sub]=1,Items.ItemName,IIf([Sub]=2,""
"" & Items.ItemName,IIf([Sub]=3,"" "" & Items.ItemName))), " & _
"Format([SalesDetailsMoney].[BasePrice]*SalesDetailsMoney.QtyPrice,""$0.00""),
" & _
"Items.ItemType, Items.Tax " & _
"HAVING (((SalesDetails.SalesID) = [Forms]![CheckAction]![TxtSalesID]))
" & _
"ORDER BY SalesDetails.LineID, Items.ItemType;"
.Requery
.ColumnCount = 8
.ColumnWidths = "0 in;0 in;0.2 in;0 in;2.5 in;0.5 in;0 in;0 in"
End With
Click to expand...
Thanks Nick. How would I do this?
DS
Click to expand...
Click to expand...
Got ya!
Thnaks
DS
 
Back
Top