SQL Woes

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
 
N

Nick 'The database Guy'

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
 
D

DS

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
 
N

Nick 'The database Guy'

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...
 
D

DS

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL Rowsource 2
SQL Syntax Error 3
Setting Textbox with SQL 2
Sum of SQL Statement 1
DSum Problem 4
Not Aligning 2

Top