Create a Sum expression in a report using WHERE

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Laura,

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

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

Allen Browne said:
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.

laura reid said:
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?
Click to expand...
Click to expand...
 
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.

Andre said:
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


.
Click to expand...
Click to expand...
 
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.

laura reid said:
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.

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


laura reid said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Allen, perfect. Thanks so much!!

Allen Browne said:
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.

laura reid said:
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.

Allen Browne said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top