I don't know how to use DSum

[bw]

My PartsForm has an unbound control TotSold as follows:
=nz([QtySold1])+nz([QtySold2])+nz([QtySold3])+nz([QtySold4])+nz([QtySold5])+nz([QtySold6])

I would like to show the SUM of all of the TotSold values on the form in
another unbound control GrandTotSold.
and I have tried various variations of the following:
=DSum("[TotSold]","tblParts")
but since I don't know what I'm doing, I'm not getting the right answer.

Can someone please explain?

Thanks,
Bernie

 

[Arvin Meyer]

DSum is not the function you wish to use. DSum is the total of all records
in a "domain". A "domain" is a table or query. So you must have stored all
those values to add them. They will not include values on a form until those
values have benn saved (the record updated).

TotSold, being an unbound control, is not storing the calculation. That's
generally a good thing since most calculations shouldn't be stored anyway.
Perhaps it might be a good idea to give us a general idea of what you are
trying to accomplish, and we can suggest a method for you.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[bw]

Thanks for the help Arvin,

I have six bound controls, QtySold1 through QtySold6. I want to show the
total for these six controls on the PartsForm, hence the calculation in an
Unbound Control named TotSold:
=nz([QtySold1])+nz([QtySold2])+nz([QtySold3])+nz([QtySold4])+nz([QtySold5])+
nz([QtySold6])

This calculation is giving me the correct value for each record on the Form.

Now I want to show on the Form, a "Constant" value (until I update one of
the QtySold fields), the TOTAL of all the QtySold fields, for all records in
the table.

Let me know if I didn't explain well enough, or give you enough information.

Thanks,
Bernie

DSum is not the function you wish to use. DSum is the total of all records
in a "domain". A "domain" is a table or query. So you must have stored all
those values to add them. They will not include values on a form until
those
values have benn saved (the record updated).

TotSold, being an unbound control, is not storing the calculation. That's
generally a good thing since most calculations shouldn't be stored anyway.
Perhaps it might be a good idea to give us a general idea of what you are
trying to accomplish, and we can suggest a method for you.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

My PartsForm has an unbound control TotSold as follows:
=nz([QtySold1])+nz([QtySold2])+nz([QtySold3])+nz([QtySold4])+nz([QtySold5])+
nz([QtySold6])

I would like to show the SUM of all of the TotSold values on the form in
another unbound control GrandTotSold.
and I have tried various variations of the following:
=DSum("[TotSold]","tblParts")
but since I don't know what I'm doing, I'm not getting the right answer.

Can someone please explain?

Thanks,
Bernie

 

[Arvin Meyer]

So wouldn't that be:

=DSum("[QtySold]","tblParts")

instead of:

=DSum("[TotSold]","tblParts")
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Thanks for the help Arvin,

I have six bound controls, QtySold1 through QtySold6. I want to show the
total for these six controls on the PartsForm, hence the calculation in an
Unbound Control named TotSold:
=nz([QtySold1])+nz([QtySold2])+nz([QtySold3])+nz([QtySold4])+nz([QtySold5])+
nz([QtySold6])

This calculation is giving me the correct value for each record on the Form.

Now I want to show on the Form, a "Constant" value (until I update one of
the QtySold fields), the TOTAL of all the QtySold fields, for all records in
the table.

Let me know if I didn't explain well enough, or give you enough information.

Thanks,
Bernie

DSum is not the function you wish to use. DSum is the total of all records
in a "domain". A "domain" is a table or query. So you must have stored all
those values to add them. They will not include values on a form until
those
values have benn saved (the record updated).

TotSold, being an unbound control, is not storing the calculation. That's
generally a good thing since most calculations shouldn't be stored anyway.
Perhaps it might be a good idea to give us a general idea of what you are
trying to accomplish, and we can suggest a method for you.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

My PartsForm has an unbound control TotSold as follows:

=nz([QtySold1])+nz([QtySold2])+nz([QtySold3])+nz([QtySold4])+nz([QtySold5])+
nz([QtySold6])

I would like to show the SUM of all of the TotSold values on the form in
another unbound control GrandTotSold.
and I have tried various variations of the following:
=DSum("[TotSold]","tblParts")
but since I don't know what I'm doing, I'm not getting the right answer.

Can someone please explain?

Thanks,
Bernie

 

[Pat Hartman]

DSum() is not the function you want to use. The domain functions require
you to specify a domain. You already have the domain which is the form's
RecordSource. You would use the Sum() function. However, since you want to
aggregate a calculation, you will need to repeat the calculation within the
Sum(). Make the controlSource of your total field:


=Sum(nz([QtySold1])+nz([QtySold2])+nz([QtySold3])+nz([QtySold4])+nz([QtySold
5])+nz([QtySold6]))

 

[bw]

Pat,

Proof that I don't know what I'm doing. Your solution worked perfectly. I
have some small idea what you have explained here, but I'm going to have to
study both the DSum() function, Sum() function, and "aggregate a
calculation".

I appreciate your help!
Thanks much,
Bernie

DSum() is not the function you want to use. The domain functions require
you to specify a domain. You already have the domain which is the form's
RecordSource. You would use the Sum() function. However, since you want
to
aggregate a calculation, you will need to repeat the calculation within
the
Sum(). Make the controlSource of your total field:


=Sum(nz([QtySold1])+nz([QtySold2])+nz([QtySold3])+nz([QtySold4])+nz([QtySold
5])+nz([QtySold6]))

My PartsForm has an unbound control TotSold as follows:
=nz([QtySold1])+nz([QtySold2])+nz([QtySold3])+nz([QtySold4])+nz([QtySold5])+
nz([QtySold6])

I would like to show the SUM of all of the TotSold values on the form in
another unbound control GrandTotSold.
and I have tried various variations of the following:
=DSum("[TotSold]","tblParts")
but since I don't know what I'm doing, I'm not getting the right answer.

Can someone please explain?

Thanks,
Bernie

 

[Pat Hartman]

Notice that with the domain functions, you must specify the domain either as
a query or table name in addition to any selection criteria and the column
you want to aggregate. In the aggregate functions, you only specify the
column you want to aggregate, the recordset is already defined so no
table/query or criteria can be specified. The aggregate functions work only
in three places - SQL queries, forms, and reports. Domain functions can be
used any place where a function is used.

Pat,

Proof that I don't know what I'm doing. Your solution worked perfectly. I
have some small idea what you have explained here, but I'm going to have to
study both the DSum() function, Sum() function, and "aggregate a
calculation".

I appreciate your help!
Thanks much,
Bernie

DSum() is not the function you want to use. The domain functions require
you to specify a domain. You already have the domain which is the form's
RecordSource. You would use the Sum() function. However, since you want
to
aggregate a calculation, you will need to repeat the calculation within
the
Sum(). Make the controlSource of your total field:


=Sum(nz([QtySold1])+nz([QtySold2])+nz([QtySold3])+nz([QtySold4])+nz([QtySold
5])+nz([QtySold6]))

My PartsForm has an unbound control TotSold as follows:

=nz([QtySold1])+nz([QtySold2])+nz([QtySold3])+nz([QtySold4])+nz([QtySold5])+
nz([QtySold6])

I would like to show the SUM of all of the TotSold values on the form in
another unbound control GrandTotSold.
and I have tried various variations of the following:
=DSum("[TotSold]","tblParts")
but since I don't know what I'm doing, I'm not getting the right answer.

Can someone please explain?

Thanks,
Bernie