Using month as a calculation value in a query

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

Guest

I need to return a value for a whole or a part of a month. This is to be used
for an invoicing system.
Example: A subscription is priced on a monthly basis. A subscription that is
active for a whole month should be invoiced for the value '"1" multiplied
with the monthly price.
A subscription that is active from april 14th, should be invoiced for the
value "0,53" (the part of april where the subscription has been active),
multiplied with the monthly price. The problem arrises as there ar different
numbers of days in each month. If I should invoice th subscription starting
the 14th og april until the 30th of june, the value to multiply the monthly
price with is 2,53.
Do anyone know how to calculate this value in a query when the start and end
date is available in the query?
 
Perhaps you can do an append query, that adds a column to the data, which
calculates the number of days from EOM to the Subscription Start Date.

Then using that number, you'll know what the multiplier should be.
 
This does not really solve the problem. The same situation will occur also
the month the subscription ends. What I need is a formula that based on the
values "Startdate" and "Enddate" returns the number of months between this
two dates with two digits. I.e. from april 14th until june 30th, the number
of months is 2,53.
As there will be some hundred subscriptions to administer, the solution with
an append query would start it, but as time passes the maintennance here will
increase. If it can be calculated with a formula, I only get the value I need
when I need it.
 
Have you looked at the DateDiff and DateSerial functions.

You can calculate the number of days in the month with

Day(DateSerial(Year(StartDate), Month(StartDate)+1,0))

You can use date diff to get the number of month boundaries crossed
DateDiff("m",StartDate,EndDate)

You'll have to adjust for partial months.

OR

You can download the "More Complete DateDiff Function" Graham Seach and Doug
Steele wrote as a starting point to get what you want

http://www.accessmvp.com/djsteele/Diff2Dates.html

You specify how you want the difference between two date/times to be calculated
by providing which of ymdhns (for years, months, days, hours, minutes and
seconds) you want calculated.
 
Just wanted to say THANKS for the Dates2Diffs Function provided in this
message. It works GREAT!!!!

thanks
Connie
 
Back
Top