mathematical function

[Excellent1]

I am working with a table in Access that contains

Tracking number, invoice number, customer number, box
number, freight charge, ship via rate. we have two
customers ABC001 and DEF002 that have special freight
charge rules. for ABC001 their freight charge is 4.50
less than the value in freight charge and for DEF002
their freight charge is 2.50 less than the value in
freight charge. How can I code a function to pass
through access and make these changes for the given
customers? thank you in advance

 

[khexcel]

Hi,
The quickest non programming way is to create an update query.
Go to queries, add your table, from the "query" option of your menu bar, select "Update query". In the "Update to" field, create your formula:

iif(yourcustomerfield = "abc0001",your_freight_charge_field - 4.5,
your_freight_charge_field-2.5)

When you run this query, your table should be updated. You can save this query and run it whenever you need.

 

[khexcel]

oops. Before you write the formula, you have to select the field you want to update then you write your formula in the "update to" field.

 

[Excellent1]

thanks for the reply, i noticed that the syntax of your first formula is a "one or the other" but this table has hundreds of customers...also, how do I select the field I want to update to.

 

[khexcel]

Sorry,
I thought there were only two customers... but you can modify the formula and it will still work
Try:

iif(yourcustomerfield = "abc0001",your_freight_charge_field - 4.5,
iif( yourcustomerfield = "def0002",your_freight_charge_field-2.5,
your_freight_charge_field)

from the query design screen of Access, select only the field you want to update after you add
your table (the same procedure has you would normally select a field in a normal query).
Then enter the formula in the update to section.

Remember to save a copy of your database before you experiment on new ideas.

 

[Norie]

Do you have a table with each customer and their freight charge value reduction?

Is there anyway to recognise which customers get which reduction?

 

[Excellent1]

I guess I could set up another table with those two customers and their freight discounts.....what would I do then?

 

[SydneyGeek]

How's about creating a Freight Discount field (formatted as Currency) in the Customers table? Default is zero, and you can enter whatever you like for any customers.
Then the Freight charge in the invoice calc is [Freight Charge]-[Freight Discount], no matter which client you use.

Denis

 

[Excellent1]

can anyone tell me why I get the INVALID SYNTAX error from the following code:

[FreightCharge]iif(CustomerNumber = "BAT002",FreightCharge - 4.5, 
iif(CustomerNumber = "ODO001",FreightCharge-2.5, 
FreightCharge)

It highlights the first IIF

 

[khexcel]

Since you have two iif, you need two closing parenthesis at the end.