Hello again,

I currently have a formula that looks like this: Qty:
Sum(IIf([OL_PRICE]*([OL_QTY_ORD]-[OL_QTY_CAN_TD])=0,0,([OL_QTY_ORD]-[OL_QTY_CAN_TD])))

I have another field titled: Description: P_DESC

I would like to add to the Qty formula that if the letters “*SS*” located it the P_DESC then /2

Can anyone help me learn how to combing two iif statements?

Thanks

Corey

What is the other IIF?

Boyd
"Hi Tech Coach"
Access Based Accounting/Business Solutions developer.
http://www.officeprogramming.com

If you wish to nest if statements you would need to decide what the primary question would be. Using your statement it could look something like the following:-

Sum(IIf([OL_PRICE]*([OL_QTY_ORD]-[OL_QTY_CAN_TD])=0,0,IIf([P_DESC]="*SS*",([OL_PRICE]/2),([OL_QTY_ORD]-[OL_QTY_CAN_TD]))))

It sort of runs like "Ask the first question, if the answer is no ask the second question, if the answer is no ask the third question. When you;ve asked all the questions if the answer is still no have a default value. The statement would stop at any stage that the answer was yes.

I'm not sure if that makes sence I'm more used to writing if statements in Excel and they can get absolutley huge.

The formula you provide worked. Thank you.

But the problem I’m having now is with the formula below, which is very similar to the formula you provide.
Basically what I’m trying to do is if the name Diana Grover shows up for a buyer I need to divide 1: the qty 2: Retail 3: Cost

Field (1)Divide: Qty: Sum(IIf([OL_PRICE]*([OL_QTY_ORD]-[OL_QTY_CAN_TD])=0,0,([OL_QTY_ORD]-[OL_QTY_CAN_TD])))

Field (2)Divide: Retail: Sum(([OL_PRICE]*([OL_QTY_ORD]-[OL_QTY_CAN_TD])))

Field (2)Divide: Cost: Sum([OL_DMD_COST]*([OL_QTY_ORD]-[OL_QTY_CAN_TD]))

Thank you very much for your help, I’ve been working on these fixes for a number of months

Sorry, mis type

Field (3)Divide: Cost: Sum([OL_DMD_COST]*([OL_QTY_ORD]-[OL_QTY_CAN_TD]))

I think in this instance I would write a new event which handled the actual calulations which could then be called if your criterea was met.

I'm only just geting into VBA so this might not work. I used to program with small talk and that is totally different. I would try something like:-

Private Sub Discount()

Dim Qty As Integer
Dim Retail As Ineteger
Dim Cost As Integer

 Qty = (Me!.Qty.Value/(Sum(IIf([OL_PRICE]*([OL_QTY_ORD]-[OL_QTY_CAN_TD])=0,0,([OL_QTY_ORD]-[OL_QTY_CAN_TD])))))

 Retail =(Me!.Retail.Value/(Sum(([OL_PRICE]*([OL_QTY_ORD]-[OL_QTY_CAN_TD]))))

 Cost =(Me!.Cost.Value/(Sum([OL_DMD_COST]*([OL_QTY_ORD]-[OL_QTY_CAN_TD]))))

 Me!.Field_1.Value= Qty

 Me!.Field_2.Value= Retail

 Me!.Fiels_3.Value= Cost

End Sub


Then use your IIf([Buyer]="Diana Grover",Call Discount,(you need to choose what to do if the buyer isn't Diana Grover))

As I say I'm not used to VBA yet so some of the other guys on here might be able to phrase this a bit better.

The other thing that springs to mind is that you are being very presise in selecting one buyer to apply this condition to. Would it not be better to have the option to apply this to other people and that way save yourself a bit of work if it does become neccesary. Just a thought.

I'm just starting out with VBA. is there a way you can exsplain this to me?

Well it sort of goes like this. When the wife asks me to make a cup of tea there is a whole series of things I have to do. I fill the kettle with tap water, I get the cups ready, When the kettles boiled I poor the water etc. It would be a bit long winded for her to tell me how to do all that each time she wanted a drink so she just say's "get off ya butt and make a drink".

It works the same way. You have three reasonably complex calculations that you want to be carried out when a certain condition applies. i.e. When the buyer is Diana Grover you want to apply the calculations.

If you set the calculations up as a subroutine then you can call upon it when you want without having to rewrite it for each condition where you want it to apply. All you need to do is write a simple IIf statement to test the condition and if it meets your criterea then it runs the subroutine.

What I meant by the last part is that by hard coding a persons name into an application you make it very inflexible and store yourself up problems for later. Say six months down the road you want to apply the same conditions to Jon Williams. If you write it the way you intend to your going to have to break into your code and make the changes or write a whole new set of code. What I would do is set up a table with the list of names and a check box for when you wish to apply your calculations. I've done a similar thing with the database I'm curently working on. I have a claim form where the confirmation team can choose to have an investigation carried out. Then the stock control team will only see the records that have been requested for an investigation.

As I said I'm only just begining with Access and VBA myself and by no means am I an expert. What I have suggested is only the method I would use.