Total cost calculation help please!

[Guest]

I have designed a basic booking database for a hotel with details on
customers and their stay.
I am stuck though at how to work out the guests total cost of their stay.
I need to work this out through the fields of:

price per night of a room (£56.00, £70.00 etc)
length of stay (4,5,6,7 days etc)
Tariff (Two chioces-BBEM adds £8 per night onto the cost, BB does not)

And

Total Cost ((price per night*length of stay)+(tariff*length of stay))

To enable the above formula to work, i need the query to recognise when BBEM
has been typed and maybe use an IF function like in Excel to substitute in
the £8 to enable calculation.

The main point of this question is to know how the query would be disigned
to enable me to work out the total cost.

Any help would be greatly appreciated.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

For Length of Stay use the DateDiff() function:

LenOfStay: DateDiff("d",Start,End)

Use the IIf() function like this to get the Total Cost:

((PricePerNight*LenOfStay)+(IIf(tariff="BBEM",8,0)*LenOfStay))

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmQ9mIechKqOuFEgEQITLwCfcSd1U2/T12PBPDTA4eirfYhHzd4An0R6
QIVLn/A5sQ6gbehsrHRBA5Dc
=OWRU
-----END PGP SIGNATURE-----

 

[Guest]

What field titles do i use and how do i enter whether the tariff is BBEM or
not? Where would this query be used in the database?

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Field titles? Do you mean column names? Make up some that fit your
data.

I assumed that your example was using the column names of your tables.
Tariff was mentioned in your formula; therefore, in my IIf() function I
used tariff="BBEM". Look up the definition of IIf() function in the
Access VBA Help. Basic IIF() syntax is:

IIf(<True/False evaluation>, <true expression>, <false expression>)

I didn't post an example query. I just posted the formula for the
Length of Stay (LenOfStay) column and how that formula was to be used in
the Total Cost formula.

Formulas, again (these go in the Fields row of a QueryDef):

LenOfStay: DateDiff("d",Start,End)
Start and End are room occupancy Start and End dates, respectively.

Total Cost:
([PricePerNight]*[LenOfStay])+(IIf([Tariff]="BBEM",8,0)*[LenOfStay]))
Use LenOfStay in the TotalCost formula. The IIf() function determines
if the tarrif is BBEM; if yes, then use 8 as the multiplier of
LenOfStay; otherwise, use 0.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmTCCIechKqOuFEgEQITAgCg9zkaU+FTaRRoEVJHks3hsXNSIUYAniWM
4yVotXVpjpskfXurVj6BjRTb
=EBwA
-----END PGP SIGNATURE-----