none
IIf function Not Returning Correct Calculation RRS feed

  • Question

  • 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.
    Friday, August 14, 2015 12:04 AM

Answers

  • .......bulk price, and standard price are all look-up fields to my "products" table.

    A 'lookup field', i.e. a column whose data type has been set by the lookup field wizard, does not have the value you see in the table.  Instead its value is an arbitrary long integer number which references the primary key of the referenced table, usually an autonumber.  The values you see are the values from a non-key column in the row in the referenced table to which the row in the referencing table maps via the keys.  You see these by virtue of the column's DisplayControl property being a combo box whose BoundColumn is a hidden first column.  The same will be true in a form in which a combo box is bound to the foreign key column.

    On the subject of 'lookup field' in general you might care to take a look at the following link to see why they should be avoided:

    http://www.mvps.org/access/lookupfields.htm

    However, the important point here is that you should not be *referencing* the non-key columns in the Products table at all by means of a foreign key in the referencing Invoices table.  The prices in an invoice are functionally determined by its key, not by the key of the referenced Products table.  The price should be *assigned* to a column in the Invoice table when a product is selected.  You'll find an example in InvoicePDF.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file code in the AfterUpdate event procedure of the ProductID control in the invoice details subform within the main invoice form assigns the current unit price of a product and the current VAT rate applicable to it to columns in the current row in the InvoiceDetails table as follows:

    Private Sub ProductID_AfterUpdate()

        Dim ctrl As Control
        
        Set ctrl = Me.ActiveControl
        
        Me.UnitPrice = ctrl.Column(1)
        Me.TaxRate = ctrl.Column(2)
        
    End Sub


    Ken Sheridan, Stafford, England



    Friday, August 14, 2015 6:04 PM

All replies

  • Hi. Are you using that expression in a query or form/report? Can you please post the SQL statement for your query or your form's record source? Thanks.
    • Edited by .theDBguy Friday, August 14, 2015 1:21 AM
    Friday, August 14, 2015 1:21 AM
  • Things to check:

    1. Make sure that all your fields involved are defined as Number. 
    2. Try showing each field involved in your formula as individual columns in your query just following your calculation column to see what the values of each are.


    I will assume that your column is defined as follows:

    [Quantity Purchased] * IIF([Quantity Purchased]>=[Bulk Minimum Quantity],[Bulk Price],[Standard Price])  As [Actual Item Cost]


    Friday, August 14, 2015 1:30 AM
  • bulk minimum order quantity, bulk price, and standard price are all look-up fields to my "products" table.
    Is it possible he 1 represents the AutoNumber of the record you want returned rather than the value you want to display? If so, since this is a lookup field then you may have to modify your query to include the Table that contains the value and add that Field from the Lookup Table to your Query.

    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Friday, August 14, 2015 3:49 AM
  • In my limited experience, I find that lookup fields sometimes cloud the issue, and my calculations work better thru links to actual tables. You might check the statements (queries) used to look up values; sometimes names change that don't get propagated to them.

    peter n roth - http://PNR1.com, Maybe some useful stuff

    Friday, August 14, 2015 2:44 PM
  • .......bulk price, and standard price are all look-up fields to my "products" table.

    A 'lookup field', i.e. a column whose data type has been set by the lookup field wizard, does not have the value you see in the table.  Instead its value is an arbitrary long integer number which references the primary key of the referenced table, usually an autonumber.  The values you see are the values from a non-key column in the row in the referenced table to which the row in the referencing table maps via the keys.  You see these by virtue of the column's DisplayControl property being a combo box whose BoundColumn is a hidden first column.  The same will be true in a form in which a combo box is bound to the foreign key column.

    On the subject of 'lookup field' in general you might care to take a look at the following link to see why they should be avoided:

    http://www.mvps.org/access/lookupfields.htm

    However, the important point here is that you should not be *referencing* the non-key columns in the Products table at all by means of a foreign key in the referencing Invoices table.  The prices in an invoice are functionally determined by its key, not by the key of the referenced Products table.  The price should be *assigned* to a column in the Invoice table when a product is selected.  You'll find an example in InvoicePDF.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file code in the AfterUpdate event procedure of the ProductID control in the invoice details subform within the main invoice form assigns the current unit price of a product and the current VAT rate applicable to it to columns in the current row in the InvoiceDetails table as follows:

    Private Sub ProductID_AfterUpdate()

        Dim ctrl As Control
        
        Set ctrl = Me.ActiveControl
        
        Me.UnitPrice = ctrl.Column(1)
        Me.TaxRate = ctrl.Column(2)
        
    End Sub


    Ken Sheridan, Stafford, England



    Friday, August 14, 2015 6:04 PM