It's rather difficult to see just what you are trying to do, but here are a
few things I noticed. You need to use the IIf function in this case. With
IIf you do not use quotes around field names as you would with a domain
function:
=DSum(IIff([CDDiscountDP] =
1,[CDDiscountAmount]*[CDQuantity],[CDFinalPrice]*[CDQuantity]*[CDDiscountPercent],
_
"tblChecks","[CDCheckID] = " & [Forms]![frmCheckPreview]![TxtSalesID] &
" AND " & [CDItemID] > 42 & "")
Note that the underscore is just for convenience in posting here; you would
not use it in an expression (although you can use it for line breaks in
VBA).
Note that I changed the format of the AND part of the expression. The
expression I suggested assumes that CDItemID is a number field. If it is a
text field, I think the end part of the expression would be " AND """
[CDItemID] > 42 & """". I need to say that I am not quite as sure as I
would like to be about the strings of quote marks.
I also need to stress that although I can recognize some syntax problems,
I'm not sure what you are trying to do, so I can't say if my suggestions
will work. I don't see how the expression in your most recent post can be
evaluating CDItemID, since CDItemID is within the quotes, and therefore part
of the text string that includes the word AND.
DS said:
DS wrote:
How can I combine these two statements into one?
Thanks
DS
=Nz(DSum("-(CDFinalPrice*CDDiscountPercent) *
CDQuantity","tblCheckDetails","[CDCheckID]= " &
[Forms]![frmCheckPreview]![TxtSalesID] & " AND [CDItemID] =31"),0)
=Nz(DSum("-CDDiscountAmount*CDQuantity","tblCheckDetails","[CDCheckID]=
" & [Forms]![frmCheckPreview]![TxtSalesID] & " AND [CDItemID] =31"),0)
I came up with this but I'm getting the #Name error.
=DSum(If("CDDiscountDP"=1,("CDDiscountAmount*CDQuantity"),(("CDFinalPrice*CDQuantity")*"CDDiscountPercent")),
"tblChecks","CDCheckID = " & [Forms]![frmCheckPreview]![TxtSalesID] & "
AND [CDItemID] > 42")
I also did this, it works but it doesn't seem effecient.
=Nz(DSum("CDDiscountAmount * CDQuantity","tblCheckDetails","[CDCheckID]= "
& [Forms]![frmCheckPreview]![TxtSalesID] & " AND [CDItemID]
=31"),0)+Nz(DSum("-(CDFinalPrice*CDDiscountPercent) *
CDQuantity","tblCheckDetails","[CDCheckID]= " &
[Forms]![frmCheckPreview]![TxtSalesID] & " AND [CDItemID] =31"),0)
