Nested IF or IIF Formula Help

[MelonFuel]

Hi Guys




I need some help


I'm need some code either nested IF or IIF formula to add up the values if a check box has been ticked.


I may have 1 item or 20 items and the formula needs to add them up aswell. I really don't know if this is possible but this is what my colleague wants. I'm sorry for the brief description thats all he has really said. If you need more information let me know thanks. Any help would be appreciated.


Thanks

[Robeen]

Can you tell us where this calculation is going to be taking place?
Is it to be when a button is clicked on a Form [Adding values that are on the Form] . . . or is it in a Query [Adding values from various fields in the Query - from a Table]?

The more information you can post here, the easier it will be for people to help you with a solution.

[MelonFuel]

Thank you for the reply.

The reason for asking this question is because i have been set the task of converting an excel spreadsheet into an access database.

The code will be placed in the query

This is where it gets hard to explain:

Here i go in one table there are 27 items in the 4740 table. Only 12 out of the 27 items need to be added together but the quantity will often change.

E.G.

BASIC - (Needs to be in the field but when the quantity is changed to 1 it wont add it.

5 AMPS - (Needs to be in the field but this time if the quantity if 1,2 or 100 it needs to be added to the quantity total.)

same goes for other 11 items

The other 15 do not need to be added but still need to be added to the spaceleft, throughput.

Plus for the 12 items that are being added i need a sum to work out this.

12 items total if more than 20 display the number 285 if less than 20 display 225.

[Robeen]

I know you did your best to explain your situation, but it is still difficult to understand.
Would it be possible for you to post an example of some of your data [if you click 'Go Advanced', then you will have the option to create a Table in your post, and add some data to it].

If we see actual data [in which we can see what needs to happen in the IIF statement] - then it will be easier to visualize the problem and come up with a solution.

If what you need is to be able to construct a nested IIF statement, then a good way to start is to create a simple, single-level IIF statement, and then add one level of nesting to it. Once you have that working, and you understand what you accomplished, then you can add another level of nesting . . . and so on.

Nesting can get complex really quick, I should warn you, but once you understand the 3 parameters of the IIF statement, you will be able to work it out.

Post some example data here [even if you have to make up some fake data] so that we can help.

[MelonFuel]

ill really think about it tonight on how i can explain it best but its hard today because work has been very busy

Thanks for all your help i should be on tomorrow.

[MelonFuel]

On second thoughts would it be possible to use conditional formatting for what I'm trying to achieve where if the quantity changes to 21 the number in another number text field will turn to 285 and if its below 20 the number will be 225.

[recyan]

Based only on your last post, perhaps,
if quantity is the name of the textbox on your form & you want this to happen in another text box say 'x',
put this in the 'x' text box,

=IIf([quantity]<=20,225,285).

Thanks

[MelonFuel]

Thanks for your post.

I have got this code already in but i only want it to add 13 out the 27 fields up thats the part i'm struggling with.

Excel is easy to that but i just cant figure it out in access.

The database is already in excel all i'm doing is converting it from excel to access.

Im 99% done its just this thats holding me back

Thanks

[recyan]

i only want it to add 13 out the 27 fields up

Pls elaborate

Thanks

[MelonFuel]

Thanks for the reply recyan.

I have attached a simple excel version of what im trying to do.

All together there are 27 results on the main excel database. If you double click the field to the right that is the code that i am trying to get into access.

Heres the code if you cant download the database.

=IF(E17<=20,225,285)

Thanks

[recyan]

First, we need to understand where you are trying to achieve this result.
In a form or In a report or In a query.

What you are asking for should be possible.

Thanks

[MelonFuel]

alrite ok sorry i forgot to mention where its going.

Right.

The calculation should be in the TX4700 Query.

Then that query will be brought over as a subform and placed on the main form.

I will then do a calculation on the main form
( Something like this =[qryTX4700 subform].[Form]![4700calc] ) inside a text box

Which will update every time i move or type

[recyan]

Difficult to suggest something without the table/s structure/s & data involved.
If you can post the table/s involved with some dummy data thrown in, & the result you want out of it, it would perhaps be easier for some one to help.

Edit : Have seen the excel posted by you, but not sure whether that is the way data is stored in the db.
Thanks

[MelonFuel]

ok here it is all with dummy data.

There are no relationships in the database because there is no need for them.

TX4740RingWidth ( the ones with numbers in are the ones I'm talking about that need to be added ).

If you look back at the excel database and you change the quantity of AA, it will always stay 225 even if you change the quantity to 100. i need that added in aswell somehow.

The reason why the ignore is in the table is what i tried. I was going to make some vba code (dont know how) so that it would add up only if the checkboxes that arent ticked. If you cant achieve the other way would you mind helping me try and do the Tickbox method.

[recyan]

Sorry, Was not able to understand things.
Do you want the sum of RingWidth from sub-form qryTX4740 subform to appear at the top in a text box in the Form frmTX4740.
Do you want the count of Number of RingWidth from sub-form qryTX4740 subform to appear at the top in a text box in the Form frmTX4740.

If neither of the above, can you elaborate a bit.

Was also not able to understand your reference to AA in excel sheet.

Thanks