Having issues with DSUM not calculating as expected

[pbaldy]

Is the LineItem field text? Must be to have that one value in it. If so try



DSum("[LICost]","SubQ2","[LineItem] <= '" & [LineItem] & "'")

but watch out, as with a text field you get an alphabetic comparison (100 is less than 2).

[mittra]

It took a little finnagling, but I got the thing working. Instead of putting the DSUM into a subquery, I saved that portion out as it's own separate query and put that query as the from part of the SQL statement. So in the end it looks like this:

SELECT SubQ3.LineItem
FROM (SELECT qrySubQ2.LineItem, qrySubQ2.LICost, qrySubQ2.LIMustDo, qrySubQ2.LIScore, DSum("[LICost]","qrySubQ2","[LineItem] <= " & [LineItem]) *1 AS LITotal
FROM qrySubQ2
GROUP BY qrySubQ2.LineItem, qrySubQ2.LICost, qrySubQ2.LIMustDo, qrySubQ2.LIScore
ORDER BY qrySubQ2.LIMustDo, qrySubQ2.LIScore DESC) AS SubQ3
WHERE (((SubQ3.LITotal)<=([AvailFunds])*1));

Thanks for your help better understanding the DSUM statement!

[mittra]

Is the LineItem field text? Must be to have that one value in it. If so try

DSum("[LICost]","SubQ2","[LineItem] <= '" & [LineItem] & "'")

but watch out, as with a text field you get an alphabetic comparison (100 is less than 2).

Turns out LineItem was text. We had to break out the Projects into a subtable and grab the first LineItem # to put in the LineItem field so they would all stay numbers and would keep them in order. Made it tricky to break back out in the end, but we've got it working now.

[pbaldy]

Glad you got it working.