IIF Expression in Access
I have constructed a simple data base to handle guest house bookings. I want to insert an expression which will calculate the price of staying in a room which involves certain variables. The room can accommodate: no one in it, or 1 person, or 2 people. The price for 1 person is £38 and the price for 2 people is £48.
The fields that supply the information are:[Number of Nights] and for the twin room [Twin]. The prices are inserted in the expression depending on the room.
The expression I have used for this twin room is as follows:
=IIf([Twin]=2,48*[Number of nights],38*[Number of Nights])
this returns a Price of £48 times the number of nights if two people use the room, or a Price of £38 times the number of nights if one person uses the room. This is fine, BUT it also returns
£38 if no one has booked the room at all.
How can I modify this IIF expression to return a Zero or blank field if no one has booked the room that night?
The problem seems to be that an IIf expression only returns an alternative. It cannot seem to cope with three options, one of which is blank.
|
