SQL Rowsource

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

DS

This worked until I added the CDDiscountDP=3 part....
I have it returning a value from two fields on a form.....
Can you not do that?
Thanks
DS

With Forms!frmCheckPreview!ListOrders
..RowSource = "SELECT tblCheckDetails.CDCheckID,
tblCheckDetails.CDLineID, tblCheckDetails.CDGroupID, " & _
"tblItems.ItemTypeID, tblCheckDetails.CDItemID,
tblCheckDetails.CDQuantity, " & _
"IIf([tblCheckDetails].[CDDiscountDP]>=1 And
[tblCheckDetails].[CDDiscountWhere] Not Like ""A"","" "" & ""*"" &
[tblDiscounts].[DiscountName], " & _
"IIf([tblCheckDetails].[CDDiscountDP]>=1 And [CDDiscountWhere]=""A"",""
"" & ""**"" & [tblDiscounts].[DiscountName], " & _
"IIf([CDSub]=1,[tblItems].[ItemName],IIf([CDSub]=2,"" "" &
[tblItems].[ItemName],IIf([CDSub]=3,"" "" & [tblItems].[ItemName],
" & _
"IIf([CDSub]=4,"" "" &
[tblItems].[ItemName],IIf([CDSub]=5,[tblItems].[ItemName]))))))) AS
NAME, " & _
"tblCheckDetails.CDSent, tblCheckDetails.CDDiscountDP,
CCur(IIf([CDDiscountDP]=1,[CDQuantity]*[CDDiscountAmount], " & _
"IIf([CDDiscountDP]=2,-(([CDQuantity]*[CDFinalPrice]))*[CDDiscountPercent],
" & _
"IIf([CDDiscountDP]=3,Forms!frmCheckPreview!TxtNoDiscounts*Forms!frmCheckPreview!TxtCheckPercent,[CDQuantity]*[CDFinalPrice])))))
AS PRICE, " & _
"tblCheckDetails.CDMenuID, tblCheckDetails.CDMenuCatID " & _
"FROM tblItems INNER JOIN (tblCheckDetails LEFT JOIN tblDiscounts ON
tblCheckDetails.CDDiscountID = tblDiscounts.DiscountID) ON
tblItems.ItemID = tblCheckDetails.CDItemID " & _
"GROUP BY tblCheckDetails.CDCheckID, tblCheckDetails.CDLineID,
tblCheckDetails.CDGroupID, " & _
"tblItems.ItemTypeID, tblCheckDetails.CDItemID,
tblCheckDetails.CDQuantity, " & _
"IIf([tblCheckDetails].[CDDiscountDP]>=1 And
[tblCheckDetails].[CDDiscountWhere] Not Like ""A"","" "" & ""*"" &
[tblDiscounts].[DiscountName], " & _
"IIf([tblCheckDetails].[CDDiscountDP]>=1 And [CDDiscountWhere]=""A"",""
"" & ""**"" & [tblDiscounts].[DiscountName], " & _
"IIf([CDSub]=1,[tblItems].[ItemName],IIf([CDSub]=2,"" "" &
[tblItems].[ItemName],IIf([CDSub]=3,"" "" &
[tblItems].[ItemName],IIf([CDSub]=4,"" "" & [tblItems].[ItemName], " & _
"IIf([CDSub]=5,[tblItems].[ItemName]))))))), tblCheckDetails.CDSent,
tblCheckDetails.CDDiscountDP, " & _
"CCur(IIf([CDDiscountDP]=1,[CDQuantity]*[CDDiscountAmount],IIf([CDDiscountDP]=2,-(([CDQuantity]*[CDFinalPrice]))*[CDDiscountPercent],
" & _
"IIf([CDDiscountDP]=3,Forms!frmCheckPreview!TxtNoDiscounts*Forms!frmCheckPreview!TxtChecPercent,[CDQuantity]*[CDFinalPrice]))))),
" & _
"tblCheckDetails.CDMenuID, tblCheckDetails.CDMenuCatID " & _
"HAVING (((tblCheckDetails.CDCheckID) =
[Forms]![frmCheckAction]![TxtSalesID]) " & _
"And ((tblCheckDetails.CDSent) = -1)) " & _
"ORDER BY tblCheckDetails.CDGroupID, tblCheckDetails.CDLineID,
tblItems.ItemTypeID;"
..ColumnCount = 12
..ColumnWidths = "0 in;0 in;0 in;0 in;0 in;.35 in;2.9 in;0 in;0 in;.5
in;0 in;0 in"
..Requery
End With
 
You may well have reached the length limit for the RowSource. Alias your
tables with single letters:

"FROM tblItems As I INNER JOIN (tblCheckDetails AS C LEFT JOIN tblDiscounts AS
D ON C.CDDiscountID = D.DiscountID) ON I.ItemID = C.CDItemID " & _

This will shorten all of the clauses considerably.

You also seem to have an aggregate query with no aggregate functions involved
so drop the GROUP BY clause entirely. If you were grouping just to remove
duplicates then add the DISTINCT predicate instead. The HAVING clause should
be a WHERE clause.

That should cut the length sufficiently but if not then the ORDER BY can use
column numbers rather than names, harder to read but shorter, so change it to:

"ORDER BY 3, 5, 4"

HTH
John
##################################
Don't Print - Save trees
This worked until I added the CDDiscountDP=3 part....
I have it returning a value from two fields on a form.....
Can you not do that?
Thanks
DS

.RowSource = "SELECT tblCheckDetails.CDCheckID,
tblCheckDetails.CDLineID, tblCheckDetails.CDGroupID, " & _
"tblItems.ItemTypeID, tblCheckDetails.CDItemID,
tblCheckDetails.CDQuantity, " & _
"IIf([tblCheckDetails].[CDDiscountDP]>=1 And
[tblCheckDetails].[CDDiscountWhere] Not Like ""A"","" "" & ""*"" &
[tblDiscounts].[DiscountName], " & _
"IIf([tblCheckDetails].[CDDiscountDP]>=1 And [CDDiscountWhere]=""A"",""
"" & ""**"" & [tblDiscounts].[DiscountName], " & _
"IIf([CDSub]=1,[tblItems].[ItemName],IIf([CDSub]=2,"" "" &
[tblItems].[ItemName],IIf([CDSub]=3,"" "" & [tblItems].[ItemName],
" & _
"IIf([CDSub]=4,"" "" &
[tblItems].[ItemName],IIf([CDSub]=5,[tblItems].[ItemName]))))))) AS
NAME, " & _
"tblCheckDetails.CDSent, tblCheckDetails.CDDiscountDP,
CCur(IIf([CDDiscountDP]=1,[CDQuantity]*[CDDiscountAmount], " & _
"IIf([CDDiscountDP]=2,-(([CDQuantity]*[CDFinalPrice]))*[CDDiscountPercent],
" & _
"IIf([CDDiscountDP]=3,Forms!frmCheckPreview!TxtNoDiscounts*Forms!frmCheckPreview!TxtCheckPercent,[CDQuantity]*[CDFinalPrice])))))
AS PRICE, " & _
"tblCheckDetails.CDMenuID, tblCheckDetails.CDMenuCatID " & _
"FROM tblItems INNER JOIN (tblCheckDetails LEFT JOIN tblDiscounts ON
tblCheckDetails.CDDiscountID = tblDiscounts.DiscountID) ON
tblItems.ItemID = tblCheckDetails.CDItemID " & _
"GROUP BY tblCheckDetails.CDCheckID, tblCheckDetails.CDLineID,
tblCheckDetails.CDGroupID, " & _
"tblItems.ItemTypeID, tblCheckDetails.CDItemID,
tblCheckDetails.CDQuantity, " & _
"IIf([tblCheckDetails].[CDDiscountDP]>=1 And
[tblCheckDetails].[CDDiscountWhere] Not Like ""A"","" "" & ""*"" &
[tblDiscounts].[DiscountName], " & _
"IIf([tblCheckDetails].[CDDiscountDP]>=1 And [CDDiscountWhere]=""A"",""
"" & ""**"" & [tblDiscounts].[DiscountName], " & _
"IIf([CDSub]=1,[tblItems].[ItemName],IIf([CDSub]=2,"" "" &
[tblItems].[ItemName],IIf([CDSub]=3,"" "" &
[tblItems].[ItemName],IIf([CDSub]=4,"" "" & [tblItems].[ItemName], " & _
"IIf([CDSub]=5,[tblItems].[ItemName]))))))), tblCheckDetails.CDSent,
tblCheckDetails.CDDiscountDP, " & _
"CCur(IIf([CDDiscountDP]=1,[CDQuantity]*[CDDiscountAmount],IIf([CDDiscountDP]=2,-(([CDQuantity]*[CDFinalPrice]))*[CDDiscountPercent],
" & _
"IIf([CDDiscountDP]=3,Forms!frmCheckPreview!TxtNoDiscounts*Forms!frmCheckPreview!TxtChecPercent,[CDQuantity]*[CDFinalPrice]))))),
" & _
"tblCheckDetails.CDMenuID, tblCheckDetails.CDMenuCatID " & _
"HAVING (((tblCheckDetails.CDCheckID) =
[Forms]![frmCheckAction]![TxtSalesID]) " & _
"And ((tblCheckDetails.CDSent) = -1)) " & _
"ORDER BY tblCheckDetails.CDGroupID, tblCheckDetails.CDLineID,
tblItems.ItemTypeID;"
Click to expand...
 
Back
Top