Pull year from mm/dd/yyyy field into report

  • Thread starter Thread starter Tom Brown
  • Start date Start date
T

Tom Brown

I am trying to total prior years activity into a beginning balance
on a report using the DSum function as shown:

=nz(DSum("Amount","tbl_NUS1"," Year([PdYr])=2004 and Year([PdYr])=2005 "),0)

The PdYr column in table tbl_NUS1 is format Date/Time.
The Amount column is in Number format Double field size.
The PdYr entries are shown as 1/1/2006. So I am trying to pull all 2004 and
2005
data into one number in one cell in the report. (1/1/2004 thru 12/1/2005)

There is definitely data in the prior years because I ran a query on a query
which
gave me the total, but I get $0 when I run the report.

If this doesnt work, can someone please show me how to pull a query into the
same BegBal field. The query shows only one cell in one category, which is
the BegBal total which is the correct amount, but again I cant figure out
how that is supposed to be pulled into this cell. Either way will work for
me.

Thanks in advance for your help.

Tom
 
Use "Year([PdYr])=2004 Or Year([PdYr])=2005 "

Remember that a given record has only one PdYr value associated with it, and
that PdYr value can only be associated with a single year.
 
Tom said:
I am trying to total prior years activity into a beginning balance
on a report using the DSum function as shown:

=nz(DSum("Amount","tbl_NUS1"," Year([PdYr])=2004 and
Year([PdYr])=2005 "),0)
Click to expand...

=nz(DSum("Amount","tbl_NUS1","[PdYr] > #12/31/2003# and [PdYr] < #1/1/2006#
"),0)

Never apply criteria to an expression if you can avoid it. You lose the ability
to use an index if you do that.
 
Rick Brandt said:
Tom said:
I am trying to total prior years activity into a beginning balance
on a report using the DSum function as shown:

=nz(DSum("Amount","tbl_NUS1"," Year([PdYr])=2004 and
Year([PdYr])=2005 "),0)
Click to expand...

=nz(DSum("Amount","tbl_NUS1","[PdYr] > #12/31/2003# and [PdYr] <
#1/1/2006# "),0)

Never apply criteria to an expression if you can avoid it. You lose the
ability to use an index if you do that.
Click to expand...

You're right: that's better than my suggestion.

Not only does it allow you to use the index (if there is one in this case),
but it avoids multiple function calls.
 
Thanks guys. Ricks reply worked like a charm. Learned something new today.
Thanks, again.

Tom

Douglas J. Steele said:
Rick Brandt said:
Tom said:
I am trying to total prior years activity into a beginning balance
on a report using the DSum function as shown:

=nz(DSum("Amount","tbl_NUS1"," Year([PdYr])=2004 and
Year([PdYr])=2005 "),0)
Click to expand...

=nz(DSum("Amount","tbl_NUS1","[PdYr] > #12/31/2003# and [PdYr] <
#1/1/2006# "),0)

Never apply criteria to an expression if you can avoid it. You lose the
ability to use an index if you do that.
Click to expand...

You're right: that's better than my suggestion.

Not only does it allow you to use the index (if there is one in this case),
but it avoids multiple function calls.
Click to expand...
 
Back
Top