Taking the sum of a 'DLookup' field

[Access::Student]

Hey, I've got a continuous form and I'm trying to get some totals correctly.
In each row of my "table" I have these two values:

=DLookUp("[grand_total_requested]","Submissions Totals","kf_submissions_id="
& [kp_submissions_id])

=DLookUp("[grand_total_approved]","Submissions Totals","kf_submissions_id="
& [kp_submissions_id])

So they are basically the same. Submission Totals is a query that finds the
total of another query which is based on a table.
Those two values work, but I'm trying to total them up and I can't figure
out how. If I just 'Sum()' the controls (named [requested] and [accepted] and
do not conflict with any other names) I get an error, which I think is
supposed to happen(because they don't get stored?). But I also can't put
those previous expressions in sums because that would make no sense. Any help
on what I should do?

 

[Access::Student]

Ok, so I've changed some things and the situation is a bit different now.
I've eliminated a redundant query, so now my two fields in each row are:

=DSum("[amount_requested]","Financial Totals","kf_submissions_id=" &
[kp_submissions_id])

=DSum("[amount_approved]","Financial Totals","kf_submissions_id=" &
[kp_submissions_id])

And now the total that I use is:

=DSum("[amount_requested]","Financial Totals","kf_groups_id=" &
[kf_groups_id])

This total might not make sense to anyone, but that's irrelevant since it
won't work all the time. That's because I have a filter on this form:

SELECT SUBMISSIONS.* FROM SUBMISSIONS WHERE
(((IIf([Forms]![Groups]![Filter_toggle]="ALL",True,[SUBMISSIONS].[fiscal_year]=[Forms]![Groups]![Filter_toggle]))<>False));

The previous formula will work when 'ALL' is selected on the filter, but not
if a specific value is selected, as it doesn't have anything to do with it.
So I still don't know how to create a total.

 

[Access::Student]

Thanks a lot for the help it makes a lot of sense and works. I'm just having
one problem now. The values 'grand_total_approved' and
'grand_total_requested' returned by the query are text. So I can't use
formatting on them, or ordering. How do I force them to be number format? I
assume that's in the SQL?

If you compute the amount_requested and amount_approved value in the form's
underlying query rather than as computed controls on the form, and bind
controls in the detail section of the form to the two computed columns, you
should then be able to sum each in computed controls in the form with:

=Sum([grand_total_requested])
and:
=Sum([grand_total_approved])

So the query would be:

SELECT *,
DSum("amount_requested","Financial Totals","kf_submissions_id=" &
[kp_submissions_id] AS grand_total_requested,
DSum("amount_approved","Financial Totals","kf_submissions_id=" &
[kp_submissions_id] AS grand_total_approved
FROM Submissions
WHERE (fiscal_year = [Forms]![Groups]![Filter_toggle]
OR [Forms]![Groups]![Filter_toggle] = "ALL");

Note the different expression in the WHERE clause. If the value of the
Filter_toggle control is a fiscal year first part of the OR operation will
evaluate to True for each row with that year, so the query will be restricted
to the year; if the value of the Filter_toggle control is 'All' the second
part of the OR operation will evaluate to True for any row, so all rows will
be returned. I would think this will be more efficient than calling the IIf
function. It is the commonly used way of 'optionalizing' parameters like
this.

If the form is not to be updatable you could either join Submissions to
Financial Totals in the query, grouping it on the columns from the former and
summing the amount_requested and amount_approved columns from the latter, or
you could use subqueries to return the grand_total_requested and
grand_total_approved columns in the query rather than calling the DSum
functions. For the form to be updatable, however, call the DSum functions as
in my example above.

Ken Sheridan
Stafford, England

Ok, so I've changed some things and the situation is a bit different now.
I've eliminated a redundant query, so now my two fields in each row are:

=DSum("[amount_requested]","Financial Totals","kf_submissions_id=" &
[kp_submissions_id])

=DSum("[amount_approved]","Financial Totals","kf_submissions_id=" &
[kp_submissions_id])

And now the total that I use is:

=DSum("[amount_requested]","Financial Totals","kf_groups_id=" &
[kf_groups_id])

This total might not make sense to anyone, but that's irrelevant since it
won't work all the time. That's because I have a filter on this form:

SELECT SUBMISSIONS.* FROM SUBMISSIONS WHERE
(((IIf([Forms]![Groups]![Filter_toggle]="ALL",True,[SUBMISSIONS].[fiscal_year]=[Forms]![Groups]![Filter_toggle]))<>False));

The previous formula will work when 'ALL' is selected on the filter, but not
if a specific value is selected, as it doesn't have anything to do with it.
So I still don't know how to create a total.

Hey, I've got a continuous form and I'm trying to get some totals correctly.
In each row of my "table" I have these two values:

[quoted text clipped - 13 lines]

those previous expressions in sums because that would make no sense. Any help
on what I should do?

 

[Access::Student]

Perfect. Thanks so much for your help.

You could use the Val function to convert the values to numbers, e.g.

Val(DSum("amount_requested","Financial Totals","kf_submissions_id=" &
[kp_submissions_id])) AS grand_total_requested

or if its currency, as I'd guess it is from the table name, you could use the
CCur function to return the values as currency data type:

CCur(DSum("amount_requested","Financial Totals","kf_submissions_id=" &
[kp_submissions_id])) AS grand_total_requested

Ken Sheridan
Stafford, England

Thanks a lot for the help it makes a lot of sense and works. I'm just having
one problem now. The values 'grand_total_approved' and
'grand_total_requested' returned by the query are text. So I can't use
formatting on them, or ordering. How do I force them to be number format? I
assume that's in the SQL?

If you compute the amount_requested and amount_approved value in the form's
underlying query rather than as computed controls on the form, and bind

[quoted text clipped - 65 lines]

those previous expressions in sums because that would make no sense. Any help
on what I should do?