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?
LVL 1
CFMIFinancial Systems AnalystAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
syntax is

If DSum("[Amt]", "[In]", "[Srce_Type] = 'Lockbox'") = 0  Then
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do:


If DSum("[Amt]", "[In]", "[Srce_Type] = 'Lockbox'") = 0 Then
0
 
CFMIFinancial Systems AnalystAuthor Commented:
Okay now let's build on that.  Two scenarios:
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
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
CFMIFinancial Systems AnalystAuthor Commented:
Or how about if  "Is NOT Null([Srce_Type] ??"  . . I know that's a third scenario but thanks.
0
 
Rey Obrero (Capricorn1)Commented:
so what is now the question here?
0
 
CFMIFinancial Systems AnalystAuthor Commented:
3) if the event should occur where [Srce_Type] is NOT null, then what is syntax?
0
 
Rey Obrero (Capricorn1)Commented:
<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
0
 
CFMIFinancial Systems AnalystAuthor Commented:
"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.
0
 
Rey Obrero (Capricorn1)Commented:
<I'm looking for totals of ALL records where [Srce_Type] is null or empty or blank. >

then use this

If DSum("[Amt]", "[In]", "[Srce_Type] is Null") = 0 then

or


If DSum("[Amt]", "[In]", "IsNull([Srce_Type])") = 0 then
0
 
CFMIFinancial Systems AnalystAuthor Commented:
Thanks so much for your patience.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.