Number of Occurrences between 2 dates

[Kavy]

Good day,

I have a table which has the following fields:

-Payment Amount
-Freq (i.e monthly vs weekly)
-Freq amount (i.e. 2 for ever 2 weeks)
-Start Date
-End Date


I am trying to create query to calculate the total amount for each entry in the table. The total amount would only include full payment cycles between the two dates - i.e if the freq is 1 month, and the Start Date is Jan 1, 2011, and the end Date is Feb 16 2011, and the Amount is $60, the total amount would be $60.

I tried using datediff, but it assumes that there is 1 month between Jan 30 and Feb 1, which of course is incorrect. Does anyone know a better way of doing this?

My only other path-forward would be to calculate the differences in days and then use averages (i.e avg 30.41 days per month).

Thank you

Kavy

 

[Kavy]

To update this:

Is it possible to create a query which will create all the future payment dates for each table item, i.e this way I can use the date-add function which would give me exactly the right amount of payments?

So from the fields above, it creates a new table/query, which has all the fute dates for a given line item?

 

[Kavy]

Ok, I have done it, well sort of

I made the following query, which dateadds to my start date:

<code>

SELECT DateAdd([ShortForm],[Num],[First]) AS [Pay Dates], Fix([Days]/[AverageDaysPer]) AS Cycles, [datee]-[Start] AS Days, [Forms]![Budgets]![txtEDate] AS Datee, BVendors.Vendor, BAccounts.FirstOccurrence AS [First]
FROM Numbers, Freq INNER JOIN (BVendors INNER JOIN BAccounts ON BVendors.ID = BAccounts.BVendor) ON Freq.ID = BAccounts.FreqDesc;

</code>

The only issue is, the number of occurrences actually comes from another pre-populated table called "Numbers". When I try to use the calculated "Cycles" field instead of "Num", it assumes I only want one occurrence, does anyone have a workaround to this?

also, When I apply a filter to the "Pay Date" field, so only <= "End Date" appear, it prompts me to enter the "Start" parameter, and I dont know why?

Thank you!

 

[Kavy]

Since this is becoming it's own monster, I apologize but I reposted to this thread:

http://www.mrexcel.com/forum/showthread.php?p=2698736#post2698736