 |
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|

September 30th, 2005, 11:08 AM
|
Friend of Wrox
|
|
Join Date: Sep 2005
Posts: 106
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Nested Query with an iif statment
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
|

October 15th, 2005, 02:58 PM
|
Friend of Wrox
|
|
Join Date: Jul 2005
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
What is the other IIF?
Boyd
"Hi Tech Coach"
Access Based Accounting/Business Solutions developer.
http://www.officeprogramming.com
|

October 16th, 2005, 04:45 PM
|
Authorized User
|
|
Join Date: Oct 2005
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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.
|

October 17th, 2005, 10:48 AM
|
Friend of Wrox
|
|
Join Date: Sep 2005
Posts: 106
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|

October 17th, 2005, 10:49 AM
|
Friend of Wrox
|
|
Join Date: Sep 2005
Posts: 106
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Sorry, mis type
Field (3)Divide: Cost: Sum([OL_DMD_COST]*([OL_QTY_ORD]-[OL_QTY_CAN_TD]))
|

October 17th, 2005, 12:45 PM
|
Authorized User
|
|
Join Date: Oct 2005
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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.
|

October 17th, 2005, 03:35 PM
|
Friend of Wrox
|
|
Join Date: Sep 2005
Posts: 106
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
I'm just starting out with VBA. is there a way you can exsplain this to me?
Quote:
quote:Originally posted by Figgis
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.
|
|

October 17th, 2005, 04:40 PM
|
Authorized User
|
|
Join Date: Oct 2005
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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.
|
|
 |