SQL Rowsource

[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

 

[Dirk Goldgar]

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

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

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

 

[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

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

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

Thanks,,,,,Dirk. Right again! It was running to early! And thanks for
that .requery Tip. One less thing for me to type.
DS