date calculation and decimal places

M

Mary

I have a query that uses the datediff function to determine the "turnaround
time" or number of days to complete a task. I have this field format set as
FIXED in the query. Then there is another query that calculates the average
of "turnaround time". I have set this field's format property to FIXED also.
The first query runs properly and gives me a number with two decimal places.
The problem is that when I run the second query, the average value has too
many decimal places. Changing the format to STANDARD or even CURRENCY has no
effect on the number of decimal places for the average.

There is a report based on the second query. I have tried setting the format
of the control based on Turnaround Time to fixed with 2 decimal places. I
also tried entering a custom format of #.## or 0.00, with no effect on the
number of decimal places.

The only way I could control the number of decimal places in the Average
Turnaround Time was to use the ROUND function. Does anyone know why setting
the format property didn't work for me?

Access 2003/Win2000

Thanks,
Mary
 
A

Allen Browne

What you describe is exactly how I would expect Access to work.

The formatting applies to the values in the field. However, when you sum or
average, the formatting is not applied to the aggregated value. Therefore
you do have to explicitly Round() the result.

Asides:
1. The result of a DateDiff() will always be a whole number, regardless of
how you format it.

2. Not only do aggregated fields have no formatting defined, but Access can
even misunderstand the data type of calculated fields, as explained here:
http://members.iinet.net.au/~allenbrowne/ser-45.html
 
M

Mary

Thank you, Allen. I've read several Access books and don't ever remember
seeing that you can't specify format for calculated query fields. I'll keep
your "Tips for Serious Users" page handy for future query design.

Mary
 
Top