Hi Scott
If I give you a solution will you promise not to kill me?
First, you must not hard-code any rates, because next week they will be
sure to change.
Create two tables - one called Tariffs and one called TariffRates.
Tariffs has the following fields:
TariffID (autonumber, primary key)
TariffName (text, indexed no duplicates)
TariffRates has the following fields:
TariffID (long integer) } these two fields make a
MinStayDays (integer) } composite primary key
BlockRate (currency, required)
ExtraDayRate (currency)
Make a one-to-many relationship between the two TariffID fields.
Tariffs has records like this:
1 Standard
2 Loyalty
TariffRates has records like this:
1 1 $199 <null>
1 7 $1100 <null>
2 1 $160 <null>
2 7 $1000 $145
What all this means is that for TariffID 1 (Standard), guests get charged
$199 per night unless they stay 7 nights or more, in which case they get
charged $1100 for each 7 nights and one seventh of $1100 for each extra
night.
For TariffID 2 (Loyalty) they get charged $160 per night unless they stay
7 nights or more, in which case they get charged $1000 for each 7 nights
and $145 for each extra night.
Note that if the ExtraDayRate is null, it is assumed to be the BlockRate
divided by MinStayDays.
You could add extra records, say:
2 21 $2500 $120
...so a loyalty guest staying more than 21 days gets charged $2500 plus
$120 for each extra day.
You can add extra tariffs as well - for example "Bill Gates" at $1,000,000
per night.
Make a standard form/subform to maintain the tariffs and rates.
Now, you need a function like this to calculate the charge:
Function CalcCharge(TariffID As Long, Days As Integer) As Currency
Dim rs As DAO.Recordset, cExtraRate As Currency
On Error GoTo ProcErr
Set rs = CurrentDb.OpenRecordset( _
"Select top 1 * from TariffRates where TariffID=" _
& TariffID & " and MinStayDays<=" & Days _
& " order by MinStaydays desc", dbOpenForwardOnly)
With rs
If !MinStayDays <= 1 Then
CalcCharge = Days * !Blockrate
ElseIf IsNull(!ExtraDayRate) Then
CalcCharge = Round(Days * !Blockrate / !MinStayDays, 2)
Else
CalcCharge = (Days \ !MinStayDays) * !Blockrate _
+ (Days Mod !MinStayDays) * !ExtraDayRate
End If
End With
ProcEnd:
On Error Resume Next
rs.Close
Set rs = Nothing
Exit Function
ProcErr:
MsgBox Err.Description
Resume ProcEnd
End Function
All you need to do when a guest books in is select the tariff, enter the
number of days, and call the function to calculate the charge.
--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand
Scott B said:
A guest gets the closest we can get to the weekly rate divided by 7. So
at $1,100 per week for 8 days would be $1,100 + $157.14 or 1257.14. This
is the only way we have been able to figure out how to do more than week
but less than multiples of weeks. It doesn't help that we have several
daily rates as well as the weekly rates. the weekly rate can be either
$1,100 or $1,000 depending on the guest. Return guests often get the
$1,000 rate as a kind of reward for their loyalty. Now I'm going to have
to kill you since I told you a B&B innkeepers secret.
I am trying to construct the function code right now. It takes me a long
time becuase all the code I know I learned here one post at a time.
Thanks,
Scott
Mike Painter said:
A function is probably better than an IIF.
Roberts probably will not work without a WeeklyRate and a DailyRate.
It also assumes that if they stay 7 days or more they get the weekly
rate.
Do they or does an 8 day stay get charged at one weekly rate plus one
day rate??
Ronald Roberts wrote:
Function TotalCostRtn( StayWeekly as Boolean, Rate as _
Currency, StayDays as Integer) as Currency
TotalCostRtn = 0
If StayWeekly = True then
Rate = dLookup the weekly rate - for that room
TotalCostRtn = Rate * (StayDays / 7)
Else
Rate = dLookup the daily rate for that room.
TotalCostRtn = Rate * StayDays
End Function
In your Query put:
TotalCost: TotalCostRtn(WeeklyStays, RoomRate, StayLength)
----
The IIF is:
TotalCost: IIF(WeeklyStay=True, Rate * (StayDays / 7), Rate *
StayDays)
I think you will be better off using a function.
In the function you can do more error checking for bad input data.
This is air code.
Ron
Scott B wrote:
I forget to say that I currently have a check box for weekly stays
in the table that I thought I might use in this instance. It would
allow me to tabulate the number of weekly stays versus overnights, a
sometimes useful statistic.
Thanks agian,
Scott B
Greetings,
I have a table with a field [Rate] and field [Staylength]. I
calculate the amount of a guest's stay on a form by multiplying
these two fields together ( I don't store this mumber). [Rate] is
normaly an even dollar abount like $199 and [Staylength] is always
a number like 1 or 5 or 10 for the number of nights a guest is
staying. But we also have a rate of $1,000 and $1,100 per week
(seven nights). This poses a problem because I still need to know
how many nights the guest is staying. It could be 14 or 21
(multiples of 7) as well as 7. If I did not need to know the
number of nights I could just use the weekly rate times the number
of weeks, but then I do not know the number of nights which I use
in other places. So my puzzle is this (I think), is there a way to
write an IIf
statement to handle the multiple cases or do I need to use code to
manage this? The IIf statement has me stumped and I do not know a
lot about code. Maybe a case statement?
Please let me know if you need more info. I find this hard to
verbalize although I can see what I want.
Thanks,
Scott B
