Full Version: Iif (not Working)
UtterAccess Forums > Microsoft® Access > Access Forms
Drwyer
=IIf([podTaxExempt]=-1,Sum([[podQuantity]*[podUnitCost]),Sum(([podQuantity]*[podUnitCost])+([podQuant
ity]*[podUnitCost]*[Forms]![frmEnterUpdatePurchaseOrders]![piMaterialTax])))
i, Can anyone offer assistance in getting the above IIF statement to function? As you can see in the expression, I want to add all of the extended amounts of tax exempt and non-exempt items.
Bob G
what happens with it now? do you get an error message?
where are you using this formula ??
Drwyer
Hi Bob,
Yes, I get "#Error". The Control the formula is in is located in the footer of the sub form. The "#Error" message displays on the main form control. The main form control is set to display the calculated control i the sub form.
Bob G
what i would suggest for troubleshooting purposes is to make the formula basic at first and then add pieces and see when the error happens.
It may be the way you are calling the form. Try this link to see if it helps.
http://access.MVPs.org/access/forms/frm0031.htm
to start i would try this. then add each pieice. I may not have counted the () correctly so please check
CODE
=IIf([podTaxExempt]=-1,Sum([[podQuantity]*[podUnitCost]),Sum(([podQuantity]*[podUnitCost]))
J.D.
Does the calculated control in the subform return Error or just the reference to it on the main form?
missinglinq
Then perhaps you should show the code you're using to do this, since that is where people frequently come a cropper in this sort of thing! OF course, a simple way to test this is to simply assign a value to the Control in the Subform and see if it shows correctlyin the Main Form Control.
Linq ;0)>
Drwyer
To all,
Thanks for the collaboration. Prior to this IIF statement I had Sum (podQuantity*podUnitCost). This worked both in the sub and returned a grand total amount in the main form.
JD. When I just open the subform in non datasheet view I get the same error with this new expression. Am I referring to the YES/NO value or "-1" correctly in the expression? Although, The main reference isn't visible to the expression when I just open the subform in single form view.
Bob G
what happens if you use all of your formula BUT the part that references the form?
IIf([podTaxExempt]=-1,Sum([[podQuantity]*[podUnitCost]),Sum(([podQuantity]*[podUnitCost])+([podQuant
ity]*[podUnitCost])))
Drwyer
Same error message
J.D.
ere your problem is. This is trying to look at a single record but then as a result sum the records that meet that criteria. That isn't the way this function works.
The way to fix this is in the record source for the subform, if it's built on a query (SQL statement) open it. If it's built on a table, build a SQL statement from the table that includes all the fields.
Now add a calculated field that does your calculation (with out the sums). so it would look something like...
=IIf([podTaxExempt]=-1,[podQuantity]*[podUnitCost],([podQuantity]*[podUnitCost])+([podQuant
ity]*[podUnitCost]*[Forms]![frmEnterUpdatePurchaseOrders]![piMaterialTax]))
untested so you will probably have to clean up the syntax, but essentially get your total from each record. Give the field a name like "RecordTotal".
NOTE: you might have to add this as a hidden control on your subform to reference it in the footer.
Now you can reference this new calculation field in the subform footer and have the control source for your total be...
=Sum([RecordTotal]).
That should give you the proper result and the reference in the main form will then populate.
Hope this helps,
Bob G
did you try instead of -1 using yes or 'yes' ??
Drwyer
Yes I did.
Drwyer
This may sound like a silly question but how will i enter the information in the subform if the record source is a query?
J.D.
Take a look at my changes, I did just a couple of things.
1. The subform was based on the table. I changed it to a query and then added your IIF statement removing the sums as I wanted the total for each record not all the records.
2. I also added the CCur() function to the IIF statement to convert it to currency so that you aren't left with 6 and 8 decimal places on some records
3. Added the new field to your subform and hid it both in form view and datasheet view.
4. Change the control source of the subform footer control that used to have the IIF statement to now just do the sum of the new field "=Sum([RecordTotal])
hould be doing what you are expecting now.
Hope this helps,
Drwyer
JD,
ounds great but the recordset is not updateable. I can't add or update any records in the subform.
Drwyer
BUT what if I make the grand total control on the main form based on a query that does what you described?
J.D.
In the database I posted, the recordset is updateable. Your dropdown wasn't working but I was able to add a description, quantity and cost and when I moved off the record the total updated appropriately.
J.D.
Even in a grand total calculation becuase you are using a record level flag to change the calculation you first have to do the calculation at the record level before you can do any totalling.
Hope this helps,
Drwyer
JD,
o make sure I understand your last statement. Originally I was multiplying each row then summing those values to obtain one grand total. Now I need to extend the amounts out in the query and sum the extended amount field in the main form control. Right?
J.D.
If you are wanting to use the flag to change the total calculation then yes, you need to extend the amount in the query and sum that.
Drwyer
Yes, because some items are tax exempt and others aren't
Drwyer
JD,
couldn't get the control on the main form to display a query result. Is there a special way to do that? I then put another control on the sub form to extend the cost with or without tax (works with the IIF). I tried to sum that control in the sub form footer but crashed and burned on that also. If I remember correctly, I can't sum a calculated control because it doesn't store the data. Right?
J.D.
Save the query as a named query and do a dLookup? First thought off the top of my head although it probably won't refresh dynamically.
orrect, that is why I put the IIF statement you were using into the query of the subform, that way it's done before the form sees it, so the form can then sum it.
Drwyer
JD,
OK, I have removed the check box for tax exempt. There are others ways around it. So now I want to show the grand total including tax (if any) on the main form. I put a control on the main form that show what the project material tax is EI: 6%.
from the footer in the sub form I have " =Sum ((podQuantity*podUnitCost)+(podQuantity*podUnicCost*Forms!frmEnterUpdatePurchaseOrders!piTaxAmount))"
It won't read the main form tax amount. Any ideas?
J.D.
I see 2 things here. First "*podUnicCost" should be "podUnitCost
ext, in the example db I have I do not see "piTaxAmount" anywhere. There is a "piMaterialTax"
So I was able to do the following without any trouble...
CODE
=([podQuantity]*[podUnitCost])+([podQuantity]*[podUnitCost]*[Forms]![frmEnterUpdatePurchaseOrders]![piMaterialTax])

Now if in your db you have a piTaxAmount, the reference should work you just need to clean up the podunitcost issue.
Hope this helps,
Drwyer
Yes, that works. that is the expression I am using to extend the unit cost to include tax. But when I use "=Sum(([podQuantity]*[podUnitCost])+([podQuantity]*[podUnitCost]*[Forms]![frmEnterUpdatePurchaseOrders]![piMaterialTax]))" in the form footer to calculate the grand total I still get the error message.
J.D.
subform or main form? Because it works just fine for me in the sub form. for the main form the syntax is all wrong, thus my question? Also if you are wanting to do it at the main form for all records, it really wouldn't work like this because these calculation are only on the records showing on the form (current main record and it's related sub/child records). so if you are wanting a Grand total for everything, that is a completely different calculation.
Drwyer
Good morning,
I have =Sum(([podQuantity]*[podUnitCost])+([podQuantity]*[podUnitCost]*[Forms]![frmEnterUpdatePurchaseOrders]![piMaterialTax])) in the footer of the sub form, this is named Text32.
The grand total control in the main form is =[Forms]![frmEnterUpdatePurchaseOrders]![frmPODetailNew]![Text32]
J.D.
Try...
=[Forms]![frmEnterUpdatePurchaseOrders]![frmPODetailNew].Form![Text32]
You have to identify the sub forms "form properties" otherwise Access looks at the subform as 1 big control. To get inside it, you have to tell Access that with the .form designation.
Hope this helps,
Drwyer
Nope, still didn't work. Is the problem in the sub form?
J.D.
here is your db back. There was a problem with your subform. Not sure what as I could not get it to sum anything. even a simple sum of a column it would not do. compact and repair had no affect on it either. Very strange indeed...
< pullhair.gif <
With that said, I created a new subform and put in the control on the footer and changed the references and it all works. Use it as a template for how to do the references. Notice I also used the Nz() function because your material tax field doesn't always have a value and that can cause an error or lack of calculation. Of course my subform doesn't have your combo boxes and laid out the same but the functionality in question is there for what you need to recreate it yourself. you also had some references requerying a combo on the sub form that became problems with my subform, so be aware of that as well.
Hope this helps,
Drwyer
JD,
Thanks for the help. Strange indeed. The form worked fine until I tried the IIF. Like you I couldn't get it to do any simple calculation.
J.D.
br />eah, not sure what was going on, but creating new certainly took care of things.
good luck with your project.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.