Solved

FORMS: DSUM Lookup on Table - with Filter

Posted on 2013-01-16
16
398 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

820 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