Solved

FORMS: DSUM Lookup on Table - with Filter

Posted on 2013-01-16
16
401 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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
 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

691 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