Solved

FORMS: DSUM Lookup on Table - with Filter

Posted on 2013-01-16
16
395 Views
Last Modified: 2013-01-17
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'")
0
Comment
Question by:CFMI
  • 9
  • 6
16 Comments
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 410 total points
ID: 38784799
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
 
LVL 14

Assisted Solution

by:pteranodon72
pteranodon72 earned 90 total points
ID: 38784826
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
 
LVL 1

Author Comment

by:CFMI
ID: 38787637
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
 
LVL 61

Expert Comment

by:mbizup
ID: 38787664
What output are you hoping for?  (explain it in words)
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38787682
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
 
LVL 1

Author Comment

by:CFMI
ID: 38787690
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
 
LVL 61

Expert Comment

by:mbizup
ID: 38787706
Ok - try my last post...
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38787731
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 1

Author Comment

by:CFMI
ID: 38787761
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
 
LVL 61

Expert Comment

by:mbizup
ID: 38787780
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
 
LVL 1

Author Comment

by:CFMI
ID: 38787934
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
 
LVL 61

Expert Comment

by:mbizup
ID: 38787988
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
 
LVL 1

Author Comment

by:CFMI
ID: 38788666
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
 
LVL 61

Accepted Solution

by:
mbizup earned 410 total points
ID: 38788988
-->  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
 
LVL 1

Author Closing Comment

by:CFMI
ID: 38789139
Thanks so much for your patience.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38789191
Glad to help :)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now