Billing first 2 hrs at one rate

[wnicole]

I have a database where I am trying to set it up where I bill the first 2hrs one rate and the remaining hours another rate.
Is there a way to automatically calculate it?



Example:
Advance Notice Rate:
Total Hours: 5
Rate: $140 first 2 hrs
Rate: $55 for remaining hours

Short Notice Rate:
Total Hours: 6
Rate: $150 first 2 hrs
Rate: $55 for remaining hours.

...and so on

When I chose my rate, type in my total hours I want it to automatically calculate it based on my rate system.
My question is should I set up a "Rate" table or is this something I do in the query? At a lost...any help appreciated.

[June7]

Try:

IIf([TotHours] > 2, ([TotHours] - 2) * 55 + 2 * 140, [TotalHours] * 140)

[wnicole]

THe calculation works perfect. However, I'm having a problem applying it to the work code. Not sure from what angle.

If work code is Advanced Notice Rate this this apply: IIf([Billable Hours]>2,([Billable Hours]-2)*55+2*140,[Billable Hours]*140)
If work code is Short Notice Rate this apply: IIf([Billable Hours]>2,([Billable Hours]-2)*55+2*150,[Billable Hours]*150)

So some how I need it to talk to the field "Work Code." Depending on which is select from the drop down combo box then the code you gave apply.

Can you help?
BTW Thanks so much!

[June7]

Another IIf

IIf([Billable Hours]>2,([Billable Hours]-2)*55+2*IIf([Work Code]="Short Notice",150,140),[Billable Hours]*IIf([Work Code]="Short Notice",150,140))

[wnicole]

Ok I get it...the only problem now is the remaining hours rate can change...I dont see how I can adjust the code. I was wondering why total wasnt matching. I only have 5 different rates below. Can you show me how to adjust the code to fit when the "remaining hours rate" changes. See below the five rates. Appreciate your help


Advance Notice Rate:
Total Hours: 5
Rate: $140 first 2 hrs
Rate: $55 for remaining hours

Short Notice Rate:
Total Hours: 6
Rate: $150 first 2 hrs
Rate: $55 for remaining hours.

Emergency Rate:
Rate: 170 first 2hrs
Rate: 65.50 remaining hours

Weekend/Holiday Advance
Rate: 185 first 2 hrs
Rate: 65.00 remaining hours

Weekend/Holiday Short
Rate: 225 first 2 hrs
Rate: 65 Remaining

[June7]

That's getting too complicated. Options:

1. A table of work codes and the rates. Join that table to the other table. The expression will then reference the rate fields.

IIf([Billable Hours]>2,([Billable Hours]-2)*[Rate1]+2*[Rate2],[Billable Hours]*[Rate2])

2. Use VBA to write a custom function to do the calcs.

[wnicole]

Just a little confused now...I made a table of Rate
Rate Name, Amount, Ext Amt
Meaning: "Advance Rate", 140, 55

However when putting it in query I dont see how to join it?
IIf([Billable Hours]>2,([Billable Hours]-2)*[Adv Rate]+2*[Ext Amt],[Billable Hours]*[?])

[June7]

Join on the Work Code and Rate Name fields.

Then expression:

IIf([Billable Hours]>2, ([Billable Hours]-2)*[Ext Amt]+2*[Amount], [Billable Hours]*[Amount])

[wnicole]

I noticed the rate name is not in the code "advanced rate"

Advanced rate is the first 2hr
Ext rate is for remaining hours.

Does the code u sent reflect that? Am I reading correctly?

[June7]

What code 'advanced rate'? What does that mean?

Did you join the tables in query?

Don't need the work code or rate name in the expression, just reference the fields.

I used the field names you showed in post. Use whatever the actual field names are in the table.