Nested IF or IIF Formula Help

[MelonFuel]

Yeah sorry you wont i've changed it now so you should understand

AA Always needs to be 225 even if the quantity is 100.

Descriptions column in the access DB
( BB, CC, DD, EE, FF, GG, HH, II, JJ, KK, LL, MM ) will be only the ones that will get added.
The excel is not full so just look at the access DB.

( NN, OO, P,P QQ, RR, SS, TT, UU, VV, WW, XX, YY, ZZ ) will not add to the CL

The CL is on the EXCEL DB so like i said if you:

Change AA quantity to 100 and check the result of the CL on the excel spreadsheet ( it should be 225 ) if no other quantity is in any other fields. Also the total stays the same so i also need to include that.

Now change BB to 100 and in the CL you will see the the CL is now 285 and now the total changes.



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: The answer to this is yes i do but i know how to do that i think.

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: Yes also but i know how to do this.

All i need is a formula to add up ( BB, CC, DD, EE, FF, GG, HH, II, JJ, KK, LL, MM ) but not include ( NN, OO, P,P QQ, RR, SS, TT, UU, VV, WW, XX, YY, ZZ ) but include AA to ZZ in the total.

Have a mess around with the excel DB and you will understand what im on about haha

Thanks

[recyan]

Still not sure, all the same,
Just give below a try :
In a text box on your Form at the top, use below as Control Source

=DSum("TX4740RingWidth","tblTX4740","[TX4740Description]<>'AA'")

Thanks

[MelonFuel]

I have just tried the code and it works i think ( it doesn't throw out any error or #name ) but i don't know where its getting that value from. Plus it needs to update when i change the quantity values on the form.

What i've done now on my proper one is added 2 new fields 4740225 and 4740285 and in 4740225 i've put a number field with the value 225 and in 4740285 ive put a number field with the value 285. Do you know any code that can do the sum.

I also need to add a text field to 4740 form to add up only ( BB, CC, DD, EE, FF, GG, HH, II, JJ, KK, LL, MM ) and when the number in the new text field reaches 21 it changes another text box on the form to 285. if its 20 or below the value of the other text field will be 225.

Thanks

EDIT: i have just put it on my subform and it still displays the the same number 521 and it doesn't change the value on it. In the code you have supplied the quantity needs to go in there somewhere but i don't know how.

Thanks

EDIT: I have just changed =DSum("TX4740RingWidth","tblTX4740","[TX4740Description]<>'AA'") TX4740RingWidth to TX4740Qty and it does some weird stuff adding up numbers, like when you scroll down the number it will be like 1, 3, 12, 12, 12, 12, 15, 33???

Thanks

EDIT: I partly works im soooo happy this has been doing my head in for ages i just now need to know if you can get it to update ever time i click away or move my selection then i can role out the database. i just have to change ringwidth to Qty and tblTX4740 to qryTX4740

Thanks

[MelonFuel]

My Code now.

On Main Form - =[qryTX4740 subform].[Form]![Dsum]

On Subform - =DSum("TX4740Qty","qryTX4740","[TX4740Description]<>'Basic'")

Plus a slight mistake is the it doesn't add AA which is what i want but it also adds ( NN, OO, PP, QQ, RR, SS, TT, UU, VV, WW, XX, YY, ZZ ) and i dont want it to add these ones.

Thanks

[recyan]

i just now need to know if you can get it to update ever time i click away or move my selection then i can role out the database.

Am not much in to forms.
For update, take a look at events like on update, on dirty, on change, etc.

My Code now.
On Main Form - =[qryTX4740 subform].[Form]![Dsum]

Is [Dsum] the name of your field on the form. Would suggest changing it to something relevant to the field. Avoid using reserved words, standard function names as field names.

My Code now.
Plus a slight mistake is the it doesn't add AA which is what i want but it also adds ( NN, OO, PP, QQ, RR, SS, TT, UU, VV, WW, XX, YY, ZZ ) and i dont want it to add these ones.

Would suggest, you first take a look at the DSum() function explanation via google & then see if it suits your purpose.
If you still face problems, post your latest updated db with dummy data & some one should be able to help you along.

Thanks

[likhon]

help me with access balance sheet & query

query Cord:
SELECT Transaction.AccountID, Transaction.Date, Transaction.AccountsName, Transaction.WithdrawalAmount, Transaction.CreditAmount
FROM [Transaction];

[recyan]

You need to start your own thread & should not post in others threads unless relevant to that thread.
In case you do not know how to start your own thread :
1) Go to the Forum page by clicking on Forum near the top of this page.
2) Select one of the listed Links below, in your case "Queries".
3) On that page, click on "Post New Thread".

After starting new thread, till some one comes along, just google "Running Balance in access".

Thanks