Spreading a number

  • Thread starter Thread starter Carl B
  • Start date Start date
C

Carl B

I am working on a project where a user will enter a number
on the first of the month in a form and Access will be
required to spread that number over the days of the month.
I have a date table set up that the spread number will
need to go into. To make things more complicated, if the
number is changed on the 15th day, Access needs to
subtract the number that was entered for the first 15 days
from the new number entered on the 15th then spread the
result over the last 15 days(or how many days are
remaining in the month.)

I'm not well versed in Access and any suggestions as to
how to get Access to handle this task would be appreciated.

Thanks,
 
Firstly, you need to know how many days in the month right???
so, this code was in the Access help..

open a new module and paste in these 2 procedures..

===================================================
'Returns the number of days in a month. You can pass either a date or a
string to the DaysInMonth function.
Function DaysInMonth(dteInput As Date) As Integer
Dim intDays As Integer
' Add one month, subtract dates to find difference.
intDays = DateSerial(Year(dteInput), Month(dteInput) + 1, Day(dteInput))
_
- DateSerial(Year(dteInput), Month(dteInput), Day(dteInput))
DaysInMonth = intDays
End Function

'The following Sub procedure shows several different ways that you might
call the DaysInMonth function.
Sub CallDaysInMonth()
Dim intDays As Integer
intDays = DaysInMonth(#4/1/1996#)
intDays = DaysInMonth("4-1-96")
intDays = DaysInMonth("April 1, 1996")
End Sub
===================================================

Then you need to divide the number entered for the first of the month, eg
1000
So if you entered 1000 for Feb/04 (a leap year) then the above code will
tell you that there are 29 days in that month
So, it's 1000 / 29 isn't it? So you know how many days and how much
per day right?
So, if you put another figure in on the 15th, then its just straight
maths, isn't it?
 
Carl B said:
I am working on a project where a user will enter a number
on the first of the month in a form and Access will be
required to spread that number over the days of the month.
I have a date table set up that the spread number will
need to go into. To make things more complicated, if the
number is changed on the 15th day, Access needs to
subtract the number that was entered for the first 15 days
from the new number entered on the 15th then spread the
result over the last 15 days(or how many days are
remaining in the month.)
Click to expand...

Just calculate it in a form or report, but do not store it in a table.

First you need to find the days in a given month:

Public Function MonthLength(dtIn As Date) As Integer
On Error Resume Next ' To use this function in a query

MonthLength = Day(DateSerial(Year(dtIn), Month(dtIn) + 1, 0))

End Function

Next you need to divide the amount by the days in the month and adjust it
for the fifteenth. We may also have to alter the fifteenth because it can
fall on the weekend or holiday. This is aircode so you'll have to test it.
Call the function in the after update event of the date textbox

Public Function DistributeNumber(dtIn As Date, _
curIn As Currency, curOldAmt As Currency) As Double

' Add some error handling to avoid any chance of passing the wrong values

Dim intDayNumber As Integer

If Day(dtIn) >= 15 Then
intDayNumber = 15
intDayNumber = MonthLength(dtIn) - intDayNumber
DistributeNumber = (curIn - curOldAmt) / intDayNumber
Else
intDayNumber = MonthLength(dtIn)
DistributeNumber = curIn / intDayNumber
End If

End Function

--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Back
Top