By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,497 Members | 2,127 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,497 IT Pros & Developers. It's quick & easy.

Saving the value of a calculated field to a table field

P: 2
I have a calculated field in a form using this expression typed in the control source box
=[Begin Date]+[Duration]
Since I cant bind this field to a table field I would like to transfer the value returned by this expression to a field in a table. Does anyone know how to do this?
Jul 13 '12 #1
Share this Question
Share on Google+
9 Replies


P: 4
the general rule is "never save calculated values" since if any data element of the calculation changes the saved value is invalid. The calculated value is always available through a query as an example. But OK that disclaimer being said, if you're using a form to edit the [begin date] and [duration] fields, use the after_update event in both fields. Check for null in the other field and write the computed value to your summary table if both fields contain data. You can use DAO but ADO is much preferred.
Jul 13 '12 #2

zmbd
Expert Mod 5K+
P: 5,287
FGSteward,
I second MarkP2012 comment about calculated values. You should be able to create a select query that will do this for you.

With that said; however, there are times wherein such information needs to be recorded for history such as when the begin date or duration may change but you may need to recall the result at some point in the future; thus, why I ask why you need to save the result of the calculation and if the values in th fields are stable or will change with time?

-z
Jul 14 '12 #3

NeoPa
Expert Mod 15k+
P: 31,186
I guess we'll all say the same thing here about Normalisation Rules etc. Check out Database Normalisation and Table Structures. It appears to me that the example you quote is exactly the sort of thing you need to consider avoiding assiduously, if you want to avoid the panoply of problems associated with non-normalised database design.

If you are saving something which is related to something available, but not stored (EG. The current value of Date() or Now() - which are actually functions), then you can sensibly store these values. On a form you would probably set the DefaultValue of the control to the calculation required. Alternatively, depending on the precision required and how important it is for the operator to see the value before proceeding, you may choose to set it after the fact in either the AfterUpdate event procedure of the control or the BeforeUpdate event procedure of the form.

The one really important point to take from this question though, is not to do it at all in most circumstances.
Jul 14 '12 #4

P: 2
I am trying to filter a report or a query off of the calculated field. That is why I wanted to save it to a table field. I really dont need to store the date permanently. When the date calculated by "[Begin Date] + [Duration]" is past the current date I would like to filter these records off a report.

Thanks for any help..


@zmbd
Jul 16 '12 #5

Rabbit
Expert Mod 10K+
P: 12,315
You can do exactly that in the query. Just use the WHERE clause to filter out those dates from the result set.
Jul 16 '12 #6

zmbd
Expert Mod 5K+
P: 5,287
See what happens when I go to lunch... Rabbits beat the tortoise ! :)

Rabbit is exactly correct... the WHERE clause in the SQL string is the way to do the filtering you're after without haveing to store the calculated value.

-z
Jul 17 '12 #7

P: 3
thanks SO much for NOT answering this question, I have heaps of spare time for to read through reams of pages where people tell me not to do what I need to do....

thanks for nothing guys
Apr 7 '15 #8

zmbd
Expert Mod 5K+
P: 5,287
bushrat
thanks SO much for NOT answering this question, I have heaps of spare time for to read through reams of pages where people tell me not to do what I need to do....

thanks for nothing guys
1) This thread is two years old
2) You are not the original poster
3) You just joined on the 7th? You might have better results if you have a specific question by using the search tool ( that field at the top of the page by the orange magnifying glass :) ) or better yet start a new thread with your question clearly stated.
4) The tone of your post is not exactly friendly and not likely inspire people to help. Bushrat, please keep in mind that our Experts and Moderators provide their help both free of charge and without pay along with the insights of fellow programmer/members that do this because we enjoy helping.
Apr 7 '15 #9

Rabbit
Expert Mod 10K+
P: 12,315
Often people only think that something needs to be done without looking at the alternatives. What we are trying to say in our responses in this thread is that it doesn't "need to be done" this way. That it is much better and saves you from a lot of trouble in the long run to not do it that way. That our years of experience in databases have shown us that what you think "needs to be done" is actually counter productive and it is much more preferable to normalize the data.

We do not tell you that you shouldn't do something to be difficult. We are not trying to prevent you from getting the result you need. We are trying to save you from bad practices and show you the good practices that have been born from years of study in the field of databases that we have learned from people that have spent decades themselves learning.
Apr 7 '15 #10

Post your reply

Sign in to post your reply or Sign up for a free account.