Calculated value in query to insert into a field in talbe?

[d9pierce1]

Hi all,
Happy New Year!!!!!!!

I have a query that popultes a form... OK
It has the tbl fields on it, and some calculated fields.

I am trying to use an IIf statement in one field of my query to either let me put value in or automatic insert value in if is not null....


Not real sure how to word that....

Field in table/query OpenBalance, standard currency field
Calculated Field in Query DueAmount, Field is DueAmount: [TotalPay]-[PaidAmount] This only gets value when loan fields are entered. Otherwise, null

I have tried tons of things and cannot seem to make it work at all.

So, If the calculated field is null, then I want to be able to fill in the OpenBalance value manually, if its not null, then I want the value of the calculated field inserted into OpenBalance field.

Obviously this does not work at all, just gives a parameter box.... but was the last thing i tried.
If(IsNull([DueAmount]),[OpenBalance],[DueAmount])

Is there a way to put an expresion in the OpenBalance field criteria that will allow me to enter the OpenBalance amount if the DueAmount isNull, and put the value in this OpenBalance field if not isNull....?

Thanks
Mr. Stumped

[Welshgasman]

Have you looked at the NZ() function ?

[June7]

Basically, no. Field in query is either editable or it is not. A calculated field (or textbox on form) is never editable. Data entry should be via form, not directly into table or query.

Saving calculated data is usually unnecessary and often a bad idea. If it can be calculated for saving, it can be calculated when needed.

Saving calculated value would require an UPDATE action SQL or code (macro or VBA) behind form.

[d9pierce1]

Thank you,
I guess i will have to configure another update then. Its basically a value I need to store in that field in table as so to include in a different update action...
So, If I do update that calc field, it has to update something else and would save a lot of code to just have it saved in that field....
This should be interesting to create a new update action... had a ball with the last one.

Smile\
Dave

[d9pierce1]

OK,
Going to have to write an update on this. So, any suggestions on where to put this update at? On form properties, on field properties?

basically i am going to want this to fire once it becomes not null. Before Updat, After Update, OnChange....? Not real sure where to fire this
at so I get it in a field on the table/form..... Basically want to take this value, put it in the field, so i can click a button and have it going to a different
form/Table... The biggest problem is i dont have the value till it is created by calculations and would like to input my info and have that value created for me,
Then put it in the field i really need the value in....

[June7]

Well, why is that second UPDATE action necessary? Again, saving calculated data is usually unnecessary. You have a saved calculation that is based on another saved calculation. If any part of this chain is neglected (original data is modified and any saved calculation is not), the data is worthless.

[d9pierce1]

Hi all,
June7, I was able to work around not saving that calc data and just want to thank everyone for all the assistance this past week!
Have learned a lot about updates and concat for sure... Did a bunch of concat today and really simplfies the code....

Thanks
Dave