Pull year from mm/dd/yyyy field into report

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tom Brown - 11 Nov 2006 11:09 GMT

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

Reply to this Message

Douglas J. Steele - 11 Nov 2006 12:25 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

>I am trying to total prior years activity into a beginning balance
> on a report using the DSum function as shown:
[quoted text clipped - 26 lines]
>
> Tom

Reply to this Message

Rick Brandt - 11 Nov 2006 12:39 GMT

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

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

Douglas J. Steele - 11 Nov 2006 15:45 GMT

>> I am trying to total prior years activity into a beginning balance
>> on a report using the DSum function as shown:
[quoted text clipped - 7 lines]
> 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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

Reply to this Message

Tom Brown - 11 Nov 2006 16:56 GMT

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:
[quoted text clipped - 12 lines]
> Not only does it allow you to use the index (if there is one in this case),
> but it avoids multiple function calls.

Reply to this Message