Calculated Fields in Access Userform & sheet (1 Viewer)

[Zany]

Hi Guys use the expression =DateDiff("d",Date(),[DueDate])
To calculate the difference between the day the incident reported and the current day to see how old is the case.

However my calculations result on the form are not shown on the sheet when i saved it.

Please anyone with a explaination on my issue?

 

[Isaac]

What do you mean by "the sheet when I saved it". What sheet? What's a sheet?

Any control with that expression in it is unbound

 

[Zany]

What do you mean by "the sheet when I saved it". What sheet? What's a sheet?

Any control with that expression in it is unbound

Please further elaborate on the your explanation. Sheet: My database which feeds from the Form. The expression i used in the form displays the differences but when saved to my database the field are blank. Hope this clarifies. New in using access so still having difficulty understanding it.

 

[arnelgp]

you do not need to Add the expression to your table, because
it changes Everyday and can be Calculated.

if you really need to save it to your table, add the "field" to your form.
set it's Visible property to No (invisible).

you then add code to the Load Event and Before Update Event of the form.

private sub form_load()
Currentdb.execute "update yourTable set [theField] = DateDiff("d",Date(),[DueDate]);"
end sub


private sub form_beforeUpdate(cancel as integer)
Me!theField = DateDiff("d",Date(),[DueDate])
end sub

 

[Zany]

you do not need to Add the expression to your table, because
it changes Everyday and can be Calculated.

if you really need to save it to your table, add the "field" to your form.
set it's Visible property to No (invisible).

you then add code to the Load Event and Before Update Event of the form.

private sub form_load()
Currentdb.execute "update yourTable set [theField] = DateDiff("d",Date(),[DueDate]);"
end sub


private sub form_beforeUpdate(cancel as integer)
Me!theField = DateDiff("d",Date(),[DueDate])
end sub

Got an error here. change due date to date_of_incident referring to my data field. But still can't even complete writing it and I already got an error.

 

[arnelgp]

you need to change yourtable to the name of your table.

 

[Pat Hartman]

The reason that the calculated value is not being saved is because the control is not bound. Using an expression such as --
=DateDiff("d",Date(),[DueDate])
in a control's ControlSource neabs tge cibtrik usb;t biybd, Bound controls have a fieldname in their ControlSource which is how Access knows what field to save a value in.

arnel told you how to save the calculation. But no one has told you not to save the calculated value or why.

The reason the value shouldn't be saved is pretty obvious. Tomorrow the value would be different. Are you prepared to run an update query every single day to change the calculated vaue? That isn't the way we use relational databases. You can have the calculated value available to you any time you need it and it will ALWAYS be the current value. All it takes is performing the calculation in the query that the Form/Report is bound to.

Select ...., DateDiff("d", Date(), DueDate) as IncidentAge From ....

This allows you to sort on the calculated value so that the oldest incidents can pop to the top and provides a lot more functionality than a calculated value that is always wrong the day after it is calculated.

 

[Isaac]

Please further elaborate on the your explanation. Sheet: My database which feeds from the Form. The expression i used in the form displays the differences but when saved to my database the field are blank. Hope this clarifies. New in using access so still having difficulty understanding it.

Thanks for the additional info, I am just now looking at this thread again.

As I and others have mentioned, if you want to have a Bound control on a form (where the data is dynamically bound to the Table data - and thus will save when something is typed into it), then you have to adjust that control's ControlSource property to match a column in the form's underlying Recordsource [also a property]. Then when someone types something in there, it gets saved to the table.

On the other hand, when you have (as a control's ControlSource), an expression, like DateDiff, that will display on the form for display purposes, but be unbound, and not save to the table.

In order to take a moment in time when someone is using the form, and make a calculation (like Date Diff) and then have that save to the table, you will have to write some code. Some ideas are:

- code the form's BeforeUpdate event to cause the control's value to become your DateDiff expression
- code in the source controls' Exit or LostFocus events (those controls whose values are the sources of the DateDiff, might just be one), in order to update the destination control.

I would probably recommend the first one.

 

[Pat Hartman]

PS - Access forms are NOT userforms so be careful if you are searching for info. You will get irrelevant or just plain wrong responses if you search for userforms.

Isaac, It looks like you are actually recommending saving this calculated value. Is that your intention? Are you recommending that saving calculated data that will ALWAYS be invalid the very next day is good practice?

 

[Isaac]

PS - Access forms are NOT userforms so be careful if you are searching for info. You will get irrelevant or just plain wrong responses if you search for userforms.

Isaac, It looks like you are actually recommending saving this calculated value. Is that your intention? Are you recommending that saving calculated data that will ALWAYS be invalid the very next day is good practice?

No, my intention wasn't to address that aspect of it at all, merely to help explain the difference between bound and unbound controls, and how to save a value that may need to be calculated

 

[Pat Hartman]

Thanks Isaac. I was pretty sure that would be the answer but reading the original response, All I see is "I would probably recommend the first one." which explains how to make the field bound so it can be saved.

I hate to be pedantic but, I don't think this is a situation for equivocation. Storing calculated values is a violation of second normal form and therefore bad in general although we do occasionally do it, especially in data warehouse applications. This calculation is seriously bad since the result will be invalid tomorrow and we would never store this particular result even in a data warehouse..

 

[Isaac]

the result will be invalid tomorrow

Whether or not that's a true statement, to be technically correct though, doesn't it kind of depend on how the business defines that column? What if the business tells you that it's defined as, "the number of days between those two things at the time this record was saved" ?

In this question I'm not suggesting that it's still necessarily a good idea to save it, if it can be defined by a query later, I'm just addressing the specific statement that the value will for sure be wrong tomorrow.

Since we're being pedantic

 

[Pat Hartman]

The calculation includes Date() which will be different tomorrow than it is today so if the result were calculated tomorrow, it would be a different value from what would have been calculated today. That is the epitome of of bad data but you are correct. Perhaps the user doesn't care at all what the value of this field is tomorrow. Maybe it only matters to them what the value is today. We'll never know unless zany elects to tell us

 

[Isaac]

The calculation includes Date() which will be different tomorrow than it is today so if the result were calculated tomorrow, it would be a different value from what would have been calculated today

Oh - sorry, we have a misunderstanding that can clear that up. What I meant was, if, in the form's BeforeUpdate event, a person ran the following code, referencing a bound control: Me.Controlname.Value=DateDiff(something)

Then that value would be static, and save correctly, and might mean something valuable to the business. (Although I agree, why not just calculate it at runtime? Unless the answer is 'no' because the source values might be changed somehow and the original calculated value be meaningful to preserve).

You thought I meant the DateDiff as an expression dynamically persisting somehow in the table column, which isn't what I meant, and of course would result in a silly daily calculation ever changing. Sorry for the confusion.

 

[Zany]

The reason that the calculated value is not being saved is because the control is not bound. Using an expression such as --
=DateDiff("d",Date(),[DueDate])
in a control's ControlSource neabs tge cibtrik usb;t biybd, Bound controls have a fieldname in their ControlSource which is how Access knows what field to save a value in.

arnel told you how to save the calculation. But no one has told you not to save the calculated value or why.

The reason the value shouldn't be saved is pretty obvious. Tomorrow the value would be different. Are you prepared to run an update query every single day to change the calculated vaue? That isn't the way we use relational databases. You can have the calculated value available to you any time you need it and it will ALWAYS be the current value. All it takes is performing the calculation in the query that the Form/Report is bound to.

Select ...., DateDiff("d", Date(), DueDate) as IncidentAge From ....

This allows you to sort on the calculated value so that the oldest incidents can pop to the top and provides a lot more functionality than a calculated value that is always wrong the day after it is calculated.

Thank you Pat.
Cheers!

 

[Pat Hartman]

You're welcome