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_Clear
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'")
=DSum("[CRAmt]","[RA_Clear
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'")
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What output are you hoping for? (explain it in words)
Maybe this?
I'm assuming the date criteria desired is the max deposit date as seen in the RA_Clearing_Temp_In table.
=DSum("[CRAmt]","[RA_Clearing_Temp_In]", "[Srce_Type] = 'Lockbox' and [Deposit_Date] = #" & DMax("Deposit_Date", "RA_Clearing_Temp_In") & "#"I'm assuming the date criteria desired is the max deposit date as seen in the RA_Clearing_Temp_In table.
ASKER
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.
So if there are two dates (1/16/2013 and 1/17/2013), the output will pull only data of 1/17/2013.
Ok - try my last post...
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'" ) & "#")ASKER
Somehow there are missing brackets in your "mbizup" possible solution. I added but I'm still missing something.
=DSum("[CRAmt]","[RA_Clear
=DSum("[CRAmt]","[RA_Clear
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'" ) & "#")ASKER
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_T
FROM RA_Clearing_Temp_In
GROUP BY RA_Clearing_Temp_In.Srce_T
HAVING (((RA_Clearing_Temp_In.Src
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!
SELECT RA_Clearing_Temp_In.Srce_T
FROM RA_Clearing_Temp_In
GROUP BY RA_Clearing_Temp_In.Srce_T
HAVING (((RA_Clearing_Temp_In.Src
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!
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]ASKER
Okay. So can I get this converted into text box code that the form will read?
SELECT RA_Clearing_Temp_In.Srce_T
FROM RA_Clearing_Temp_In
GROUP BY RA_Clearing_Temp_In.Srce_T
HAVING (((RA_Clearing_Temp_In.Src
Just thinking . . . something like this:
=DSum("[CRAmt]","[RA_Clear
or this:
=DSum("[CRAmt]","[RA_Clear
But neither of these work.
SELECT RA_Clearing_Temp_In.Srce_T
FROM RA_Clearing_Temp_In
GROUP BY RA_Clearing_Temp_In.Srce_T
HAVING (((RA_Clearing_Temp_In.Src
Just thinking . . . something like this:
=DSum("[CRAmt]","[RA_Clear
or this:
=DSum("[CRAmt]","[RA_Clear
But neither of these work.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks so much for your patience.
Glad to help :)
ASKER
=DSum("[CRAmt]","[RA_Clear