If/Then Statement in Form Calculated Field

[hrseebeck]

Good day! I have a form that currently calculates a total based on the qty times the price; however, we want to add a field that lists a discounted price if applicable. What I need is the if/then statement I would need to use to have the total field look at the discount price and times that with the qty if there is something there and if not calculate using the price field. I have attached a Access 03 database if that would help. Thanks for helping.

 

[jzwp22]

Is there any criteria that determine which customer gets the discounted price versus the regular price?

If the discounted price is always given just because the item has one, then you could create a query that assigns either price first and base your product combo box on that query. You would nest the IsNull() function within and IIF() function to do that.

That query would look like this (I assume DistPrice is the discounted price).

SELECT tblProduct.ID, tblProduct.ProdNo, tblProduct.ProdName, tblProduct.Description, tblProduct.Size, tblProduct.UOM, IIF(isnull(DistPrice),Price,DistPrice) as ItemPrice
FROM tblProduct;

 

[jzwp22]

BTW, a quick look at your customer table indicates that you have a structural issue that should be addressed. You essentially repeat a number of fields depending on whether the address applies to the invoice, delivery or order. Since a company can have 3 of these addresses, it is describing a one(customer)-to-many(addresses) relationship which should be handled with a separate table

tblCustomerAddresses
-pkCustomerAddID primary key, autonumber
-fkCustomerID foreign key to tblCustomers
-fkActivityTypeID foreign key to tblActivityTypes (use this field to identify the type of activity: order, invoice or delivery)
-Addr1
-Addr2
-State
-City
etc.


tblActivityTypes (3 records: order, invoice, delivery)
-pkActivityTypeID primary key, autonumber
-txtActivityType


Since you have many contact people at the customer which also depend on the activity (order, delivery, invoice), that would follow the same approach (i.e. a separate but related table).

tblCustomerContacts
-pkCustomerContactID primary key, autonumber
-fkActivityTypeID foreign key to tblActivityTypes
-FName
-LName

The above structure assumes that the contact person is not necessarily associated with a particular customer location/address (your actual application may be different).

Also, there should be no order or quote number reference fields in tblProducts. The fields in tblProducts should only be relevant to the product.

 

[hrseebeck]

Thank you for the help. For the query, would I create a new field called total to put the if statement? I want to show the list price on the form but if there is a discounted price that needs to show as well and the total needs to be the qty times the price or discounted price if there is one. Would I redo the form using the query as the record source?

 

[jzwp22]

The query I showed would be used to supply data for the combo box in your quote subform. You would not include a total in that query. You would use the textbox control that you already have in your subform for the total. You also should not have a total field in the quoteprod table since calculated values are generally not store. You should however have a price field in that table since prices can change over time and you do not want an old order having a new price. To handle this in the quote product subform, you have to use an the after update event of the product combo box to push the price to the price textbox control. I have taken care of that in the attached database.

In looking at your database further, I see you used lookup fields in your tables. Although Access has this capability, doing so does causes several problems as detailed on this site. I corrected those and reworked your relationships so that all relationships use the autonumber primary key and the corresponding foreign key fields in the related tables.

You also based your main quote form on a query which also caused some issues, so I changed that so that the form is based only on the quote table.

I reworked you subform since the lookup caused several issues with that.

I also added the address table I mentioned earlier. I'll let you take care of the contacts since I'm not sure how you handle those in your business process.