subtotal on form footer

[Evan]

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

[Phil Stanton]

Try

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

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

Phil

"Evan" <ev****@gmail.comwrote in message
news:e6**********************************@k39g2000 hsf.googlegroups.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

[Evan]

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

[Phil Stanton]

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!InputDat e,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" <ev****@gmail.comwrote in message
news:75**********************************@c23g2000 hsa.googlegroups.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

[Evan]

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