Full Version: Room & Board \$600 per month, pro rated.
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:

Jerry Dennison
CODE

(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.
Thank you Jerry. I'll give it a shot.
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
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.