DSUM Lookup on Table - with Filter
I have a form into which I'm using an "On Open" event. If the total for [In] table of the [Amt] field = 0, where grouped [Srce_Type] = "Lockbox", then perform an operation.
I know I could write a GROUP BY query to return the desired output but was hoping to filter and sum total in a simpler way.
Example: If DSum("[Amt]", "[In]", [Srce_Type] = "Lockbox") = "0" Then
Is there a simple VBA line which would perform this operation?
I know I could write a GROUP BY query to return the desired output but was hoping to filter and sum total in a simpler way.
Example: If DSum("[Amt]", "[In]", [Srce_Type] = "Lockbox") = "0" Then
Is there a simple VBA line which would perform this operation?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Or how about if "Is NOT Null([Srce_Type] ??" . . I know that's a third scenario but thanks.
so what is now the question here?
ASKER
3) if the event should occur where [Srce_Type] is NOT null, then what is syntax?
<if the event should occur where [Srce_Type] is NOT null, then what is syntax? >
i don't understand, what you are trying to do, first you want to get the sum, using
If DSum("[Amt]", "[In]", "[Srce_Type] = 'Lockbox'") = 0 then
if [Srce_Type] is null or emplty or blank, then it will not be included in the SUM of the AMT
i don't understand, what you are trying to do, first you want to get the sum, using
If DSum("[Amt]", "[In]", "[Srce_Type] = 'Lockbox'") = 0 then
if [Srce_Type] is null or emplty or blank, then it will not be included in the SUM of the AMT
ASKER
"if [Srce_Type] is null or empty or blank, then it will not be included in the SUM of the AMT"
That is correct but in the prior statements I was looking for totals for only certain records. In this case, I'm looking for totals of ALL records where [Srce_Type] is null or empty or blank.
That is correct but in the prior statements I was looking for totals for only certain records. In this case, I'm looking for totals of ALL records where [Srce_Type] is null or empty or blank.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks so much for your patience.
ASKER
1) If there are two possible filters - either Lockbox / or Cashbox, do I enter
If DSum("[Amt]", "[In]", "[Srce_Type] = 'Lockbox'") = 0 or If DSum("[Amt]", "[In]", "[Srce_Type] = 'Cashbox'") = 0 Then
2) If IsNull [Srce_Type], do I enter
If DSum("[Amt]", "[In]", "IsNull([Srce_Type])") = 0 Then