Help with calculated table fields

[AmazonWendy]

Hi all
What I'm looking for is help on which expressions can be used in calculated fields in tables. I'm trying to do a date difference calculation and I'm being told by Access that I can't use the Now() function or any of the other functions I've tried.
It seems to only allow simple calculations like [date1]+30 which won't do what I want.
I know how to do this in a query but my customer needs the date difference value to be visible in the table.
Any help greatly appreciated.

 

[cyanidem]

Why would customer/user ever want to see a table?
Also, data in calculated field is not updated "live", so using functions like Now() or Date() wouldn't make sense; results would usually be wrong.

 

[AmazonWendy]

Thanks for the response. The customer wants to list their data in a table. This in my opinion would be best done in a query but they are adamant.
I guess this is one of the reasons that Microsoft created the calculated field in a table, where it wouldn't have been in older versions.

I disagree that using Now() in a calculated field would not make sense, I think their wish to see a number of days since the record was created is a reasonable one.

 

[cyanidem]

Thanks for the response. The customer wants to list their data in a table. This in my opinion would be best done in a query but they are adamant.
I guess this is one of the reasons that Microsoft created the calculated field in a table, where it wouldn't have been in older versions.

Why not show them continuous form, or split form even, where you could easily use any calculations you want? What is the reason to give users access to raw data in table? I'm not an Access pro by any measure but it's wrong IMHO.

I disagree that using Now() in a calculated field would not make sense, I think their wish to see a number of days since the record was created is a reasonable one.

It's reasonable, but not using calculated fields in table. You can construct query, or provide calculations in textbox on form. Calculated field in table is updated when one of the fields used in calculations is updated and it doesn't happen simply when you open a table for view. So If the record was created yesterday and no field value in it changed since then, your calculated field would show calculations valid for yesterday, that's why you can't use functions like now() in it.

 

[arnelgp]

you can create another field (Date/Time), then every time update this to current date. Based your calculated field on the above field.

 

[AmazonWendy]

Thanks both.
I think I will discourage them from wanting the table to display the data and give them a query and a form, it is much simpler.

 

[The_Doc_Man]

No, Wendy. It is your responsibility to advise your customers when they are making a technical error in a request. Don't DISCOURAGE them. Tell them that for Access, their request is technically not correct given what they are asking to see, and that a query is the technically correct solution for their problem.

If they are not familiar with Access, you can tell them that it is just the way that access does what it does. If they ARE familiar with Access, have them post a question on that topic here under a name we wouldn't recognize and advise them to see how quickly they get redirected.

Where I am going with this is that with the concept of merchantability, you have a monetary (fiduciary) responsibility to give them advice including knowledge of technical limitations.

Now, here is the next question: HOW IN THE HELL WOULD THEY KNOW? If they actually can see the record because they can directly create it, there is problem #1. Users should NEVER see the tables, queries, etc. EVERYTHING should be done via forms. And if so, you can dump that date field into the record via VBA or a default value in the text-box on that form, or any of a gazillion other ways that they would never see. Which then protects YOU from having to field such questions in the first place.

 

[Rabbie]

Hi all
What I'm looking for is help on which expressions can be used in calculated fields in tables. I'm trying to do a date difference calculation and I'm being told by Access that I can't use the Now() function or any of the other functions I've tried.
It seems to only allow simple calculations like [date1]+30 which won't do what I want.
I know how to do this in a query but my customer needs the date difference value to be visible in the table.
Any help greatly appreciated.

This rings alarm bells for me. Calculated fields in a table should be a no-no because the calculation Isi lye performed once when the record is inserted. This means that if one of the fields used in the calculation changes the calculated field does not unless you take special steps to recalculate it.

Also in my opinion tables should be used for storing data, queries for selecting data, forms for inputting data and reports/forms for displaying data. In nearly every case I have seen where tables are displayed they are not properly normalised and this usually makes for difficulties when the customer wants the DB expanded to do extra things.

If I were in your position I would produce a continuous view form to show the customer that the view would be similar and you would have no problems with showing the date difference. The correct way is often simpler than the wrong way

EDIT

I have just seen Doc's post and as usual he has got it right

 

[AmazonWendy]

Thanks again.