SQL Rowsource

D

DS

This works when I use it hrough a Query, but when I use it as SQL it
doesn't work.
Thanks
DS

With Forms!CheckPreview2!ListPreview
..RowSource = "SELECT SalesDetails.SalesID, SalesDetails.LineID, " & _
"SalesDetails.ItemID,
IIf(SalesDetails.Quantity=1,"""",SalesDetails.Quantity) AS EQuantity, " & _
"IIf([Sub]=1,Items.ItemName,IIf([Sub]=2,"" "" &
Items.ItemName,IIf([Sub]=3,"" "" & Items.ItemName))) AS EName, " & _
"Items.ItemType, SalesDetails.Sub, SalesDetails.Sent,
Format([BasePrice]*[Quantity],""$0.00"") AS Price " & _
"FROM Items INNER JOIN SalesDetails ON Items.ItemID =
SalesDetails.ItemID " & _
"GROUP BY SalesDetails.SalesID, SalesDetails.LineID,
SalesDetails.ItemID, " & _
"IIf(SalesDetails.Quantity=1,"""",SalesDetails.Quantity),
IIf([Sub]=1,Items.ItemName,IIf([Sub]=2,"" "" &
Items.ItemName,IIf([Sub]=3,"" "" & Items.ItemName))), " & _
"Items.ItemType, SalesDetails.Sub, SalesDetails.Sent,
Format([BasePrice]*[Quantity],""$0.00"") " & _
"HAVING(((SalesDetails.SalesID) =[Forms]![OrderScreen]![SalesID])) " & _
"ORDER BY SalesDetails.LineID, Items.ItemType;"
..Requery
..ColumnCount = 9
..ColumnWidths = ".2 in;.2 in;.2 in;.2 in;.2 in;.2 in;.2 in;.2 in;.2 in"
End With
 
D

Dirk Goldgar

DS said:
This works when I use it hrough a Query, but when I use it as SQL it
doesn't work.
Thanks
DS

With Forms!CheckPreview2!ListPreview
.RowSource = "SELECT SalesDetails.SalesID, SalesDetails.LineID, " & _
"SalesDetails.ItemID,
IIf(SalesDetails.Quantity=1,"""",SalesDetails.Quantity) AS EQuantity,
" & _ "IIf([Sub]=1,Items.ItemName,IIf([Sub]=2,"" "" &
Items.ItemName,IIf([Sub]=3,"" "" & Items.ItemName))) AS EName, "
& _ "Items.ItemType, SalesDetails.Sub, SalesDetails.Sent,
Format([BasePrice]*[Quantity],""$0.00"") AS Price " & _
"FROM Items INNER JOIN SalesDetails ON Items.ItemID =
SalesDetails.ItemID " & _
"GROUP BY SalesDetails.SalesID, SalesDetails.LineID,
SalesDetails.ItemID, " & _
"IIf(SalesDetails.Quantity=1,"""",SalesDetails.Quantity),
IIf([Sub]=1,Items.ItemName,IIf([Sub]=2,"" "" &
Items.ItemName,IIf([Sub]=3,"" "" & Items.ItemName))), " & _
"Items.ItemType, SalesDetails.Sub, SalesDetails.Sent,
Format([BasePrice]*[Quantity],""$0.00"") " & _
"HAVING(((SalesDetails.SalesID) =[Forms]![OrderScreen]![SalesID])) "
& _ "ORDER BY SalesDetails.LineID, Items.ItemType;"
.Requery
.ColumnCount = 9
.ColumnWidths = ".2 in;.2 in;.2 in;.2 in;.2 in;.2 in;.2 in;.2 in;.2
in" End With
Click to expand...

You should know by now, you have to tell us *in what way* something
"doesn't work." <g>

In what event, on what form, is this code running? If you are running
it on form "OrderScreen", then any event before the Current event will
be too early, since the (presumably bound) control [SalesID] won't have
a value yet.

Note: this line ...
.Requery
Click to expand...

.... is unnecessary, since setting the RowSource propoerty always forces
a requery.
 
D

DS

Dirk said:
This works when I use it hrough a Query, but when I use it as SQL it
doesn't work.
Thanks
DS

With Forms!CheckPreview2!ListPreview
.RowSource = "SELECT SalesDetails.SalesID, SalesDetails.LineID, " & _
"SalesDetails.ItemID,
IIf(SalesDetails.Quantity=1,"""",SalesDetails.Quantity) AS EQuantity,
" & _ "IIf([Sub]=1,Items.ItemName,IIf([Sub]=2,"" "" &
Items.ItemName,IIf([Sub]=3,"" "" & Items.ItemName))) AS EName, "
& _ "Items.ItemType, SalesDetails.Sub, SalesDetails.Sent,
Format([BasePrice]*[Quantity],""$0.00"") AS Price " & _
"FROM Items INNER JOIN SalesDetails ON Items.ItemID =
SalesDetails.ItemID " & _
"GROUP BY SalesDetails.SalesID, SalesDetails.LineID,
SalesDetails.ItemID, " & _
"IIf(SalesDetails.Quantity=1,"""",SalesDetails.Quantity),
IIf([Sub]=1,Items.ItemName,IIf([Sub]=2,"" "" &
Items.ItemName,IIf([Sub]=3,"" "" & Items.ItemName))), " & _
"Items.ItemType, SalesDetails.Sub, SalesDetails.Sent,
Format([BasePrice]*[Quantity],""$0.00"") " & _
"HAVING(((SalesDetails.SalesID) =[Forms]![OrderScreen]![SalesID])) "
& _ "ORDER BY SalesDetails.LineID, Items.ItemType;"
.Requery
.ColumnCount = 9
.ColumnWidths = ".2 in;.2 in;.2 in;.2 in;.2 in;.2 in;.2 in;.2 in;.2
in" End With
Click to expand...


You should know by now, you have to tell us *in what way* something
"doesn't work." <g>

In what event, on what form, is this code running? If you are running
it on form "OrderScreen", then any event before the Current event will
be too early, since the (presumably bound) control [SalesID] won't have
a value yet.

Note: this line ...

.Requery
Click to expand...


... is unnecessary, since setting the RowSource propoerty always forces
a requery.
Click to expand...
Thanks,,,,,Dirk. Right again! It was running to early! And thanks for
that .requery Tip. One less thing for me to type.
DS
 
Top