Date Control

G

Guest

I have a report that is generated by a query.
My Query1 has these fields: fDate, Name, Amt In the Critiera field for
(fDate) I have: Between [Start Date] and [Ending Date].

This all works fine on the report with Sum for Amt at the end of the report.

The data looks like this
fDate Name Amt
01/01/2005 Tom 10.00
01/05/2005 Joe 20.00
01/29/2005 John 30.00
01/30/2005 Frank 30.00

The user only want to print what he entered today which is 01/30/2005
Which he will enters in the Dialog box of dates 01/30/2005 and 01/30/2005
inturn, it prints two names and a total of $60.00. Which is correct.
But HERE IS THE PROBLEM:

The user want to see the total of the whole month of $90.00
10/1/2005-10/30/2005 AND only wants to see what he input on the dates he
entered in the Dialog box. (10/29/2005 to 10/30/2005) More or less like a
total for the month regardless of what dates he enters in the prompt dialog
box.

Is there a way of doing this? I only using simple query and reports
functions?
Thanks
 
M

Marshall Barton

bladelock said:
I have a report that is generated by a query.
My Query1 has these fields: fDate, Name, Amt In the Critiera field for
(fDate) I have: Between [Start Date] and [Ending Date].

This all works fine on the report with Sum for Amt at the end of the report.

The data looks like this
fDate Name Amt
01/01/2005 Tom 10.00
01/05/2005 Joe 20.00
01/29/2005 John 30.00
01/30/2005 Frank 30.00

The user only want to print what he entered today which is 01/30/2005
Which he will enters in the Dialog box of dates 01/30/2005 and 01/30/2005
inturn, it prints two names and a total of $60.00. Which is correct.
But HERE IS THE PROBLEM:

The user want to see the total of the whole month of $90.00
10/1/2005-10/30/2005 AND only wants to see what he input on the dates he
entered in the Dialog box. (10/29/2005 to 10/30/2005) More or less like a
total for the month regardless of what dates he enters in the prompt dialog
box.


I think you can use a text box with an expression like:

=DSum("amt", "table", "Format(fDate,""yyyymm"") = """ &
Format([Ending Date], "yyyymm") & """")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top