Wrox Programmer Forums IIF Expression in Access
 |
 Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
 Welcome to the p2p.wrox.com Forums. You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com

November 21st, 2003, 12:47 PM
 mickhumph Registered User Join Date: Nov 2003 Posts: 1 Thanks: 0 Thanked 0 Times in 0 Posts
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.

November 21st, 2003, 02:48 PM
 sal Friend of Wrox Join Date: Oct 2003 Posts: 702 Thanks: 0 Thanked 0 Times in 0 Posts

you can do a nested IIf

=IIF([Twin]IsNull,0,(IIf([Twin]=2,48*[Number of nights],38*[Number of Nights])))

Sal

 Similar Threads Thread Thread Starter Forum Replies Last Post Message> in query expression . (Error ybg1 Access 5 July 15th, 2007 05:42 AM IIf umeshtheone Beginning VB 6 3 June 21st, 2007 12:24 AM Iif statement in Access rangeview Access 7 March 28th, 2006 01:14 PM Access Query expression problem ms99sb Access 1 September 7th, 2004 06:15 AM Access to SQL (IIF) Steven SQL Server 2000 2 March 3rd, 2004 10:03 PM