subtotal on form footer

Hi,

I have a continuous form with three fields: id, date, score. I am
trying to add total field on the form footer to show total score for
certain year.

I created a unbound textbox and set the control source as:
=sum(iif(year(date)=2007,nz(score),0))

It work, and give me the subtotal for 2007. Now I want to add another
unbound textbox(text1) on the footer so that people can input the
year, and I change the control source to
=sum(iif(year(date)=[text1],nz(score),0))

It shows #error
I also tried [forms].[myformname].[text1] instead of [text1], same
error. Can anyone help me out?

Thank you

Evan

Re: subtotal on form footer

Try

=sum(iif(CStr(year(date))=2007,nz(score),0))

Convert the year to a string so that the compare will work

Phil



"Evan" wrote in message
news:e68469a4-de96-42aa-8237-7006e62186db@k39g2000hsf.google groups.com...
> Hi,
>
> I have a continuous form with three fields: id, date, score. I am
> trying to add total field on the form footer to show total score for
> certain year.
>
> I created a unbound textbox and set the control source as:
> =sum(iif(year(date)=2007,nz(score),0))
>
> It work, and give me the subtotal for 2007. Now I want to add another
> unbound textbox(text1) on the footer so that people can input the
> year, and I change the control source to
> =sum(iif(year(date)=[text1],nz(score),0))
>
> It shows #error
> I also tried [forms].[myformname].[text1] instead of [text1], same
> error. Can anyone help me out?
>
> Thank you
>
> Evan

Re: subtotal on form footer

Thank you Phil,

If I want 2007 only, that work. My challenge is I need to include a
textbox in the formula so that people can input any year.

Evan

Re: subtotal on form footer

You need a TextBox called "InputDate" in the header of your continuous form.
I leave it to you to work out routines that are going to result in a 4 digit
year (prpbably not in the future and not before a certain date with only
numbers.

Then your clause becomes

Sum(IIf(CStr(year(date))=Forms!MyFormName!InputDate,Nz(score ),0))

Incidently it is bad practice to call a field "Date" it is a reserved word.
OK if you always put square brackets [] round it. Much better to call it
somethin like MatchDate

Phil


"Evan" wrote in message
news:75e64c99-e834-466f-9976-5567d79b1ddf@c23g2000hsa.google groups.com...
> Thank you Phil,
>
> If I want 2007 only, that work. My challenge is I need to include a
> textbox in the formula so that people can input any year.
>
> Evan

Re: subtotal on form footer

You are right, name a field "date" is very bad idea.
I tried the clause you wrote. Same error. If I replace the Forms!
MyFormName!InputDate with a real number, say 2007, it works.
Thank you for being patient