Displaying a calculation derived from two fields on a form

[leaners]

I have 2 tables

Booking
BookingID (Primary Key)
StartDate
EndDate
Rooomno (Foreign Key)
Room
Roomno (Primary Key)
CostPerNight

I made a form using the booking table. I need to display the total cost of the booking in a textbox on the form:
=(Nz([EndDate])-Nz([StartDate]))*Nz([CostPerNight])

I get the #Name? error and I think it is because the CostPerNight field is not displayed on the form. I dont want to use a subform and I dont know any other way to display fields from different tables on one form.

I dont want to add a calculated field called TotalCost to my booking table. Can you please suggest another way to accomplish my task?

 

[KenReay]

Why not put the costpernight on the form as a text box, and set its visible property to false?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[leaners]

I have no idea how to do that.
To add a field I use field list.
But field list only displays the fields from the booking table.

Could you show me how to add the CostPerNight field?

 

[KenReay]

Hi

Make a query joining the two tables you mention (on RoomNo), base you form on the query rather than the booking table

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[leaners]

thanks it works now