DSum HELP Please

[MB]

Okay, I give up. I can't figure it out. I wrote in a previous post my
dilemma of having a field that add all costs (see original post below). I
was told to change my table and use the Dsum function.

I cannot change the table (I have to live with how it’s set up) and I just
cannot get the DSum to work.
Could you please help by providing the correct expression for DSum?

Here's the original post:
I have a program to track courses taken by our employees. The main form
includes employee name, position, employee number, etc. A subform (called
tblCoursesSubform2) consists of the name of the course and associate costs
(cost, travel, motel, meals, misc).

There is a text box (TotalAllCosts) with the following control source:
=Nz([Cost],0)+Nz([TravelCost],0)+Nz([MotelCost],0)+Nz([MealCost],0)+Nz([MiscCost],0)

I need to have another text box that will add the TotalAllCosts amount to
the last record added.

In other words, the last record has $1,200 in the TotalAllCosts box. When I
add a new record where the TotalAllCosts for that record is, say, $1,000, I
need a box (called TotalToDate) that will show $2,200 (total spent for that
employee to date).

Thank you so much for your help.

 

[Linq Adams via AccessMonster.com]

It would help if you showed us what you've tried so far. In general, to use
aggregate functions, such as DSum() on a calculated field, such as
TotalAllCosts, you can't use

DSum(TotalAllCosts)

but rather use DSum() against the forumla

DSum(Nz([Cost],0)+Nz([TravelCost],0)+Nz([MotelCost],0)+Nz([MealCost],0)+Nz(
[MiscCost],0))

 

[Klatuu]

If you are saying you are trying to update the last record you added in your
form with a value calculated in the current record, you should not do that.

Calculated values should not be stored in tables.

If I am misunderstanding your question, please post back and clarify what it
is you want to do. Also, you did not state the name of the table you want to
use the Dsum on. The DSum requires a domain name. That is a table or a
query. The basic syntax is:

=DSum("FieldName", "DomainName", "Criteria")
Where
FieldName is the name of the field to sum
DomainName is the name of the table or query FieldName is in
Criteria would be the same as an SQL WHERE clause without the word WHERE.
It would be used to limit which rows would be included in the sum.

 

[MB]

Shows how little I know about DSum.

Yes, I want to add new record info the the last record (an accumulative sum).

The field [TotalAllCost] is in a subform called "Courses Subform2" and the
record source is a query called "qry Courses2."

[TotalAllCosts] is a text box that adds all values in the following fields:
Costs, TravelCosts, MotelCosts, MealsCosts, MiscCosts.

When the form is opened for a new record, there should be a field showing
total costs to date.

Thank you for your help.
--
MB

If you are saying you are trying to update the last record you added in your
form with a value calculated in the current record, you should not do that.

Calculated values should not be stored in tables.

If I am misunderstanding your question, please post back and clarify what it
is you want to do. Also, you did not state the name of the table you want to
use the Dsum on. The DSum requires a domain name. That is a table or a
query. The basic syntax is:

=DSum("FieldName", "DomainName", "Criteria")
Where
FieldName is the name of the field to sum
DomainName is the name of the table or query FieldName is in
Criteria would be the same as an SQL WHERE clause without the word WHERE.
It would be used to limit which rows would be included in the sum.

--
Dave Hargis, Microsoft Access MVP

Okay, I give up. I can't figure it out. I wrote in a previous post my
dilemma of having a field that add all costs (see original post below). I
was told to change my table and use the Dsum function.

I cannot change the table (I have to live with how it’s set up) and I just
cannot get the DSum to work.
Could you please help by providing the correct expression for DSum?

Here's the original post:
I have a program to track courses taken by our employees. The main form
includes employee name, position, employee number, etc. A subform (called
tblCoursesSubform2) consists of the name of the course and associate costs
(cost, travel, motel, meals, misc).

There is a text box (TotalAllCosts) with the following control source:
=Nz([Cost],0)+Nz([TravelCost],0)+Nz([MotelCost],0)+Nz([MealCost],0)+Nz([MiscCost],0)

I need to have another text box that will add the TotalAllCosts amount to
the last record added.

In other words, the last record has $1,200 in the TotalAllCosts box. When I
add a new record where the TotalAllCosts for that record is, say, $1,000, I
need a box (called TotalToDate) that will show $2,200 (total spent for that
employee to date).

Thank you so much for your help.

 

[MB]

Thank you for your response.

I tried that formula and I get #Name? I added the = sign in front of DSum
and I get, "...Expression has a function containing the wrong number of
arguments."

 

[Klatuu]

--
Dave Hargis, Microsoft Access MVP

Shows how little I know about DSum.

Yes, I want to add new record info the the last record (an accumulative sum).

Don't even try. It will never be accurate. First, identifying the last
record added will be difficult enough with one user. In a multi user
environment, it may be almost impossible. Any changes to records would mean
you have to update that field in multiple records. Any deletions would cause
the same problem.

The field [TotalAllCost] is in a subform called "Courses Subform2" and the
record source is a query called "qry Courses2."

Will all the records to be included in the sum be in the subform's record
source? If so, there is another way to do this. If not, you would not be
doing the DSum on the subform's record sourece query.

[TotalAllCosts] is a text box that adds all values in the following fields:
Costs, TravelCosts, MotelCosts, MealsCosts, MiscCosts.

When the form is opened for a new record, there should be a field showing
total costs to date.

Thank you for your help.
--
MB

If you are saying you are trying to update the last record you added in your
form with a value calculated in the current record, you should not do that.

Calculated values should not be stored in tables.

If I am misunderstanding your question, please post back and clarify what it
is you want to do. Also, you did not state the name of the table you want to
use the Dsum on. The DSum requires a domain name. That is a table or a
query. The basic syntax is:

=DSum("FieldName", "DomainName", "Criteria")
Where
FieldName is the name of the field to sum
DomainName is the name of the table or query FieldName is in
Criteria would be the same as an SQL WHERE clause without the word WHERE.
It would be used to limit which rows would be included in the sum.

--
Dave Hargis, Microsoft Access MVP

Okay, I give up. I can't figure it out. I wrote in a previous post my
dilemma of having a field that add all costs (see original post below). I
was told to change my table and use the Dsum function.

I cannot change the table (I have to live with how it’s set up) and I just
cannot get the DSum to work.
Could you please help by providing the correct expression for DSum?

Here's the original post:
I have a program to track courses taken by our employees. The main form
includes employee name, position, employee number, etc. A subform (called
tblCoursesSubform2) consists of the name of the course and associate costs
(cost, travel, motel, meals, misc).

There is a text box (TotalAllCosts) with the following control source:
=Nz([Cost],0)+Nz([TravelCost],0)+Nz([MotelCost],0)+Nz([MealCost],0)+Nz([MiscCost],0)

I need to have another text box that will add the TotalAllCosts amount to
the last record added.

In other words, the last record has $1,200 in the TotalAllCosts box. When I
add a new record where the TotalAllCosts for that record is, say, $1,000, I
need a box (called TotalToDate) that will show $2,200 (total spent for that
employee to date).

Thank you so much for your help.

 

[MB]

Hi "Klatuu"

Yes, all the records to be included in the sum can be in the subform's record
source, which is qry Courses2
--
MB

--
Dave Hargis, Microsoft Access MVP

Shows how little I know about DSum.

Yes, I want to add new record info the the last record (an accumulative sum).

Don't even try. It will never be accurate. First, identifying the last
record added will be difficult enough with one user. In a multi user
environment, it may be almost impossible. Any changes to records would mean
you have to update that field in multiple records. Any deletions would cause
the same problem.

The field [TotalAllCost] is in a subform called "Courses Subform2" and the
record source is a query called "qry Courses2."

Will all the records to be included in the sum be in the subform's record
source? If so, there is another way to do this. If not, you would not be
doing the DSum on the subform's record sourece query.

[TotalAllCosts] is a text box that adds all values in the following fields:
Costs, TravelCosts, MotelCosts, MealsCosts, MiscCosts.

When the form is opened for a new record, there should be a field showing
total costs to date.

Thank you for your help.
--
MB

If you are saying you are trying to update the last record you added in your
form with a value calculated in the current record, you should not do that.

Calculated values should not be stored in tables.

If I am misunderstanding your question, please post back and clarify what it
is you want to do. Also, you did not state the name of the table you want to
use the Dsum on. The DSum requires a domain name. That is a table or a
query. The basic syntax is:

=DSum("FieldName", "DomainName", "Criteria")
Where
FieldName is the name of the field to sum
DomainName is the name of the table or query FieldName is in
Criteria would be the same as an SQL WHERE clause without the word WHERE.
It would be used to limit which rows would be included in the sum.

--
Dave Hargis, Microsoft Access MVP


:

Okay, I give up. I can't figure it out. I wrote in a previous post my
dilemma of having a field that add all costs (see original post below). I
was told to change my table and use the Dsum function.

I cannot change the table (I have to live with how it’s set up) and I just
cannot get the DSum to work.
Could you please help by providing the correct expression for DSum?

Here's the original post:
I have a program to track courses taken by our employees. The main form
includes employee name, position, employee number, etc. A subform (called
tblCoursesSubform2) consists of the name of the course and associate costs
(cost, travel, motel, meals, misc).

There is a text box (TotalAllCosts) with the following control source:
=Nz([Cost],0)+Nz([TravelCost],0)+Nz([MotelCost],0)+Nz([MealCost],0)+Nz([MiscCost],0)

I need to have another text box that will add the TotalAllCosts amount to
the last record added.

In other words, the last record has $1,200 in the TotalAllCosts box. When I
add a new record where the TotalAllCosts for that record is, say, $1,000, I
need a box (called TotalToDate) that will show $2,200 (total spent for that
employee to date).

Thank you so much for your help.

 

[Klatuu]

Then you really don't need a DSum at all.
Here is a way to show the value you want in a text box on the main form.

You don't say what version you are using, but if it is 2003 or older, you
can look at the Order form and the Orders Subform in the Northwind sample
database. It has an example very close to what you are trying to do.

I would suggest you put a text box for each of the fields involved in the
TotalCost in the Footer section of the subform and use the Sum function in
the text box like you would on a report:

For example
Control txtSumCost - Control Source - =Sum(Nz([Cost],0))
Control txtTravelCost - Control Source - =Sum(Nz([TravelCost],0)
etc.

Now in your text box on your main form, just add up the text boxes

=[Courses Subform2].Form!txtSumCost + [Courses
Subform2].Form!txtSumTravelCost + .... etc.
--
Dave Hargis, Microsoft Access MVP

Hi "Klatuu"

Yes, all the records to be included in the sum can be in the subform's record
source, which is qry Courses2
--
MB

--
Dave Hargis, Microsoft Access MVP

Shows how little I know about DSum.

Yes, I want to add new record info the the last record (an accumulative sum).

Don't even try. It will never be accurate. First, identifying the last
record added will be difficult enough with one user. In a multi user
environment, it may be almost impossible. Any changes to records would mean
you have to update that field in multiple records. Any deletions would cause
the same problem.

The field [TotalAllCost] is in a subform called "Courses Subform2" and the
record source is a query called "qry Courses2."

Will all the records to be included in the sum be in the subform's record
source? If so, there is another way to do this. If not, you would not be
doing the DSum on the subform's record sourece query.

[TotalAllCosts] is a text box that adds all values in the following fields:
Costs, TravelCosts, MotelCosts, MealsCosts, MiscCosts.

When the form is opened for a new record, there should be a field showing
total costs to date.

Thank you for your help.
--
MB


:

If you are saying you are trying to update the last record you added in your
form with a value calculated in the current record, you should not do that.

Calculated values should not be stored in tables.

If I am misunderstanding your question, please post back and clarify what it
is you want to do. Also, you did not state the name of the table you want to
use the Dsum on. The DSum requires a domain name. That is a table or a
query. The basic syntax is:

=DSum("FieldName", "DomainName", "Criteria")
Where
FieldName is the name of the field to sum
DomainName is the name of the table or query FieldName is in
Criteria would be the same as an SQL WHERE clause without the word WHERE.
It would be used to limit which rows would be included in the sum.

--
Dave Hargis, Microsoft Access MVP


:

Okay, I give up. I can't figure it out. I wrote in a previous post my
dilemma of having a field that add all costs (see original post below). I
was told to change my table and use the Dsum function.

I cannot change the table (I have to live with how it’s set up) and I just
cannot get the DSum to work.
Could you please help by providing the correct expression for DSum?

Here's the original post:
I have a program to track courses taken by our employees. The main form
includes employee name, position, employee number, etc. A subform (called
tblCoursesSubform2) consists of the name of the course and associate costs
(cost, travel, motel, meals, misc).

There is a text box (TotalAllCosts) with the following control source:
=Nz([Cost],0)+Nz([TravelCost],0)+Nz([MotelCost],0)+Nz([MealCost],0)+Nz([MiscCost],0)

I need to have another text box that will add the TotalAllCosts amount to
the last record added.

In other words, the last record has $1,200 in the TotalAllCosts box. When I
add a new record where the TotalAllCosts for that record is, say, $1,000, I
need a box (called TotalToDate) that will show $2,200 (total spent for that
employee to date).

Thank you so much for your help.

 

[MB]

Wonderful! Thank you so much.

By the way, sorry I didn't mention the verision (2007).

You were extremely helpful. These newsgroups are terrific.
--
MB

Then you really don't need a DSum at all.
Here is a way to show the value you want in a text box on the main form.

You don't say what version you are using, but if it is 2003 or older, you
can look at the Order form and the Orders Subform in the Northwind sample
database. It has an example very close to what you are trying to do.

I would suggest you put a text box for each of the fields involved in the
TotalCost in the Footer section of the subform and use the Sum function in
the text box like you would on a report:

For example
Control txtSumCost - Control Source - =Sum(Nz([Cost],0))
Control txtTravelCost - Control Source - =Sum(Nz([TravelCost],0)
etc.

Now in your text box on your main form, just add up the text boxes

=[Courses Subform2].Form!txtSumCost + [Courses
Subform2].Form!txtSumTravelCost + .... etc.
--
Dave Hargis, Microsoft Access MVP

Hi "Klatuu"

Yes, all the records to be included in the sum can be in the subform's record
source, which is qry Courses2
--
MB

--
Dave Hargis, Microsoft Access MVP


:

Shows how little I know about DSum.

Yes, I want to add new record info the the last record (an accumulative sum).

Don't even try. It will never be accurate. First, identifying the last
record added will be difficult enough with one user. In a multi user
environment, it may be almost impossible. Any changes to records would mean
you have to update that field in multiple records. Any deletions would cause
the same problem.

The field [TotalAllCost] is in a subform called "Courses Subform2" and the
record source is a query called "qry Courses2."

Will all the records to be included in the sum be in the subform's record
source? If so, there is another way to do this. If not, you would not be
doing the DSum on the subform's record sourece query.


[TotalAllCosts] is a text box that adds all values in the following fields:
Costs, TravelCosts, MotelCosts, MealsCosts, MiscCosts.

When the form is opened for a new record, there should be a field showing
total costs to date.

Thank you for your help.
--
MB


:

If you are saying you are trying to update the last record you added in your
form with a value calculated in the current record, you should not do that.

Calculated values should not be stored in tables.

If I am misunderstanding your question, please post back and clarify what it
is you want to do. Also, you did not state the name of the table you want to
use the Dsum on. The DSum requires a domain name. That is a table or a
query. The basic syntax is:

=DSum("FieldName", "DomainName", "Criteria")
Where
FieldName is the name of the field to sum
DomainName is the name of the table or query FieldName is in
Criteria would be the same as an SQL WHERE clause without the word WHERE.
It would be used to limit which rows would be included in the sum.

--
Dave Hargis, Microsoft Access MVP


:

Okay, I give up. I can't figure it out. I wrote in a previous post my
dilemma of having a field that add all costs (see original post below). I
was told to change my table and use the Dsum function.

I cannot change the table (I have to live with how it’s set up) and I just
cannot get the DSum to work.
Could you please help by providing the correct expression for DSum?

Here's the original post:
I have a program to track courses taken by our employees. The main form
includes employee name, position, employee number, etc. A subform (called
tblCoursesSubform2) consists of the name of the course and associate costs
(cost, travel, motel, meals, misc).

There is a text box (TotalAllCosts) with the following control source:
=Nz([Cost],0)+Nz([TravelCost],0)+Nz([MotelCost],0)+Nz([MealCost],0)+Nz([MiscCost],0)

I need to have another text box that will add the TotalAllCosts amount to
the last record added.

In other words, the last record has $1,200 in the TotalAllCosts box. When I
add a new record where the TotalAllCosts for that record is, say, $1,000, I
need a box (called TotalToDate) that will show $2,200 (total spent for that
employee to date).

Thank you so much for your help.

 

[Klatuu]

Glad I could help.
For your benefit, sometime you will get a faster, better answer when you
describe what you want to do rather than asking how to do something. That
way, people who are out here to help may know a good technique to solve the
problem.
Best of Luck.
--
Dave Hargis, Microsoft Access MVP

Wonderful! Thank you so much.

By the way, sorry I didn't mention the verision (2007).

You were extremely helpful. These newsgroups are terrific.
--
MB

Then you really don't need a DSum at all.
Here is a way to show the value you want in a text box on the main form.

You don't say what version you are using, but if it is 2003 or older, you
can look at the Order form and the Orders Subform in the Northwind sample
database. It has an example very close to what you are trying to do.

I would suggest you put a text box for each of the fields involved in the
TotalCost in the Footer section of the subform and use the Sum function in
the text box like you would on a report:

For example
Control txtSumCost - Control Source - =Sum(Nz([Cost],0))
Control txtTravelCost - Control Source - =Sum(Nz([TravelCost],0)
etc.

Now in your text box on your main form, just add up the text boxes

=[Courses Subform2].Form!txtSumCost + [Courses
Subform2].Form!txtSumTravelCost + .... etc.
--
Dave Hargis, Microsoft Access MVP

Hi "Klatuu"

Yes, all the records to be included in the sum can be in the subform's record
source, which is qry Courses2
--
MB


:


--
Dave Hargis, Microsoft Access MVP


:

Shows how little I know about DSum.

Yes, I want to add new record info the the last record (an accumulative sum).

Don't even try. It will never be accurate. First, identifying the last
record added will be difficult enough with one user. In a multi user
environment, it may be almost impossible. Any changes to records would mean
you have to update that field in multiple records. Any deletions would cause
the same problem.

The field [TotalAllCost] is in a subform called "Courses Subform2" and the
record source is a query called "qry Courses2."

Will all the records to be included in the sum be in the subform's record
source? If so, there is another way to do this. If not, you would not be
doing the DSum on the subform's record sourece query.


[TotalAllCosts] is a text box that adds all values in the following fields:
Costs, TravelCosts, MotelCosts, MealsCosts, MiscCosts.

When the form is opened for a new record, there should be a field showing
total costs to date.

Thank you for your help.
--
MB


:

If you are saying you are trying to update the last record you added in your
form with a value calculated in the current record, you should not do that.

Calculated values should not be stored in tables.

If I am misunderstanding your question, please post back and clarify what it
is you want to do. Also, you did not state the name of the table you want to
use the Dsum on. The DSum requires a domain name. That is a table or a
query. The basic syntax is:

=DSum("FieldName", "DomainName", "Criteria")
Where
FieldName is the name of the field to sum
DomainName is the name of the table or query FieldName is in
Criteria would be the same as an SQL WHERE clause without the word WHERE.
It would be used to limit which rows would be included in the sum.

--
Dave Hargis, Microsoft Access MVP


:

Okay, I give up. I can't figure it out. I wrote in a previous post my
dilemma of having a field that add all costs (see original post below). I
was told to change my table and use the Dsum function.

I cannot change the table (I have to live with how it’s set up) and I just
cannot get the DSum to work.
Could you please help by providing the correct expression for DSum?

Here's the original post:
I have a program to track courses taken by our employees. The main form
includes employee name, position, employee number, etc. A subform (called
tblCoursesSubform2) consists of the name of the course and associate costs
(cost, travel, motel, meals, misc).

There is a text box (TotalAllCosts) with the following control source:
=Nz([Cost],0)+Nz([TravelCost],0)+Nz([MotelCost],0)+Nz([MealCost],0)+Nz([MiscCost],0)

I need to have another text box that will add the TotalAllCosts amount to
the last record added.

In other words, the last record has $1,200 in the TotalAllCosts box. When I
add a new record where the TotalAllCosts for that record is, say, $1,000, I
need a box (called TotalToDate) that will show $2,200 (total spent for that
employee to date).

Thank you so much for your help.

 

[bill roberts]

Dave,
I read your post about using DSum. I used "=DSum(ExtAmt1, tblOrderDetail)" (sans quotes) as an expression for a textbox in the Parent Form and all I get is "#NAME?" in the textbox. DSum should work but I have no clue why it won't.
tblOrderDetail is viewed in a subform (frmOrderDetails1) from Parent Form frmOrders against tblOrders. Common field to each is Order# which is key in tblOrders. Any suggestions?
Thanks in advance,
Bill

 

[Douglas J. Steele]

The names of the field and table need to be in quotes:

=DSum("ExtAmt1", "tblOrderDetail")