IIf function Not Returning Correct Calculation

[MAFuser]

Hi,

I'm trying to use Access to calculate some invoices, this involves the sale of individual items and bulk items. I want Access to calculate whether to charge customers the bulk price, or the standard price on orders automatically based on the quantity entered.

For my "Actual Item Cost" field, I used the Iif statement:

IIf([Quantity Purchased]>=[Bulk Minimum Quantity],[Bulk Price],[Standard Price])

to try do this but the field only ever returns a value of 1.

For the sake of the example, Quantity purchased is 16, bulk minimum quantity is 15, bulk price is $40, and standard price is $50. They are in my "invoice" table and bulk minimum order quantity, bulk price, and standard price are all look-up fields to my "products" table.

I've searched as well as I can on the forum but can't find a similar situation. Any ideas?

Thanks in advance.

[pbaldy]

Where exactly do you have that? It seems okay from what I can see, so context may be important. Returning 1 makes it sound like it's returning a Boolean or something.

[MAFuser]

Thanks for the quick reply! I haven't used any booleans except to tell whether the item is in stock or not, but that isn't included on this table. Here's a picture of the table so far if that is what you mean by context (I'm quite new to this)

[pbaldy]

So that calculation is in a query? A calculated field? Can you post the db here?

[MAFuser]

Its in a table and I plan to add a form to be able to easily add to it in the future

[Micron]

For my "Actual Item Cost" field, I used the Iif statement:

Where is this "field"? When I see this, I think table. Not a good idea to calculate values in a table field. If you mean form control, report control or query field, you should mention it so we know we're dealing with a calculated control or a query field.

pbaldy asked and I don't see where you answered:

Where exactly do you have that?

IIf([Quantity Purchased]>=[Bulk Minimum Quantity],[Bulk Price],[Standard Price])

I agree - looks like it's evaluating to true. Maybe you clarify where the expression is located, and if in code, post code.

They are in my "invoice" table and bulk minimum order quantity, bulk price, and standard price are all look-up fields to my "products" table.

If you are trying to pull these values directly from the table with this expression, you cannot do this. We need more info to help solve the issue.

[pbaldy]

It may need an = in front of it.

[pbaldy]

By the way, Long Integer will not hold the decimal portion of your values.