I have a query that has a calculation field:
Sub Total: [Rate]*[Hrs]

Works fine on whole or half numbers, but how do I get it to calculate on quarter?
ie. 2.75 hours

Apologies - probably basic know-how, but not my forte (IMG:style_emoticons/default/smile.gif)

I don't understand the question.
The calculation should work for any values. Are you sure you are formatting the display in your form/report to show the full result?

how are those fields defined in the table?

Before doing the calculation you would have to 'round' the time factor to a quarter hour.

However, I included round in quotes because how you do it depends on the related business rule governing how you handle fractional hours. There are several possibilities:

• drop fractions between quarter hour intervals (7 hrs, 15 minutes would be treated as exactly 7.0 hours, 7 hours 16 minutes would be treated as 7.25 hrs)
• round everything to the next quarter hour (7 hrs 1 minute would be treated as 7.25 hrs)
• round to the nearest quarter hour (7 hrs 10 minutes would be treated as 7.0 hrs, 7 hrs 8 minutes would be treated as 7.25 hours)

Which rules applies depends on the nature of the business and the particular business activity. A particular enterprise may apply different rules to different scenarios. For example customers a charged for each quarter hour or fraction, employees are paid for each full quarter hour. You will need to define scenario and rule that applies to your calculation.

Glenn

It's rounding the field, so the resulting calculation is not correct.
We only work with quarter hours (1.00, 1.15, 1.30, 1.45).
I was incorrect above, it will still round if a half figure is input (1.5).
The field is set to General Number and the decimal is auto.
I've tried changing to Set Number with 2 decimal places, but its still rounding.

I'm sure its something obvious ... (but not to me) (IMG:style_emoticons/default/smile.gif)

This post has been edited by karenedmonds: Jan 19 2012, 07:40 PM

>>We only work with quarter hours (1.00, 1.15, 1.30, 1.45).<<

Could it be that you are mixing up minutes and percentages?

1.15 is NOT one and a quarter hours. It is one and fifteen - one hundredth hours.

1.25 is one hour and fifteen minutes expressed as a decimal.

Could you provide some specific examples of the rounding effect? What are both values being multiplied?

Yeah, sorry, good point.
I am not the end user, but I did just clarify with them, and they are using it correctly (ie. .25 for each quarter hour).
But that's not the problem.
We have a field for Hours Worked [Hours] and another for the Rates Per Hour [Rate].
We often need to charge by quarter hour.
I have tried various settings for this field including the formats and decimal spaces etc.
I assumed it needs to be number or else it will not accept the calculation.

However, I have just tried changing the [Hours] field to Text and now it seems to be calculating properly.
Go figure!
I will proceed to change the others (of which there are about 100!!) and let you know if this is the case (for anyone else's reference).

It sounds like you had the field set to integer. Change that to single.

You're right - thanks - didn't look there.
That works.

Thanks everyone.