Calculated field in table can only sum whole numbers

ph001

New member
Local time
Today, 00:56
Joined
Oct 1, 2014
Messages
7
Hi all,

Access 2010.

Not an expert with Access by any means but I thought this would be very simple!

Have a table with columns of data which I want to total at the end of each row. The Calculated Field does this simply enough but it only seems to be able to add whole integers? How do I get it to accept and add values to 2 decimal places?

Many thanks in anticipation.
 
Last edited:
I don't use the calculated field, but does it have a data type? If it's Number/Integer or Long Integer that would be your problem.
 
Thanks for the reply. Yes it is set to long integer and cannot be changed. This also seems to be the case for all the fields it depends upon??
 
The long integer data type does not accept decimals - that's what the word integer means.

As a rule, you rarely want to store calculated values in a table. That said, if you absolutely MUST store the calculated value rather than calculating it on the fly, the only suggestion I can make for you (since you said the number cannot be changed to any other data type) is to multiply your values by 100 for storage in the table, and when you display them, convert them to Double, divide by 100, and display that.
 
Many thanks. Yes, I guess the real issue is that the number format cannot be changed in the calculated field.

The fields contain numbers of hours in a day against activities, so they need to be in 15 min resolution, e.g. 1.25 hrs. Then I wanted the total hrs column to automatically add up the hours for the day. This is for a basic time recording system.

Maybe I need to do this another way?
 
To be quite honest, problems like these are why we so strongly recommend against using calculated fields. (Also, they take up unnecessary storage space.)

My suggestion would be to store your activity times in a Single-type field, and calculate the total hours only as necessary. You don't actually need to store that the 16 0.5 hour activities sum up to 8 hours - you can calculate that when needed for forms and reports.

If that's absolutely not an option (time's often a factor), then you absolutely have to either change the field type to one that allows decimals or else use the number games I showed.

Also, there are a lot of pre-generated models available you might want to look at. A TON of them can be found HERE.
 
Can you not store the minutes (integer) then calculate to an hour value ?
 
That is one solution but I always find inputing minutes in the time format rather cumbersome.

The staff who enter the time recording do it on the form, so it only really needs to display the calculation on the form.

Sorry for the total noob question - but how do I do that?
 
TBH if they are putting hours in you only need to multiply by 60 to give you the minutes. But they could just enter the minutes as a integer - not as a time field, that might be easier?

Or have two entry boxes on the form : Hours: Minutes: and do the maths in the background to store the minutes as a total?
 
Sorry - I am probably not explaining this very well at all. The issue seems to be that because I am using a calculated field, it is locking all dependent fields to long integers... so it will not allow any decimal places in the 'time taken' fields.

I think I need to scrap the calculated field and just display the calculated value on the form rather than in the table. I'm just not sure how to do that?
 
Ah sorry - okay put a field on your form with its control source set to

=[Field1] + [Field2]

Or whatever your calculation is.
 
Here's where I embarrass myself!

All I have ever done in the past is add existing fields into a form by dragging from the list on the right hand side. How do I add a field with a control source?
 
All controls that display data have a control source property. Just click on the control you want to place in the toolbar, click on the form to place it, right click the control, select 'Properties', and in the Data tab, one of the items will be Control Source.

You may see us talking about 'bound' and 'unbound' controls. A bound control is one whose control source is a field in the underlying table or query, while an unbound one isn't. Changes made in a bound control change the data immediately. Unbound controls are generally either set to display-only (such as with calculated data) or are used for manual entry that you don't want instantly added to a field (say, if you want to get a record built completely and checked for errors before adding it).
 
The data type of a calculated field is defined by the ResultType Property of the field.

It is not limited to Integers.
 
Super! Thank you, I used a text box and used the expression builder as the control source. Works perfectly. Thanks again for all your help.
 

Users who are viewing this thread

Back
Top Bottom