How to DSUM multiple fields

[Gator]

How could/should I add 07rt, 06rt & 05rt to the following control source
property of the textbox?

=DSum("[08rt]","Collections","Year([Date]) = " & Year([Date]) & " And
Month([Date]) = " & Month([Date]) & " And Day([Date]) = " & Day([Date]))

thanks

 

[Douglas J. Steele]

Add how? Are you saying you want the sum of the four fields?

=DSum("Nz([08rt], 0) + Nz(]07rt], 0) + Nz([06rt], 0) + Nz([05rt])",
"Collections", "DateValue([Date]) = " & Format(Date, "\#yyyy\-mm\-dd\#"))

Note that you should rename your field from Date: that's a reserved word,
and should never be used for your own purposes. For a comprehensive list of
names to avoid (plus a link to a free utility that will check your
application for compliance), see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

More important, though, is that fact that your field names (05rt, 06rt, 07rt
and 08rt) implies that your tables aren't properly modelled. That looks
suspiciously like a repeating group, which means that your tables don't even
comply to 1st normal form.

 

[Allen Browne]

You can sum multiple fields in the first expression, e.g.:

=DSum("Nz([08rt],0) + Nz([07rt],0) + Nz([06rt],0) + Nz([05rt],0)",
"Collections", "[Date] = Date()")

A better solution would be to redesign the table so you don't have repeating
fields like that. It looks like you built a spreadsheet in Access: many
columns of similar data in one table, where you should have many *records*
in one column in a related table.

Also, Date is a reserved word, and will give you problems as a field name.

 

[Gator]

I want the sum of the four fields...yes.

but to explain the table models....

here is the design...(abbrv.)

Date - date/time
06rc - num
06rt - num
07rc - num
07rt - num

I get the same report from different people on the same day. For ex, I'll
get three reports of the same format from three different people. So, I use
a table displayed through a form to enter the report numbers with each report
using it's own row so there will be three rows of the same date but with
different amounts in the fields. I could use a spreasheet but I like to use
the form because I can use multiple textboxes to display different sets of
totals on the same page. I guess I'm using this table as a spreadsheet whill
utilizing the versatility of the Access Form. ???

Add how? Are you saying you want the sum of the four fields?

=DSum("Nz([08rt], 0) + Nz(]07rt], 0) + Nz([06rt], 0) + Nz([05rt])",
"Collections", "DateValue([Date]) = " & Format(Date, "\#yyyy\-mm\-dd\#"))

Note that you should rename your field from Date: that's a reserved word,
and should never be used for your own purposes. For a comprehensive list of
names to avoid (plus a link to a free utility that will check your
application for compliance), see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

More important, though, is that fact that your field names (05rt, 06rt, 07rt
and 08rt) implies that your tables aren't properly modelled. That looks
suspiciously like a repeating group, which means that your tables don't even
comply to 1st normal form.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

How could/should I add 07rt, 06rt & 05rt to the following control source
property of the textbox?

=DSum("[08rt]","Collections","Year([Date]) = " & Year([Date]) & " And
Month([Date]) = " & Month([Date]) & " And Day([Date]) = " & Day([Date]))

thanks

 

[Gator]

what does [Date] & Date() refer to?

You can sum multiple fields in the first expression, e.g.:

=DSum("Nz([08rt],0) + Nz([07rt],0) + Nz([06rt],0) + Nz([05rt],0)",
"Collections", "[Date] = Date()")

A better solution would be to redesign the table so you don't have repeating
fields like that. It looks like you built a spreadsheet in Access: many
columns of similar data in one table, where you should have many *records*
in one column in a related table.

Also, Date is a reserved word, and will give you problems as a field name.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

How could/should I add 07rt, 06rt & 05rt to the following control source
property of the textbox?

=DSum("[08rt]","Collections","Year([Date]) = " & Year([Date]) & " And
Month([Date]) = " & Month([Date]) & " And Day([Date]) = " & Day([Date]))

thanks

 

[John W. Vinson]

what does [Date] & Date() refer to?

[Date] is the name of your field in your table.
Date() is the builtin Access function that looks at the computer clock to get
today's date.

If you find it confusing, Access does too - hence the advice to use a
DIFFERENT fieldname other than Date.