Create a Sum expression in a report using WHERE

[laura reid]

Hi

In my report I'm trying to sum a field [req_str] only where the date [edate]
is less than 1/1/2004.

This is what I typed into the control source of the text box:

=sum (([req_str]) where ([edate] <"1/1/2004"))

and of course, I get an invalid syntax error.
What have I done wrong?

Thanks

 

[Allen Browne]

Try:
=Sum(IIf([edate] < #1/1/2004, [req_str], 0))

 

[Andre]

Laura,

I think that you need to use a function called 'dsum'
method. (see Help in Visual Basic)

Andre

 

[laura reid]

Thanks Allen,

I had already tried this and it ends up summing ALL the records in REQSTR.
I need it to only sum those REQSTR records where the EDATE is < 2004.

Try:
=Sum(IIf([edate] < #1/1/2004, [req_str], 0))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

In my report I'm trying to sum a field [req_str] only where the date
[edate]
is less than 1/1/2004.

This is what I typed into the control source of the text box:

=sum (([req_str]) where ([edate] <"1/1/2004"))

and of course, I get an invalid syntax error.
What have I done wrong?

 

[laura reid]

This doesn't quite work either. I have the text box in a footer, as I want
to sum by groups, this gives me a total of the whole report.

Laura,

I think that you need to use a function called 'dsum'
method. (see Help in Visual Basic)

Andre

-----Original Message-----
Hi

In my report I'm trying to sum a field [req_str] only where the date [edate]
is less than 1/1/2004.

This is what I typed into the control source of the text box:

=sum (([req_str]) where ([edate] <"1/1/2004"))

and of course, I get an invalid syntax error.
What have I done wrong?

Thanks


.

 

[Allen Browne]

Laura, I omitted one of the hashes:
=Sum(IIf([edate] < #1/1/2004#, [req_str], 0))

Try it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen,

I had already tried this and it ends up summing ALL the records in REQSTR.
I need it to only sum those REQSTR records where the EDATE is < 2004.

Try:
=Sum(IIf([edate] < #1/1/2004, [req_str], 0))

In my report I'm trying to sum a field [req_str] only where the date
[edate]
is less than 1/1/2004.

This is what I typed into the control source of the text box:

=sum (([req_str]) where ([edate] <"1/1/2004"))

and of course, I get an invalid syntax error.
What have I done wrong?

 

[laura reid]

Allen, perfect. Thanks so much!!

Laura, I omitted one of the hashes:
=Sum(IIf([edate] < #1/1/2004#, [req_str], 0))

Try it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen,

I had already tried this and it ends up summing ALL the records in REQSTR.
I need it to only sum those REQSTR records where the EDATE is < 2004.

Try:
=Sum(IIf([edate] < #1/1/2004, [req_str], 0))



In my report I'm trying to sum a field [req_str] only where the date
[edate]
is less than 1/1/2004.

This is what I typed into the control source of the text box:

=sum (([req_str]) where ([edate] <"1/1/2004"))

and of course, I get an invalid syntax error.
What have I done wrong?