If, Then Statements

[tsukeepr]

I am trying to build a query that will take a large amount of data that is sent to me so I can be able to allocate charges to users of a particular thing. The only problem is it is a phone line and for each different way of connecting, 800 number, dial up, VPN, there is a different discount given to us.

My question is how I can write an If statement that allows me to say that if a field is a, b, c, multiply it by .30, and if a feild is d and e, multiply it by .17. Is there any way I can do this?

thank you

 

[Pat Hartman]

Use the IIf() function.

IIf(Fld1 In("A","B","C"), Qty * .30, Qty * .17) As FinalCost

 

[Sohaila Taravati]

You can do this in your query:
Charges:IIf([YourField]="a","a")*.30 & IIf([YourField]="d","d")*.17
and so on.

 

[tsukeepr]

Question to sohaila

Sohaila,
I tried it your way and when I saved it, it told me that the function had the wrong number of arguments.

Could you help?

 

[The_Doc_Man]

Look up the SWITCH function, which would perhaps help you if you have more than two or three possible rates to consider.

The problem with IIF is the number of commas and parentheses involved when you have to do a bunch of nested IIFs.

You can do this with a public function. But if you are not comfortable with VBA, this would be the wrong approach.

Or... a table lookup in which the connect method is one field and the factor is another. Then join it to the incoming data on the connect method field. The cost is available to you in the joined query as just another field from the list of fields in the design grid.

 

[Sohaila Taravati]

I just tested it again and it worked for me. Make sure that you repeat the letter "a" twice.
IIf([YourField]="a","a")*.30

This if statement is saying that if your field is equal to "a" than put "a", and if "a" is what you want then multiply it by .30. The & sign means this is nested and you can repeat the same procedure as many as you want. I hope that Helps.

These nested IIf's will work as long as you have only few of them and you don't have to worry about changing them all the time. Hard coding will create more work for you in the future.
Let me know if this works

 

[tsukeepr]

I am now having a problem, b/c I need to have these numbers multipled by another field. What i mean is, when I asked for them to be multipled by .30 or.17, I need the database to take out say dialup from the field billing element, and if it is dialup then multiply .30 by another field like cost. I am trying to figure out a discount. does this make since?

thank you very much for your help!

 

[Sohaila Taravati]

I am confused Can you give me an example of your Billing Element and how the data looks like. Is your dialup another field? Depending on your situation, maybe you need to do a make table or update query. Let me know

 

[tsukeepr]

I have a table that lists
user act# billing_element timeOnSystem Cost


The billing element is either one of four choices, each choice is discounted a different amount. so it looks like this

billing_element cost
choice one $$$$
choice two $$$$
choice three $$$$
choice four $$$$$

I need to be able to say if billing_element is choice one multiply it by .30 to the cost. That way I can get the discount and later subtract the discount from the cost and get the cost after discount.

thank you

 

[Pat Hartman]

Sohaila,
1. the IIf() function takes 3 arguments. Your examples supply only 2.

IIf([YourField]="a","a")*.30

The false part is missing.

2. I don't think that you'll have much success multiplying "a" by .30

3.

The & sign means this is nested and you can repeat the same procedure as many as you want.

That is NOT how to next IIf()'s