Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old September 30th, 2005, 11:08 AM
Friend of Wrox
 
Join Date: Sep 2005
Location: , , USA.
Posts: 106
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to Corey
Default 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


Reply With Quote
  #2 (permalink)  
Old October 15th, 2005, 02:58 PM
Friend of Wrox
 
Join Date: Jul 2005
Location: Oklahoma City, OK, USA.
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts
Default

What is the other IIF?

Boyd
"Hi Tech Coach"
Access Based Accounting/Business Solutions developer.
http://www.officeprogramming.com
Reply With Quote
  #3 (permalink)  
Old October 16th, 2005, 04:45 PM
Authorized User
 
Join Date: Oct 2005
Location: South Ockendon, Essex, United Kingdom.
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Figgis
Default

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.
Reply With Quote
  #4 (permalink)  
Old October 17th, 2005, 10:48 AM
Friend of Wrox
 
Join Date: Sep 2005
Location: , , USA.
Posts: 106
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to Corey
Default



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


Reply With Quote
  #5 (permalink)  
Old October 17th, 2005, 10:49 AM
Friend of Wrox
 
Join Date: Sep 2005
Location: , , USA.
Posts: 106
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to Corey
Default

Sorry, mis type

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

Reply With Quote
  #6 (permalink)  
Old October 17th, 2005, 12:45 PM
Authorized User
 
Join Date: Oct 2005
Location: South Ockendon, Essex, United Kingdom.
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Figgis
Default

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.
Reply With Quote
  #7 (permalink)  
Old October 17th, 2005, 03:35 PM
Friend of Wrox
 
Join Date: Sep 2005
Location: , , USA.
Posts: 106
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to Corey
Default

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.

Reply With Quote
  #8 (permalink)  
Old October 17th, 2005, 04:40 PM
Authorized User
 
Join Date: Oct 2005
Location: South Ockendon, Essex, United Kingdom.
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Figgis
Default

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.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 01:16 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.