FORMS: DSUM Lookup on Table - with Filter

I have a form in which there is a text box.  I want the text box to show data from a table directly.  I have VBA syntax which I thought might work but doesn't.  I desired 1) the total for "In" table of the [CrAmt] field, where grouped [Srce_Type] = "Lockbox."  This example isn't working.
=DSum("[CRAmt]","[RA_Clearing_Temp_In]", "[ Srce_Type] = 'Lockbox'")

If we find a solution for this then I'm hoping the other parts (like simple filters) might fall into place.  For example, I'd also like
2) the total for "In" table of the [CRAmt] field [Srce_Type] is null or empty or blank.
=DSum("[Amt]", "[ RA_Clearing_Temp_In]", "[Srce_Type] is Null")
3) the total for "In" table of the [CrAmt] field, where [Srce_Type] is either / or a given fixed selection.
= DSum("[CrAmt]", "[RA_Clearing_Temp_In]", "[Srce_Type] = 'Lockbox'") = 0 or DSum("[CRAmt]", "[RA_Clearing_Temp_In]", "[Srce_Type] = 'Cashbox'")
LVL 1
CFMIFinancial Systems AnalystAsked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
-->  So can I get this converted into text box code that the form will read?
Yup... there are a couple of ways to do this.

1.  Save it with the name qrySum (or something along those lines) and use a DLookup in your textbox's control source:
       = DLookup("SumOfCRAmt", "qrySum)

or

2.  Write it as a VBA function and call that function in your control source:

     Control source:
                   = getTheSum()

     VBA Code:

Function getTheSum() as Variant
      dim strSQL as string
      dim rs as DAO.Recordset

      strSQL = "SELECT RA_Clearing_Temp_In.Srce_Type,  " _
                 & "RA_Clearing_Temp_In.[Deposit Date], " _
                 &  "Sum(RA_Clearing_Temp_In.CRAmt) AS SumOfCRAmt  " _
                 &  "FROM RA_Clearing_Temp_In " _
                 &  "GROUP BY RA_Clearing_Temp_In.Srce_Type,  " _
                 &  "RA_Clearing_Temp_In.[Deposit Date] " _
                 &   "HAVING (((RA_Clearing_Temp_In.Srce_Type)='Lockbox') AND " _   
                 &   "((RA_Clearing_Temp_In.[Deposit Date]) = " _
                 &   "(SELECT Max(RA_Clearing_Temp_In.[Deposit Date]) AS " _
                 &   " [MaxOfDeposit Date] FROM RA_Clearing_Temp_In))); "
   set rs = currentdb.openrecordset(strSQL)
   if rs.recordcount = 0 then 
       getTheSum = NULL
  Else
       getTheSum = rs!SumOfCRAmt 
  end if
  rs.close
  set rs = nothing
End Sub

Open in new window

0
 
mbizupConnect With a Mentor Commented:
By 'not working' do you mean it is producing an error, or incorrect results?

Not sure if this is the issue, but you have an extraneous space in your criteria.

Try this:

=DSum("[CRAmt]","[RA_Clearing_Temp_In]", "[Srce_Type] = 'Lockbox'")

Open in new window



This should give you a total sum of the
      CRAmt field
      in the RA_Clearing_Temp_In table
      where [Srce_Type] = 'Lockbox'
0
 
pteranodon72Connect With a Mentor Commented:
It appears that you have a space between the bracket and Srce_Type in your example. Change the textbox's control source to:
=DSum("[CRAmt]","[RA_Clearing_Temp_In]", "[Srce_Type] = 'Lockbox'")

2) it looks like you changed the source field (first parameter). Try:
=DSum("[CRAmt]", "[ RA_Clearing_Temp_In]", "[Srce_Type] is Null")

3) Remove the third parameter for all conditions
=DSum("[CrAmt]", "[RA_Clearing_Temp_In]")

or join the two criteria with OR
=DSum("[CRAmt]","[RA_Clearing_Temp_In]", "([Srce_Type] = 'Lockbox') or ([Srce_Type] is Null)")

hth,
pT72
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
CFMIFinancial Systems AnalystAuthor Commented:
For criteria 1 you were correct.  Syntax was missing a space.  Whew!  Thank you.  Now - I'm looking for the additional filter of deposit date.  Can this be done or be done better?
=DSum("[CRAmt]","[RA_Clearing_Temp_In]", "[Srce_Type] = 'Lockbox'") and "[Deposit_Date] = 'max([Deposit_Date])'")
0
 
mbizupCommented:
What output are you hoping for?  (explain it in words)
0
 
mbizupCommented:
Maybe this?


=DSum("[CRAmt]","[RA_Clearing_Temp_In]", "[Srce_Type] = 'Lockbox' and [Deposit_Date] = #" & DMax("Deposit_Date", "RA_Clearing_Temp_In") & "#"

Open in new window


I'm assuming the date criteria desired is the max deposit date as seen in the RA_Clearing_Temp_In table.
0
 
CFMIFinancial Systems AnalystAuthor Commented:
I want the text box to show data from a table directly similar to criteria 1 above but with one additional filter:  The total for "In" table of the [CrAmt] field, where grouped [Srce_Type] = "Lockbox" AND where grouped [Deposit_Date] is the maximum of deposit dates in the table for those records.

So if there are two dates (1/16/2013 and 1/17/2013), the output will pull only data of 1/17/2013.
0
 
mbizupCommented:
Ok - try my last post...
0
 
mbizupCommented:
If that doesnt do it, try this:

=DSum("[CRAmt]","[RA_Clearing_Temp_In]", "[Srce_Type] = 'Lockbox' and [Deposit_Date] = #" & DMax("Deposit_Date", "RA_Clearing_Temp_In","[Srce_Type] = 'Lockbox'" ) & "#")

Open in new window

0
 
CFMIFinancial Systems AnalystAuthor Commented:
Somehow there are missing brackets in your "mbizup" possible solution.  I added but I'm still missing something.

=DSum("[CRAmt]","[RA_Clearing_Temp_In]", "[Srce_Type] = 'Lockbox' and [Deposit_Date] = #" & DMax("[Deposit_Date]", "[RA_Clearing_Temp_In]") & "#"
0
 
mbizupCommented:
Give my second attempt a try...

=DSum("[CRAmt]","[RA_Clearing_Temp_In]", "[Srce_Type] = 'Lockbox' and [Deposit_Date] = #" & DMax("Deposit_Date", "RA_Clearing_Temp_In","[Srce_Type] = 'Lockbox'" ) & "#")

Open in new window

0
 
CFMIFinancial Systems AnalystAuthor Commented:
I went back to the drawing board and found two things. 1.  My use of deposit date included an errant "_".  I took that underline away and the resultant output failed.  Then 2.  I attempted to run a query which will give me the desired output.  The query looks like this:

SELECT RA_Clearing_Temp_In.Srce_Type, RA_Clearing_Temp_In.[Deposit Date], Sum(RA_Clearing_Temp_In.CRAmt) AS SumOfCRAmt
FROM RA_Clearing_Temp_In
GROUP BY RA_Clearing_Temp_In.Srce_Type, RA_Clearing_Temp_In.[Deposit Date]
HAVING (((RA_Clearing_Temp_In.Srce_Type)="Lockbox") AND ((RA_Clearing_Temp_In.[Deposit Date])=Max([RA_Clearing_Temp_In]![Deposit Date])));

The query output an UNDESIRED result.  The sums were correct for its deposit date but all dates in the table output.  I'm therefore not even sure my desired output CAN work!
0
 
mbizupCommented:
Try this:

SELECT r.Srce_Type, r.[Deposit Date], Sum(RA_Clearing_Temp_In.CRAmt) AS SumOfCRAmt
FROM RA_Clearing_Temp_In r
INNER JOIN
(SELECT MAX([Deposit Date] AS MaxDate FROM [RA_Clearing_Temp_In] WHERE Srce_Type="Lockbox") q
ON q.MaxDate = r.[DepositDate]

Open in new window

0
 
CFMIFinancial Systems AnalystAuthor Commented:
Okay.  So can I get this converted into text box code that the form will read?

SELECT RA_Clearing_Temp_In.Srce_Type, RA_Clearing_Temp_In.[Deposit Date], Sum(RA_Clearing_Temp_In.CRAmt) AS SumOfCRAmt
FROM RA_Clearing_Temp_In
GROUP BY RA_Clearing_Temp_In.Srce_Type, RA_Clearing_Temp_In.[Deposit Date]
HAVING (((RA_Clearing_Temp_In.Srce_Type)="Lockbox") AND ((RA_Clearing_Temp_In.[Deposit Date])=(SELECT Max(RA_Clearing_Temp_In.[Deposit Date]) AS [MaxOfDeposit Date] FROM RA_Clearing_Temp_In)));

Just thinking . . . something like this:
=DSum("[CRAmt]","[RA_Clearing_Temp_In]","[Srce_Type] = 'Lockbox'” and dlookup(“[Deposit Date]","[RA_Clearing_Temp_In]", DMax("[Deposit Date]"))
or this:
=DSum("[CRAmt]","[RA_Clearing_Temp_In]","[Srce_Type] = 'Lockbox'” and “[Deposit Date]"= DMax("[Deposit Date]"))

But neither of these work.
0
 
CFMIFinancial Systems AnalystAuthor Commented:
Thanks so much for your patience.
0
 
mbizupCommented:
Glad to help :)
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.