Full Version: Room & Board $600 per month, pro rated.
UtterAccess Discussion Forums > Microsoft® Access > Access Date + Time
psycodad1
May 23 2007, 08:42 PM
I need to have my database assess Room & Board @ $600 per month, and pro-rated for the first month and last month. So if the person comes in on May 10th, 31 days in that month of course. That person is there for the rest of that month, then leaves on Sept 21st. His rent will be 22 days multiplied by $600 divided by the 31 days (or 30 days depending on the month) for May. The charge of course for June, July, and Aug is $600 each. Then 20 days multiplied by $600 divided by the 30 days (or 31 days depending on the month) for Sept. The total should be $2625.8064. I would really appreciate any help that you can give me. The fields that I would use for this is as follows:

AdmissionDate, DischargeDate, RoomNBoard
Jerry Dennison
May 23 2007, 09:42 PM
CODE


TotalRoomNBoard2: IIf(Day([Admission Date])=1,

[RoomNBoard],(DateDiff("d",[Admission Date],

DateSerial(Year([Admission Date]),

Month([Admission Date])+1,0))+1)/Day(DateSerial(Year([Admission Date]),

Month([Admission Date])+1,0))*[RoomNBoard])+

((DateDiff("m",[Admission Date],[Discharge Date])-1)*[RoomNBoard])+

(IIf(Day([Discharge Date])=DateSerial(Year([Discharge Date]),Month([Discharge Date])+1,0),

[RoomNBoard],Day([Discharge Date])/Day(DateSerial(Year([Discharge Date]),

Month([Discharge Date])+1,0))*[RoomNBoard]))


This will give the rate even if it is for only 1 day, so you will probably have to put a test in for a minimum charge.
psycodad1
May 23 2007, 10:13 PM
Thank you Jerry. I'll give it a shot.
psycodad1
May 24 2007, 12:46 PM
Ok I haven't been able to get it to work correctly. It counts the departure day as a charge, but we don't charge for this day. I have tried modifying it but just cant seem to get it to take off a day correctly. I did get it to work with the dates 1/10/2007 to 1/20/2007 by modifying it to this:
QUOTE
TotalRoomNBoard2: IIf(Day([AdmissionDate])=1,[RoomNBoard],(DateDiff("d",[AdmissionDate],DateSerial(Year([AdmissionDate]),Month([AdmissionDate])+1,0)))/Day(DateSerial(Year([AdmissionDate]),Month([AdmissionDate])+1,0))*[RoomNBoard])+
((DateDiff("m",[AdmissionDate],[DischargeDate])-1)*[RoomNBoard])+(IIf(Day([DischargeDate])=DateSerial(Year([DischargeDate]),Mont
h([DischargeDate])+1,0),[RoomNBoard],Day([DischargeDate])/Day(DateSerial(Year([DischargeDate]),Month([DischargeDate])+1,0))*[RoomNBoard]))

but when I change the dates to 4/1/2007 to 4/15/07, it didn't work.
I have a field in the query named "RoomNBoard: 600".
1/10/2007 to 1/20/2007 unmodified yielded 212.903225806452, modified yielded the correct answer 193.548387096774.
4/1/2007 to 4/15/07 both methods yielded incorrect answer 300, it should be 280.
psycodad1
May 24 2007, 02:30 PM
I guess I should also mention that this will be assessed on a monthly basis. So if a client comes in 4/15/07 he is immediately assessed the prorated month for April. then he will be assess the full month on the 1st of the next month, but if he leaves then it will be prorated at the time of departure.
psycodad1
May 24 2007, 05:41 PM
Never mind I figured it out. Thanks again Jerry.
QUOTE
TotalRoomNBoard4: IIf(Day([AdmissionDate])=1,[RoomNBoard],(DateDiff("d",[AdmissionDate],DateSerial(Year([AdmissionDate]),Month([AdmissionDate])+1,0))+1
)/Day(DateSerial(Year([AdmissionDate]),Month([AdmissionDate])+1,0))*[RoomNBoard])+
((DateDiff("m",[AdmissionDate],[DischargeDate])-1)*[RoomNBoard])+(IIf(Day([DischargeDate])=DateSerial(Year([DischargeDate]),Mont
h([DischargeDate])+1,0),[RoomNBoard],Day([DischargeDate]-1)/Day(DateSerial(Year([DischargeDate]),Month([DischargeDate])+1,0))*[RoomNBoard]))

Who says you cant fix something by staring at it?
psycodad1
May 24 2007, 05:45 PM
OK now how can I round this off?
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.