Solved

FORMS: DSUM Lookup on Table - with Filter

Posted on 2013-01-16
16
396 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
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.
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

770 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