Date range

[Mr2006]

I have a query that I would like to run to get how much money have we paid in a period of time.

SELECT ProjectAct_tbl.StartDate, ProjectAct_tbl.CompletionDate, ProjectAct_tbl.Rate, ProjectAct_tbl.AmountReimb, ProjectAct_tbl.Quantity
FROM ProjectAct_tbl;

Example:

Start date completion date Quantity rate amount
01/01/2010 12/01/2010 12 $1000.00 $12000

Now, we are in the month of April.

I wan to be able to run the above and get the data for up the month of April since we are in April. So let us say if i want to run the data to sp[ecific month, i want to be able to get the right data.

Is there anyway to do this other than entering 12 records with start date and end date month by month.

 

[Ray1127]

Add the criteria to the query. Depending on if you want a date range or upto a specific date. in the Criteria for the field you want add the following:

For a Date Range Add Between [startdate] and [enddate] The query will prompt you for both the start and ending date.

If you want everything upto a specific date add <=[enddate]

 

[dhookom]

i want to be able to get the right data.

What is the right data? Should the right data be any record/project that might be in the month?

If this is the case, assuming you use controls on a form to enter your date range, try:

SELECT *
FROM ProjectAct_tbl
WHERE [Start Date]<= Forms!frmDates!txtEnd AND [Completion Date] >= Forms!frmDates!txtStart

Duane
Hook'D on Access
MS Access MVP

 

[Mr2006]

Yes, this is the right data but for the money part, is there a way to get the result based on the month we are in. example, if we pay monthly $1000.the result sould be $4000.00 for the above example.

The respns is that we have alot of recurrent requests for year long that we pay monthly. Sometime during the year, we are interested to find out how much have we paid so far and how much we will have to pay to the end of the year.

 

[dhookom]

Do you want to use the month difference from the [Start Date] to the current date * the [rate]?

Duane
Hook'D on Access
MS Access MVP

 

[Mr2006]

Yes. exactly.

 

[PHV]

find out how much have we paid so far
A starting point:

SELECT StartDate, Rate, DateDiff("m",StartDate,Date()) AS Quantity, Rate*DateDiff("m",StartDate,Date()) AS PaidSoFar
FROM ProjectAct_tbl
WHERE Date() BETWEEN StartDate And CompletionDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Mr2006]

01-Mar-08 30-Jun-08 1 $2,500.00 $2,500.00
01-Mar-08 30-Jun-08 1 $2,500.00 $2,500.00
15-Mar-08 15-Apr-08 1 $9,679.22 $9,679.22
16-Mar-08 19-Mar-08 16 $50.00 $800.00
16-Mar-08 19-Mar-08 16 $150.00 $2,400.00
24-Mar-08 1 $436.59 $436.59
26-Mar-08 26-Mar-08 1 $330.00 $330.00
31-Mar-08 04-Apr-08 1 $16,000 $16,000.
01-Apr-08 01-Apr-09 1 $2,667 $2,667
06-Apr-08 08-Apr-08 2 $3,500. $7,000.00
14-Apr-08 17-Jun-08 28 $75.00 $2,100.00
15-Apr-08 01-May-08 1 $3,800. $3,800.00
24-Apr-08 01-Aug-08 1 $12,800 $12,800.00
25-Apr-08 28-Apr-08 1 $2,800. $2,800.00
25-Apr-08 29-Apr-08 1 $1,000. $1,000.00

PHV,

I think this is very close. The above is sample data.

sometime staff would do quantity 1 for the period, if this is the case, it should show all the amount. if the quantity is 1 for one month, it should show the whole amount as well. If the comepletion date is null, it should also show the whole amount.

Thanks

 

[dhookom]

Mr2006,
What have you tried so far on your own?

Based on your sample data, could you provide a reporting period and desired output?

Duane
Hook'D on Access
MS Access MVP