DSum on Sub form

[Geezer via AccessMonster.com]

This should be easy, but I can't get it to work for the life of me. I have a
db to track information pertaining to various grants. Some grants have cost
share associated with them. Sometimes several grants may be related to each
other and I'd like to have a textbox on a subform to calculate the cost share
from the various related grants. Here's the setup:

Main form [View_form] - based on a query that lists each grant by [projnum].
Combo0 is used to select a [projnum]. ([projnum] is a text field) The whole
form is requeried in the AfterUpdate of Combo0.

Subform [postview_subform] - shows the post-award (financial info) record for
each grant, linked to [View_form].[Combo0] by [projnum]

The field I want to DSum is [Direct Cost Share Expnd] and it is Currency in
the table

I have three fields I can use to show a "relation" between grants, [Add CS],
[Add CS2], and [Add CS3]. When grants are related and I want to be able to
see the total cost share of all the related grants, I enter the [projnum] of
the related grants in these fields. Most of the time there will be nothing
in these fields as there is no relationship. When there is a relationship it
is genreally between only two grants. In several situations two or three
grants are related so I created the three [Add CS] fields.

All of the information above ([projnum], [Direct Cost Share Expnd], [Add CS],
[Add CS2], and [Add CS3] comes from a single table [post_tbl].

As the control source for the textbox to add the [Direct Cost Share Expnd] I
entered:

=DSum("[Direct Cost Share Expnd]","[post_tbl]","[Add CS]=[projnum]")+DSum("
[Direct Cost Share Expnd]","[post_tbl]","[Add CS2]=[projnum]")+DSum("[Direct
Cost Share Expnd]","[post_tbl]","[Add CS3]=[projnum]")

I've tried changing the order of [projnum] and [Add CS] as well as several
variations of added "s since [projnum] is text, but I either get a blank
textbox, a #Error, or ?Name. I'm not sure if the problem is
refressing/requerying or if the summation is not occurring. Any help with
the proper syntax is very much appreciated. Thanks

 

[Guest]

Hi Geezer,

It looks like you have an error in the syntax for the DSum().

This is what you have

DSum("[Direct Cost Share Expnd]","[post_tbl]","[Add CS]=[projnum]")

It should look like this:

DSum("[Direct Cost Share Expnd]","post_tbl","[Add CS] = '" & [projnum] & "'")

Expanded, the "where" part looks like

"[Add CS] = ' " & [projnum] & " ' "

because [projnum] is text. BTW, [Add CS], [Add CS2] & [Add CS3] must be text
also.

So the whole thing look like:

=DSum("[Direct Cost Share Expnd]","post_tbl","[Add CS] = '" & [projnum] &
"'")+DSum("[Direct Cost Share Expnd]","post_tbl","[Add CS2] = '" & [projnum]
& "'")+DSum("[Direct Cost Share Expnd]","post_tbl","[Add CS3] = '" &
[projnum]&"'")


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

This should be easy, but I can't get it to work for the life of me. I have a
db to track information pertaining to various grants. Some grants have cost
share associated with them. Sometimes several grants may be related to each
other and I'd like to have a textbox on a subform to calculate the cost share
from the various related grants. Here's the setup:

Main form [View_form] - based on a query that lists each grant by [projnum].
Combo0 is used to select a [projnum]. ([projnum] is a text field) The whole
form is requeried in the AfterUpdate of Combo0.

Subform [postview_subform] - shows the post-award (financial info) record for
each grant, linked to [View_form].[Combo0] by [projnum]

The field I want to DSum is [Direct Cost Share Expnd] and it is Currency in
the table

I have three fields I can use to show a "relation" between grants, [Add CS],
[Add CS2], and [Add CS3]. When grants are related and I want to be able to
see the total cost share of all the related grants, I enter the [projnum] of
the related grants in these fields. Most of the time there will be nothing
in these fields as there is no relationship. When there is a relationship it
is genreally between only two grants. In several situations two or three
grants are related so I created the three [Add CS] fields.

All of the information above ([projnum], [Direct Cost Share Expnd], [Add CS],
[Add CS2], and [Add CS3] comes from a single table [post_tbl].

As the control source for the textbox to add the [Direct Cost Share Expnd] I
entered:

=DSum("[Direct Cost Share Expnd]","[post_tbl]","[Add CS]=[projnum]")+DSum("
[Direct Cost Share Expnd]","[post_tbl]","[Add CS2]=[projnum]")+DSum("[Direct
Cost Share Expnd]","[post_tbl]","[Add CS3]=[projnum]")

I've tried changing the order of [projnum] and [Add CS] as well as several
variations of added "s since [projnum] is text, but I either get a blank
textbox, a #Error, or ?Name. I'm not sure if the problem is
refressing/requerying or if the summation is not occurring. Any help with
the proper syntax is very much appreciated. Thanks

 

[Geezer via AccessMonster.com]

Thanks Steve, I apparently left out a few dozen ' and "s All of the [Add
CS] fields are text so this worked fine. I needed to add NZ(,0) to each
argument get it to work and now it does perfectly. Thanks a lot to you and
this forum.

Hi Geezer,

It looks like you have an error in the syntax for the DSum().

This is what you have

DSum("[Direct Cost Share Expnd]","[post_tbl]","[Add CS]=[projnum]")

It should look like this:

DSum("[Direct Cost Share Expnd]","post_tbl","[Add CS] = '" & [projnum] & "'")

Expanded, the "where" part looks like

"[Add CS] = ' " & [projnum] & " ' "

because [projnum] is text. BTW, [Add CS], [Add CS2] & [Add CS3] must be text
also.

So the whole thing look like:

=DSum("[Direct Cost Share Expnd]","post_tbl","[Add CS] = '" & [projnum] &
"'")+DSum("[Direct Cost Share Expnd]","post_tbl","[Add CS2] = '" & [projnum]
& "'")+DSum("[Direct Cost Share Expnd]","post_tbl","[Add CS3] = '" &
[projnum]&"'")

HTH

This should be easy, but I can't get it to work for the life of me. I have a
db to track information pertaining to various grants. Some grants have cost

[quoted text clipped - 35 lines]

refressing/requerying or if the summation is not occurring. Any help with
the proper syntax is very much appreciated. Thanks

 

[Guest]

You're welcome

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Thanks Steve, I apparently left out a few dozen ' and "s All of the [Add
CS] fields are text so this worked fine. I needed to add NZ(,0) to each
argument get it to work and now it does perfectly. Thanks a lot to you and
this forum.

Hi Geezer,

It looks like you have an error in the syntax for the DSum().

This is what you have

DSum("[Direct Cost Share Expnd]","[post_tbl]","[Add CS]=[projnum]")

It should look like this:

DSum("[Direct Cost Share Expnd]","post_tbl","[Add CS] = '" & [projnum] & "'")

Expanded, the "where" part looks like

"[Add CS] = ' " & [projnum] & " ' "

because [projnum] is text. BTW, [Add CS], [Add CS2] & [Add CS3] must be text
also.

So the whole thing look like:

=DSum("[Direct Cost Share Expnd]","post_tbl","[Add CS] = '" & [projnum] &
"'")+DSum("[Direct Cost Share Expnd]","post_tbl","[Add CS2] = '" & [projnum]
& "'")+DSum("[Direct Cost Share Expnd]","post_tbl","[Add CS3] = '" &
[projnum]&"'")

HTH

This should be easy, but I can't get it to work for the life of me. I have a
db to track information pertaining to various grants. Some grants have cost

[quoted text clipped - 35 lines]

refressing/requerying or if the summation is not occurring. Any help with
the proper syntax is very much appreciated. Thanks