DSum Function - Form/Subform

[Guest]

Hello

Thank you for reading this thread

The issue is a follows

In a Form, in the ControlSource of unbound Text Box I need to have a formula that would show me the total Plan Asset value for a specific Client that was selected.
I have already a box with calculated field that shows me the TOTAL Plan Assets value for ALL my Clients in the database

Now I need to see the total Plan Asset value for each selected Client. Client may have several Plan Assets listed in the subform of my Form, and these assets needs to be added together, but not the assets from the entire database

What I need to add to this expression? It seems that it is bound to the Table rather than to the Form itself
This is what I have in the ControlSource of my unbound tex box

=DSum("[Plan Assets]","Plans"

BIG THANKS

Sincerely

Natalia

 

[Guest]

try:
=DSum("[Plan Assets]","Plans","ClientID='" &
TargetClientID & "'")

-----Original Message-----
Hello,

Thank you for reading this thread.

The issue is a follows:

In a Form, in the ControlSource of unbound Text Box I

need to have a formula that would show me the total Plan
Asset value for a specific Client that was selected.

I have already a box with calculated field that shows me

the TOTAL Plan Assets value for ALL my Clients in the
database.

Now I need to see the total Plan Asset value for each

selected Client. Client may have several Plan Assets
listed in the subform of my Form, and these assets needs
to be added together, but not the assets from the entire
database.

What I need to add to this expression? It seems that it

is bound to the Table rather than to the Form itself.

This is what I have in the ControlSource of my unbound tex box:

=DSum("[Plan Assets]","Plans")

BIG THANKS.

Sincerely,

Natalia
.

 

[Guest]

Thanks for answering to my threat. But it is not working
I am getting the error: #Name
But I am not sure if I did this right

Tables linked to my Form
Table: Clients, Field: Client Nam

Subform
Table: Plans Fields: Plan Name, Plan Asset

Should I use in the formula instead of ClientID, Client Name or Plan Name as the Target? These are my primary keys
Again, I entered this in the ControlSource of my unbounded text Box

DSum("[Plan Assets]","Plans","ClientID='" &
TargetClientID & "'"

Regards

Natali

 

[Norman Fritag]

Natalia,
try:
strstring = DSum("[Plan Assets]","Plans","ClientID = " &
TargetClientID)
if Clientid is an integer, then don't use single quotations. Single
quotations are used for string values, # # are used for dates.

regards
Norman

Thanks for answering to my threat. But it is not working.
I am getting the error: #Name?
But I am not sure if I did this right.

Tables linked to my Form:
Table: Clients, Field: Client Name

Subform:
Table: Plans Fields: Plan Name, Plan Assets

Should I use in the formula instead of ClientID, Client Name or Plan Name

as the Target? These are my primary keys.

Again, I entered this in the ControlSource of my unbounded text Box.

DSum("[Plan Assets]","Plans","ClientID='" &
TargetClientID & "'")


Regards,

Natalia

 

[louis]

if [Plan Name] is same as [Client Name] then try, in
subform:

= DSum("[Plan Assets]","Plans","[Plan name] = '" & forms!
yourMainformName![Client Name] & "'")

or, if [Plan Name] is integer:
= DSum("[Plan Assets]","Plans","ClientID = " &
forms!yourMainformName![Client Name]

good luck.

-----Original Message-----
Hello Norman,
Thanks but it is not working.
I am not sure what is ClientID? I don't have Client ID

as a field, and the fields used for my Forms are Client
Name from Table: Clients and from Table: Plans, fields:
Plan Name and Plan Assets.

I entered your formula and still it gives me an error message: #Name?
I tried what you proposed and also I replaced Client ID

with Client Name and later with Plan Name to check if this
could work, but it didn't.

 

[Guest]

Finally got it

Here is the formula that works for my form

=DSum("[Plan Assets]","Plans","[Client Name] = '" & [Forms]![Asset Info]![Client Name] & "'"

Thank you for All of You

Natalia