Calculated result wrong in table - hotel db total price for stay

[Pola_S]

Hello everyone,

I am new to the forum and newish to Access - meaning I've only used it for a few weeks now as I'm working on a project for a class. The objective is to create a hotel database to store data like customers, rooms, bookings etc. I am currently working on my "Bookings" table where I have several foreign key fields (CustomerID, RoomID, RoomType, RoomRate, PaymentID etc.). I have created a calculated field for the length of stay based on the check-in and check-out dates and this works just fine, giving me the total number of 7 nights' stay for the example dates 10/04 to 17/04. However, I have encountered a problem calculating the total price for the whole duration of the stay which I wanted to be stored in a calculated field (TotalPayment) based on the fields Length of stay (in days, calculated correctly as mentioned above) and RoomRate.

My room rates are:
65
75
80
115
There are several rooms per rate (several rooms of the type single, several for double etc.).

The TotalPayment field expression is:
[Length of stay (days)]*[RoomRate]

But instead of getting the results:
7 * 65 = 455
7 * 75 = 525
etc.

I am getting:
7 * 65 = 7
7 * 65 = 14
7 * 80 = 21

which leads me to believe the actual expression is [Length of stay (days)] * [RoomID], as the results are clearly 7 times 1, 7 times 2 etc. (the room rates are stored in a combo box). Why is this happening? How can I make it right? I'd be VERY appreciative of your help!

Pola

PS. I've browsed several calculated-reate topics and they seem to advise not to use calculated fields in tables themselves but in queries - why is that?

[Pola_S]

omg. I just noticed the last reply on the thread I linked to and there was the reply: the bound column. I changed it to 2 and there we go, it all works now.
Anyway, I'm gonna leave this thread on as I'd still like to know why calculated fields are not recommended

[princess12]

hi i just want to ask on your check out form, is your check out date same as what the customer booked, if so what have you done to deal with customers who have late checkouts.

if they not the same, then how did your system ensure people check out on time

i have done mines but i just want to see if there are other alternatives.

thanks