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

 

[Excellent1]

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

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

I just got the same error

 

[Excellent1]

I changed it to this

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

and wasn't prompted with an error.......but, the query doesn't appear to work...I get a type conversion failure for all records

 

[SydneyGeek]

Instead of...

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

Try...

Freight:iif(CustomerNumber = "BAT002",FreightCharge - 4.5, 
iif(CustomerNumber = "ODO001",FreightCharge-2.5, 
FreightCharge))

You need the colon instead of square brackets before the first IIF.
Denis

 

[SydneyGeek]

Also, if CustomerNumber truly IS a number, you can't filter it using text. You will have to filter on the field that contains the values "BAT0002", etc.

Denis

 

[Excellent1]

I used

Freight:iif(CustomerNumber = "BAT002",FreightCharge - 4.5, 
iif(CustomerNumber = "ODO001",FreightCharge-2.5, 
FreightCharge))

and I got an error saying there is an invalid .(dot) ! or invalid parentheses.

CustomerNumber is actually a text field "BAT002" is text

 

[khexcel]

Regarding your type conversion error, is the frieght charge field a number or text?

 

[Excellent1]

its a number

 

[SydneyGeek]

This is weird, but try changing

Freight:iif(CustomerNumber = "BAT002",FreightCharge - 4.5, 
iif(CustomerNumber = "ODO001",FreightCharge-2.5, 
FreightCharge))

to

Freight:iif(CustomerNumber = "BAT002",FreightCharge - 4.5, 
iif(CustomerNumber = "ODO001",FreightCharge - 2.5, 
FreightCharge))

Access may not be seeing the second expression correctly because no gaps. Otherwise, back to my earlier comment. CustomerNo is a NUMBER, you say. Why are you using TEXT to filter it? Access will spit the dummy.

Denis

 

[Excellent1]

freightcharge is a number, not customernumber

and I still get the same error after changing the spacing, it highlights the first (

 

[khexcel]

In Access control source, you have to surround the fields with []. Try This:

Freight:iif([customerNumber]= "BAT002",[freightCharge] - 4.5,
iif([CustomerNumber] = "ODO001",[FreightCharge]-2.5,
[FreightCharge]))

To my previous posting, if you are placing this formula in the "update to"
section of an update query do not include "Freight:" before the formula.
First select the field you want to update, in this example Freightcharge
then try this formula:

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