Calculated Field

[cuttsy]

This seems a daft question but I have searched the forums and can't find an answer so I hope someone can help me.

I have a table: Register(RegID, Pupil, Activity, Duration, Preparation)

I have a query that I want to calculate the total hours the pupil has spent on activities.

The Duration and Preparation fileds are held as number of minutes so for a 1 hour event the number 60 is held.

I have a query with 4 fields:
Pupil
SumofDuration
SumofPreparation
TotalHours = ([SumofDuration]+[SumofPreparation])\60

The problem I have is that TotalHours is calculating as an whole number.
e.g. I have a Record
Pupil = 701
SumofDuration = 270
SumofPreparation = 0
TotalHours = 4

But I want TotalHours = 4.5

I have set the Duration and Preparation field sizes to Decimal and Format to General Number.

I don't know what else to try.

 

[Bodisathva]

Try either redefining your [SumofDuration], [SumofPreparation] , and TotalHours fields as decimals or doubles, or typecasting calculations:

TotalHours = (CDbl([SumofDuration])+CDBL([SumofPreparation]))\60

 

[cuttsy]

Couldn't get that to work.

I have tried it with a siple query and even this rounds to an Int.

SELECT PupilRegister.Pupil, PupilRegister.Activity, CDbl([Duration])\CDbl(60.0) AS Dur, CDbl([Preparation])\CDbl(60.0) AS Prep
FROM PupilRegister;

I'm getting very frustraited, It can't be this difficult to divide in access I must be missing something obvious.

 

[neileg]

If you use the backslash \ instead of the forwardslash / to signify division, the result of the division is always an integer.

See here http://office.microsoft.com/en-gb/access/HA011813841033.aspx?pid=CL100570041033#6

 

[cuttsy]

Thanks, I just had this pointed out to me.
That was a giant pain in the ass!

I knew I was doing something daft.
Thanks.