Calculating per diem with IF/THEN

[pablotx]

Have something I can't quite figure out.

We have a convulated system for our per diems. If a person leaves before 7 am they get paid for breakfast. If they return prior to 7 pm, they don't get paid for dinner. There are a few more conditions, but you get the idea. I have [Begin Time] and [End Time]. How should I design table and/or form to calculate Meal portion of per diem.

Breakfast at 8 am is $8
Lunch at noon is $11
Dinner at 7 pm is $17

Any help would be great. I have been trying to do a 'lost focus' event procedure, but haven't had much luck.

Thanks

 

[Rickster57]

Create a query with the following fields based on your table. Make sure you set your [Begin Time] and [End Time] fields to Date/Time and format to Long in your table. Also, set your form to this query.

[Begin time]
[End time]
BreakfastbeginTime: Format("7:00","Long Time")
DinnerEndTime: Format("7:00 PM","Long Time")
Lunch: 11
Breakfast: IIf([Begin time]<[BreakfastBeginTime],"8","0")
Dinner: IIf([End time]>[DinnerEndTime],"17","0")
PerDiem: [Lunch]+[Breakfast]+[Dinner]

This assumes everyone gets lunch paid for.

Hope this helps

 

[pablotx]

Thanks for the reply! Unfortunately, lunch is not automatically covered. So I would have to restructure your example a little.

Do I need to have a query for this or is there a way to do this with event procedures?

Also, there is another variable I didn't mention earlier.

We have discussed the Begin and End Times.

We also have Begin Date and End Date. If the trip does not include an overnight stay, per diem for meals is not paid at all. So there would have to be another if statement in there somewhere.

Thanks. I'd appreciate a little more help.

 

[Rickster57]

I would not use event procedures for this.

Set up a similar formula for Lunch:


As far as [Begin Date] and [End Date]..if these are not equal then the trip was overnight and gets perdiem.


PerDiem: iif([Begin Date]<>[End Date,[Lunch]+[Breakfast]+[Dinner],0)

This states that if the Begin Date does not equal the End Date then trip was overnight, PerDiem gets paid...otherwise it gets assigned the value of zero.

 

[pablotx]

Thanks again for all of your help. It is starting to take shape.

I have run into another issue. What if it is a multiple day trip?

Such as Date of Departure = 01/16/06 Time of Departure 07:30 AM

Date of Return = 01/19/06 Time of Return 6:30 PM

How should I set that up?

All of your help has been tremendous and is saving me loads of time.

 

[pablotx]

One more issue.

The PerDiem: [Lunch] + [Breakfast] + [Dinner] is actually giving me a result of 1108 and values like that. I need it to sum the totals. I appreciate your patience as I am definitely over my head with formulas in the ACCESS field.

Thanks

 

[Rickster57]

Remove the quotes around the numbers.

"17" should just be 17 and so on. Sorry about that.

 

[CraigDolphin]

Hi. I had a crack at this problem since I wanted to pay back some of the help I've received on this board and in order to expand my own horizons.

I think I have the basics for addressing the multi-day issue but I'm having trouble with the logic test for calculating the per-diem on the last day of the trip.

Can someone look at the code below and tell me why it either calculates a whole day's worth of per diem or no per diem at all?

I am using the code in the On_Current event of a form bound to a table of data with fields Name, StartDate, EndDate,StartTime, and EndTime. The form has an unbound text box called Per_Diem which the calculation results are returned to.

Select Case Me!EndTime
Case Is < "7:00:00 AM":
Dinner = Dinner * 0
Lunch = Lunch * 0
Brekkie = Brekkie * 0
MsgBox "No 2nd day money", vbInformation
Case Is < "12:00:00 PM":
Dinner = Dinner * 0
Lunch = Lunch * 0
MsgBox " No 2nd day dinner or lunch", vbInformation
Case Is < "7:00:00 PM":
Dinner = Dinner * 0
MsgBox "No 2nd day dinner", vbInformation
End Select

The odd thing is that an equivalent Select statement for calculating the first day's per-diem works perfectly!

Any ideas?

 

[CraigDolphin]

An ugly solution

I don't know if Pablotx is still working on this but here's a db that calculates the perdiem to an unbound control box on a form bound to the main table of dates/times etc.

This does not automatically include lunch and works across multiple days.

The code is too repetitious and would definitely benefit from some pruning by an experienced programmer. But it works and I've learned some new things along the way.

Just delete the MsgBox lines as required.

And I fixed the above problem by replacing the quotation marks around the times with # instead.

Cheers,
Craig