Enter Parameter Value IIf problem 1

[feafee]

Ok, I need to make a calculated field named BillTotal that adds the order amount and the delivery charge. I also want to use the IIf function to add $2 to the BillTotal field if the city is Naples.

I've been pretty stuck on this, so any help would be appreciated. I am able to add the two fields fine, but when I try to use the IIf function, I get a little box popping up asking me to enter the parameter value.

This is what I tried for an expression:
BillTotal: IIf(City=Naples, OrderAmt+DeliveryCharge+2, OrderAmt+DeliveryCharge)

Thanks so much for being here!
feafee

 

[POKY]

hi
not sure but try this:


IIf(City=" & "'" & Naples& "'" & ", OrderAmt+DeliveryCharge+2, OrderAmt+DeliveryCharge)

 

[feafee]

Well, it didn't give me the Enter Parameter Value box, but it didn't add $2 to the BillTotal for Orders from Naples.

Any other ideas?

Thanks!

feafee

 

[cmmrfrds]

I agree with POKY that since Naples is a text literal that it must be surrounded by quotes, but in this case the quotes can be put directly in the expression.

IIf(City="Naples", OrderAmt+DeliveryCharge+2, OrderAmt+DeliveryCharge)

 

[JeremyNYC]

BillTotal: IIf(City='Naples', [OrderAmt] + [DeliveryCharge] + 2, [OrderAmt] + [DeliveryCharge])

You might consider putting nz() around each of the field names, in case there is ever a null value in either of those fields.

Also, keep in mind that this is one of those situations where you might want to store some denormalized data; your delivery charge and the $2 fee may well change at some point, and if this is just a select query, all of your invoice data will change.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[Darrylles]

Hi,

Regarding Jeremy's solution, and comments with respect to the + $2 literal.

I would assume that a delivery charge depends on location - therefore, you will have a 'Delivery' table with charges per 'Location' (Location table MUST already exist - surely you aren't using a query to enter the location / charge / supplement)!
The $2 would be a 'Supplement' field in the Delivery table.

This seems to be yet another case of programming around a 'quick n dirty' table design.
And I will be lambasted yet again (from Tek-Tips subscribers) for saying so.

Sorry - but programming for literals (for every Location) can be solved more easily and permanently via changing the model rather than working around it forever.

Seems that solutions are supplied without any interest in the overall design of the system.

Regards,

Darrylles


"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."