Pull year from mm/dd/yyyy field into report

[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

 

[Douglas J. Steele]

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.

 

[Rick Brandt]

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)

=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.

 

[Douglas J. Steele]

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)

=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.

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.

 

[Tom Brown]

Thanks guys. Ricks reply worked like a charm. Learned something new today.
Thanks, again.

Tom

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)

=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.

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.