Help with Sum and iif statements

[jamez141]

Hi everyone!
First post here, just looking for some help with a project im working on.

I have got a form with a sub form in it which works together to produce and invoice. A discount has to be applied to only one product (if the "type" field is carpet, then a discount has to be applied. (the discount comes from a radiobutton field on the main form.

here is my code...

=Sum(IIf([Type]=[carpet],[addtotal]-[addtotal]/100*[Forms]![jobs]![Frame33],[addtotal]))

the main form is called jobs
the sub form is called job_lines

for some reason this code just doesn't work.
Any help?
thanks,
James.

 

[James Dudden]

Where is this code? Is this happening on the invoice?

 

[missinglinq]

Also, I suspect you need

[Type]= "carpet"

as opposed to

[Type]=[carpet]

The brackets tell Access that carpet is the name of an object, not a value for a textbox.

 

[James Dudden]

Also, I suspect you need

[Type]= "carpet"

Well spotted missinglinq, that's a definite reason for the code not working.

If it doesn't work after you have changed this can you let us know where the code is when it is being executed.
Regards

 

[jamez141]

Hi, I changed the [carpet] to "carpet" - no effect at all.

the code is in the sub form in a text box.

it works fine without the sum() around it.

here is is again..

=Sum(IIf([type]="carpet",[addtotal]-[addtotal]/100*(Forms!jobs!Frame33),[addtotal]))

the text box still displays

#error

thanks,
James.

 

[jamez141]

print screen of form in design view...


http://www.jcookdesign.co.uk/access_db_sum_problem.jpg

James.

 

[James Dudden]

Looking at the screen dump - i think it could be a speed issue. It looks like add total is a calculated field and i don't think Access can sum it quick enough. This i think leaves you with 2 options:

1. Do the Add Total field in code
2. Do the Sum in code

If you have had no luck or no more replys I will test this when i have some time later.
Regards

 

[James Dudden]

What you need to do is calculate the add total field in a new column in your recordsource, then add this to your form as a control. Now do a sum of this field.

Hope this helps

 

[jamez141]

Hi, thanks for replying, I'm not totally sure what you mean by control?

thanks, James.

 

[James Dudden]

When i say control I mean field - so when you have created the calculated field in the recordsource it will appear in the field list. Drag this on to your form and do the sum of this in your footer.
Regards

 

[jamez141]

ok, i wasnt originally planning to save the add_total in the database. (i think this is what you mean?) I supose this could be a good thing but how can i have that as a control source and have a formula in the box as well?

James.

 

[James Dudden]

No i don't mean save it in the database. Ok this is what you need to do:

Open the form in design and open it's properties.

Click on the Data tab and then select Recordsource - a small button with 3 dots appears at the end, click this (if your form is based on a query this will open if not it will ask you if you want to invoke the query builder say yes).

Now you need to create your Add_Total field. In the next blank column (if you invoked the query builder first dbl-click on the star in the table above) click in the field and type Add_Total:YOUR ADD_TOTAL CODE HERE.

Now close the query window and save it. In your field list the Add_total field is now there - drag this on to your form. In your footer do a sum of this new field:

sum([Add_Total])

Regards