Wrox Programmer Forums Nested Query with an iif statment
 |
 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
 Corey 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
 AccessMaster 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"
http://www.officeprogramming.com

October 16th, 2005, 04:45 PM
 Figgis 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
 Corey 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
 Corey 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
 Figgis 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
 Corey 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
 Figgis 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.

 Similar Threads Thread Thread Starter Forum Replies Last Post Mulitiple/Nested IIF statements rohit_ghosh Access VBA 3 June 1st, 2007 10:44 AM Nested IIF rohit_ghosh Access 5 May 4th, 2007 09:29 AM Help with IIF Statment Corey Access 2 December 14th, 2006 11:10 AM Nested IIF Statement Help Needed Corey Access 4 November 17th, 2005 10:19 AM Nested Query with an iif statment part 2 Corey Access 1 October 17th, 2005 12:47 PM