Number Format not working

[Kerry Purdy]

Hi,

I have a make table query using groups & totals, one of my fields is set to
show the average. I want to show the average value as a whole number, no
decimals.

I have changed the properties of the field to show as fixed number format
with 0 decimals, standard number format with 0 decimals, I have even changed
the input mask. I have used the format([eval score],"#") but I am still
getting multiple deimal places on my results field - up to 14 decomal places
on some results.

Does anyone have any other ideas please?

Many thanks for your time

Kerry.

 

[Rick Brandt]

Hi,

I have a make table query using groups & totals, one of my fields is
set to show the average. I want to show the average value as a whole
number, no decimals.

I have changed the properties of the field to show as fixed number
format with 0 decimals, standard number format with 0 decimals, I
have even changed the input mask. I have used the format([eval
score],"#") but I am still getting multiple deimal places on my
results field - up to 14 decomal places on some results.

Does anyone have any other ideas please?

Many thanks for your time

Neither formatting nor input masks affect the actual underlying data. You
need to use an expression that will return an actual integer *value* rather
than just making the value *appear* like an integer. Wrapping your average
in either the CInt() or CLng() function should do it.

 

[John Spencer]

Format controls how things are displayed and has no effect on how they are
stored. Input mask controls how things are typed, but has no effect on how a
query handles the data unless you type each bit of information into a query.

Change the field you are getting the average on to a calculated field

Field: TheAverage: Round(Avg(SomeField),0)
Total: Expression

Or if you know that there is always an average value (no null values returned)
you can use
CLng(Avg(SomeField))

Or you can use
Val(Format(Avg(SomeField,"#")))

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County