DSUM

[Tina]

I have created a report that gives me budget information from my tables. I
also created a query (qry_equipment_amt) that displays records with Invoice
Amounts.

I created a DSUM expression and in my report I have data grouped by
Partner_ID and the DSUM equation is calcualated in the Partner_ID Footer.

In my other query(qry_equipment_amt) it displays records based on a
Partner_ID, so for ex:
Partner_ID Amount
2 5000
6 100000
2 500
6 300

So I would like the partner group in my report (eg:2) to match the
partner_id in my query(qry_equipment_amt) and only add up the 2's for that
group. So the
respose should be $5500.00 for partner # 2.

Instead each partner displays the sum of all the Invoice Amounts $105800.

This is what I have but it just continues to give me the sum of all the
invoice amounts.

DSUM("[InvoiceAmt], "qry_equipment_amt", "[Partner_ID = Partner_ID]")

(partner id in my report query should match the partner Id of the other
query I cerated qry_equipment_amt.)

 

[Duane Hookom]

If Partner_ID is numeric, you could use:
DSUM("[InvoiceAmt], "qry_equipment_amt", "[Partner_ID] = " & [Partner_ID])
If all the values you want to sum are in the report and the text box is in
the Partner_ID footer, you can simply use:
=Sum(InvoiceAmt)

 

[Tina]

I did this and now I get all 0.00 because I added
=Nz(DSum("[Allocation_Amt]","qry_allocation_partner_equip","[Partner_ID]"="&
[Partner_ID]"),"$0.00 ").

The values are in an other query outside of the report.

If Partner_ID is numeric, you could use:
DSUM("[InvoiceAmt], "qry_equipment_amt", "[Partner_ID] = " & [Partner_ID])
If all the values you want to sum are in the report and the text box is in
the Partner_ID footer, you can simply use:
=Sum(InvoiceAmt)


--
Duane Hookom
MS Access MVP
--

I have created a report that gives me budget information from my tables. I
also created a query (qry_equipment_amt) that displays records with
Invoice
Amounts.

I created a DSUM expression and in my report I have data grouped by
Partner_ID and the DSUM equation is calcualated in the Partner_ID Footer.

In my other query(qry_equipment_amt) it displays records based on a
Partner_ID, so for ex:
Partner_ID Amount
2 5000
6 100000
2 500
6 300

So I would like the partner group in my report (eg:2) to match the
partner_id in my query(qry_equipment_amt) and only add up the 2's for that
group. So the
respose should be $5500.00 for partner # 2.

Instead each partner displays the sum of all the Invoice Amounts $105800.

This is what I have but it just continues to give me the sum of all the
invoice amounts.

DSUM("[InvoiceAmt], "qry_equipment_amt", "[Partner_ID = Partner_ID]")

(partner id in my report query should match the partner Id of the other
query I cerated qry_equipment_amt.)

 

[Duane Hookom]

Doesn't look like you used the syntax that I suggested.

--
Duane Hookom
MS Access MVP
--

I did this and now I get all 0.00 because I added
=Nz(DSum("[Allocation_Amt]","qry_allocation_partner_equip","[Partner_ID]"="&
[Partner_ID]"),"$0.00 ").

The values are in an other query outside of the report.

If Partner_ID is numeric, you could use:
DSUM("[InvoiceAmt], "qry_equipment_amt", "[Partner_ID] = " &
[Partner_ID])
If all the values you want to sum are in the report and the text box is
in
the Partner_ID footer, you can simply use:
=Sum(InvoiceAmt)


--
Duane Hookom
MS Access MVP
--

I have created a report that gives me budget information from my tables.
I
also created a query (qry_equipment_amt) that displays records with
Invoice
Amounts.

I created a DSUM expression and in my report I have data grouped by
Partner_ID and the DSUM equation is calcualated in the Partner_ID
Footer.

In my other query(qry_equipment_amt) it displays records based on a
Partner_ID, so for ex:
Partner_ID Amount
2 5000
6 100000
2 500
6 300

So I would like the partner group in my report (eg:2) to match the
partner_id in my query(qry_equipment_amt) and only add up the 2's for
that
group. So the
respose should be $5500.00 for partner # 2.

Instead each partner displays the sum of all the Invoice Amounts
$105800.

This is what I have but it just continues to give me the sum of all the
invoice amounts.

DSUM("[InvoiceAmt], "qry_equipment_amt", "[Partner_ID = Partner_ID]")

(partner id in my report query should match the partner Id of the other
query I cerated qry_equipment_amt.)

 

[Tina]

hi,

when I use the syntax you provided DSUM("[InvoiceAmt], "qry_equipment_amt",
"[Partner_ID] = " & [Partner_ID])

I get a syntax error..

Doesn't look like you used the syntax that I suggested.

--
Duane Hookom
MS Access MVP
--

I did this and now I get all 0.00 because I added
=Nz(DSum("[Allocation_Amt]","qry_allocation_partner_equip","[Partner_ID]"="&
[Partner_ID]"),"$0.00 ").

The values are in an other query outside of the report.

If Partner_ID is numeric, you could use:
DSUM("[InvoiceAmt], "qry_equipment_amt", "[Partner_ID] = " &
[Partner_ID])
If all the values you want to sum are in the report and the text box is
in
the Partner_ID footer, you can simply use:
=Sum(InvoiceAmt)


--
Duane Hookom
MS Access MVP
--

I have created a report that gives me budget information from my tables.
I
also created a query (qry_equipment_amt) that displays records with
Invoice
Amounts.

I created a DSUM expression and in my report I have data grouped by
Partner_ID and the DSUM equation is calcualated in the Partner_ID
Footer.

In my other query(qry_equipment_amt) it displays records based on a
Partner_ID, so for ex:
Partner_ID Amount
2 5000
6 100000
2 500
6 300

So I would like the partner group in my report (eg:2) to match the
partner_id in my query(qry_equipment_amt) and only add up the 2's for
that
group. So the
respose should be $5500.00 for partner # 2.

Instead each partner displays the sum of all the Invoice Amounts
$105800.

This is what I have but it just continues to give me the sum of all the
invoice amounts.

DSUM("[InvoiceAmt], "qry_equipment_amt", "[Partner_ID = Partner_ID]")

(partner id in my report query should match the partner Id of the other
query I cerated qry_equipment_amt.)

 

[Tom Lake]

hi,

when I use the syntax you provided DSUM("[InvoiceAmt],
"qry_equipment_amt",
"[Partner_ID] = " & [Partner_ID])

If Partner_ID is a text field, you have to enclose it in apostrophes or
quotes:

DSUM("[InvoiceAmt], "qry_equipment_amt", "[Partner_ID] = '" & [Partner_ID] &
"'")

would work.

Tom Lake

 

[Tina]

I fixed the syntax and it worked. But when my report comes up it asks me to
enter paramater values for two fields. There is nothing in my record source
with these values. Would you have any idea where there coming from and how
to remove this from coming up?

hi,

when I use the syntax you provided DSUM("[InvoiceAmt], "qry_equipment_amt",
"[Partner_ID] = " & [Partner_ID])

I get a syntax error..

Doesn't look like you used the syntax that I suggested.

--
Duane Hookom
MS Access MVP
--

I did this and now I get all 0.00 because I added
=Nz(DSum("[Allocation_Amt]","qry_allocation_partner_equip","[Partner_ID]"="&
[Partner_ID]"),"$0.00 ").

The values are in an other query outside of the report.

:

If Partner_ID is numeric, you could use:
DSUM("[InvoiceAmt], "qry_equipment_amt", "[Partner_ID] = " &
[Partner_ID])
If all the values you want to sum are in the report and the text box is
in
the Partner_ID footer, you can simply use:
=Sum(InvoiceAmt)


--
Duane Hookom
MS Access MVP
--

I have created a report that gives me budget information from my tables.
I
also created a query (qry_equipment_amt) that displays records with
Invoice
Amounts.

I created a DSUM expression and in my report I have data grouped by
Partner_ID and the DSUM equation is calcualated in the Partner_ID
Footer.

In my other query(qry_equipment_amt) it displays records based on a
Partner_ID, so for ex:
Partner_ID Amount
2 5000
6 100000
2 500
6 300

So I would like the partner group in my report (eg:2) to match the
partner_id in my query(qry_equipment_amt) and only add up the 2's for
that
group. So the
respose should be $5500.00 for partner # 2.

Instead each partner displays the sum of all the Invoice Amounts
$105800.

This is what I have but it just continues to give me the sum of all the
invoice amounts.

DSUM("[InvoiceAmt], "qry_equipment_amt", "[Partner_ID = Partner_ID]")

(partner id in my report query should match the partner Id of the other
query I cerated qry_equipment_amt.)

 

[Duane Hookom]

Look in the sorting and grouping dialog. Also, are there parameters in
qry_equipment_amt?

--
Duane Hookom
MS Access MVP
--

I fixed the syntax and it worked. But when my report comes up it asks me
to
enter paramater values for two fields. There is nothing in my record
source
with these values. Would you have any idea where there coming from and
how
to remove this from coming up?

hi,

when I use the syntax you provided DSUM("[InvoiceAmt],
"qry_equipment_amt",
"[Partner_ID] = " & [Partner_ID])

I get a syntax error..

Doesn't look like you used the syntax that I suggested.

--
Duane Hookom
MS Access MVP
--

I did this and now I get all 0.00 because I added
=Nz(DSum("[Allocation_Amt]","qry_allocation_partner_equip","[Partner_ID]"="&
[Partner_ID]"),"$0.00 ").

The values are in an other query outside of the report.

:

If Partner_ID is numeric, you could use:
DSUM("[InvoiceAmt], "qry_equipment_amt", "[Partner_ID] = " &
[Partner_ID])
If all the values you want to sum are in the report and the text box
is
in
the Partner_ID footer, you can simply use:
=Sum(InvoiceAmt)


--
Duane Hookom
MS Access MVP
--

I have created a report that gives me budget information from my
tables.
I
also created a query (qry_equipment_amt) that displays records
with
Invoice
Amounts.

I created a DSUM expression and in my report I have data grouped
by
Partner_ID and the DSUM equation is calcualated in the Partner_ID
Footer.

In my other query(qry_equipment_amt) it displays records based on
a
Partner_ID, so for ex:
Partner_ID Amount
2 5000
6 100000
2 500
6 300

So I would like the partner group in my report (eg:2) to match
the
partner_id in my query(qry_equipment_amt) and only add up the 2's
for
that
group. So the
respose should be $5500.00 for partner # 2.

Instead each partner displays the sum of all the Invoice Amounts
$105800.

This is what I have but it just continues to give me the sum of
all the
invoice amounts.

DSUM("[InvoiceAmt], "qry_equipment_amt", "[Partner_ID =
Partner_ID]")

(partner id in my report query should match the partner Id of the
other
query I cerated qry_equipment_amt.)