Average Days

I'm using the expression below to figure out the Average Number of days in
custody from said field. I'm getting a 0 value in the report. Some of the
data has Null values depending on the case; is this why I'm getting the 0?
Need all the help I can get.
=Avg([# of days in Custody])

Mr. Null & Void

The Null value is cause the problem. You can use the Nz function to prevent
this. When you use the Nz function, you must put each element of the
calculation that could possibly be null in a function, not the results for
example, assume x = Null

Nz(x + 10, 0) will return 0
Nz(x, 0) + 10 will return 10

=Avg(Nz([# of days in Custody],0))

BTW, # of days in Custody breaks almost every naming rule there is.

Use only letters, numbers, and the underscore in names.
Do not use spaces or any special character other than the underscore.
Do not use any Access reserved words (Date, Value, Description, etc)

Typical database naming stanards dictate all upper case with words separated
with the underscore, so a good name would be:
DAYS_IN_CUSTODY

--
Dave Hargis, Microsoft Access MVP


"Nick CWT" wrote:

I'm using the expression below to figure out the Average Number of days in
custody from said field. I'm getting a 0 value in the report. Some of the
data has Null values depending on the case; is this why I'm getting the 0?
Need all the help I can get.
=Avg([# of days in Custody])

Mr. Null & Void

Hi Nick

The nulls won't be included in the average so they won't cause the zero
although you should decide whether to convert the nulls to zeroes themselves
if you want them included in the average calculation.

Eg Avg of null, 3 and 9 = 12 / 2 = 6
Avg of 0, 3 and 9 = 12 / 3 = 4

Is it possible that the Avg is very small and you are seeing it rounded?
Try changing the format so you can see decimal places.

Also, for testing only, add a Sum of Days in Custody and a Count of Days in
Custody
This should show you what numbers the Avg calculation is using.

Hope this helps

Andy Hull


"Nick CWT" wrote:

I'm using the expression below to figure out the Average Number of days in
custody from said field. I'm getting a 0 value in the report. Some of the
data has Null values depending on the case; is this why I'm getting the 0?
Need all the help I can get.
=Avg([# of days in Custody])

Mr. Null & Void

I'm still getting 0? When I do Sum I get 0? When I do Count I get 2 = # of
records with non-Null values. I also appreciate the additional info.
Thanks.

"Klatuu" wrote:

The Null value is cause the problem. You can use the Nz function to prevent
this. When you use the Nz function, you must put each element of the
calculation that could possibly be null in a function, not the results for
example, assume x = Null

Nz(x + 10, 0) will return 0
Nz(x, 0) + 10 will return 10

=Avg(Nz([# of days in Custody],0))

BTW, # of days in Custody breaks almost every naming rule there is.

Use only letters, numbers, and the underscore in names.
Do not use spaces or any special character other than the underscore.
Do not use any Access reserved words (Date, Value, Description, etc)

Typical database naming stanards dictate all upper case with words separated
with the underscore, so a good name would be:
DAYS_IN_CUSTODY

--
Dave Hargis, Microsoft Access MVP


"Nick CWT" wrote:

I'm using the expression below to figure out the Average Number of days in
custody from said field. I'm getting a 0 value in the report. Some of the
data has Null values depending on the case; is this why I'm getting the 0?
Need all the help I can get.
=Avg([# of days in Custody])

Mr. Null & Void

When I do Sum I get 0? When I do Count I get 2 = # of records with non-Null
values. When I do =Avg(Nz([# of days in Custody],0)) I also get 0? I
appreciate your time. Thanks.

"Andy Hull" wrote:

Hi Nick

The nulls won't be included in the average so they won't cause the zero
although you should decide whether to convert the nulls to zeroes themselves
if you want them included in the average calculation.

Eg Avg of null, 3 and 9 = 12 / 2 = 6
Avg of 0, 3 and 9 = 12 / 3 = 4

Is it possible that the Avg is very small and you are seeing it rounded?
Try changing the format so you can see decimal places.

Also, for testing only, add a Sum of Days in Custody and a Count of Days in
Custody
This should show you what numbers the Avg calculation is using.

Hope this helps

Andy Hull


"Nick CWT" wrote:

I'm using the expression below to figure out the Average Number of days in
custody from said field. I'm getting a 0 value in the report. Some of the
data has Null values depending on the case; is this why I'm getting the 0?
Need all the help I can get.
=Avg([# of days in Custody])

Mr. Null & Void

Pardon me, but the aggregate functions ignore nulls in the calculations. So
I doubt that having null in a field will break the calculation of an
average.

Avg([Some Field]) should return a number or null if all the field values
that are being aggregated are null.

What type of field or value is [# of days in Custody]? Is it a field from a
table or is it a calculation? If it is a calculated column in a query has
it been formatted or does it use NZ in the query to ensure it returns a
value?

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

"Klatuu" wrote in message
...
The Null value is cause the problem. You can use the Nz function to
prevent
this. When you use the Nz function, you must put each element of the
calculation that could possibly be null in a function, not the results for
example, assume x = Null

Nz(x + 10, 0) will return 0
Nz(x, 0) + 10 will return 10

=Avg(Nz([# of days in Custody],0))

BTW, # of days in Custody breaks almost every naming rule there is.

Use only letters, numbers, and the underscore in names.
Do not use spaces or any special character other than the underscore.
Do not use any Access reserved words (Date, Value, Description, etc)

Typical database naming stanards dictate all upper case with words
separated
with the underscore, so a good name would be:
DAYS_IN_CUSTODY

--
Dave Hargis, Microsoft Access MVP


"Nick CWT" wrote:

I'm using the expression below to figure out the Average Number of days
in
custody from said field. I'm getting a 0 value in the report. Some of
the
data has Null values depending on the case; is this why I'm getting the
0?
Need all the help I can get.
=Avg([# of days in Custody])

Mr. Null & Void

Below is the calculation in the field that I want to avg. in the report.
=DateDiff("d",[Date of Placement],[Date child(ren) returned home])
I'm still getting 0. I appreciate your time and help in this matter.

"John Spencer" wrote:

Pardon me, but the aggregate functions ignore nulls in the calculations. So
I doubt that having null in a field will break the calculation of an
average.

Avg([Some Field]) should return a number or null if all the field values
that are being aggregated are null.

What type of field or value is [# of days in Custody]? Is it a field from a
table or is it a calculation? If it is a calculated column in a query has
it been formatted or does it use NZ in the query to ensure it returns a
value?

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

"Klatuu" wrote in message
...
The Null value is cause the problem. You can use the Nz function to
prevent
this. When you use the Nz function, you must put each element of the
calculation that could possibly be null in a function, not the results for
example, assume x = Null

Nz(x + 10, 0) will return 0
Nz(x, 0) + 10 will return 10

=Avg(Nz([# of days in Custody],0))

BTW, # of days in Custody breaks almost every naming rule there is.

Use only letters, numbers, and the underscore in names.
Do not use spaces or any special character other than the underscore.
Do not use any Access reserved words (Date, Value, Description, etc)

Typical database naming stanards dictate all upper case with words
separated
with the underscore, so a good name would be:
DAYS_IN_CUSTODY

--
Dave Hargis, Microsoft Access MVP


"Nick CWT" wrote:

I'm using the expression below to figure out the Average Number of days
in
custody from said field. I'm getting a 0 value in the report. Some of
the
data has Null values depending on the case; is this why I'm getting the
0?
Need all the help I can get.
=Avg([# of days in Custody])

Mr. Null & Void

Hi again Nick

OK, now we know the sum is 0 then the avg will be 0 too.

I would run a test query showing the 2 dates and the datediff calculation
just so you get a feel for the results that are being averaged.

Just to reinforce, as stated by John, the presence of a null won't cause the
whole avg to be zero. Although, once you have solved the "zero problem", you
will need to decide whether to convert the null datediffs to zeroes and that
depends on what you want from the data.

Firstly, why is the sum = 0? Scan the results of the test query.

There are 2 possibilities...

1: The 2 dates on each row are on the same day so their difference (in days)
is zero thus giving a sum and avg of zero.

2: You have positive datediffs and negative datediffs like...

null to 12 May 2007 = null
10 May 2007 to 15 May 2007 = +5
12 May 2007 to 14 May 2007 = +2
18 May 2007 to 11 May 2007 = -7

Avg = (+5 +2 -7) / 3 = 0 / 3 = 0

If this is the case, presumably the negatives are wrong and the data itself
needs to be corrected but, again, you know the data and the purpose so that
is your call.

Note: I also originally thought that maybe there aren't any rows where both
dates are filled in. If this is true then all your datediffs will be null.
The calculation would then be...

Avg = Sum(all Nulls) / Count(all Nulls)
Avg = Null / 0
Avg = Null

But we know this isn't the case as you are getting Avg = 0 (not Null).

Personal opinion re any null dates: I am seeing your 2 dates as a start and
so if 1 or both are missing a duration can not be calculated. I wouldn't want
to include these in my average so, because they are already being excluded, I
would leave the current calculation as it is. But you know the data and the
purpose so that's your call.

Hope this helps
Regards

Andy Hull


"Nick CWT" wrote:

When I do Sum I get 0? When I do Count I get 2 = # of records with non-Null
values. When I do =Avg(Nz([# of days in Custody],0)) I also get 0? I
appreciate your time. Thanks.

"Andy Hull" wrote:

Hi Nick

The nulls won't be included in the average so they won't cause the zero
although you should decide whether to convert the nulls to zeroes themselves
if you want them included in the average calculation.

Eg Avg of null, 3 and 9 = 12 / 2 = 6
Avg of 0, 3 and 9 = 12 / 3 = 4

Is it possible that the Avg is very small and you are seeing it rounded?
Try changing the format so you can see decimal places.

Also, for testing only, add a Sum of Days in Custody and a Count of Days in
Custody
This should show you what numbers the Avg calculation is using.

Hope this helps

Andy Hull


"Nick CWT" wrote:

I'm using the expression below to figure out the Average Number of days in
custody from said field. I'm getting a 0 value in the report. Some of the
data has Null values depending on the case; is this why I'm getting the 0?
Need all the help I can get.
=Avg([# of days in Custody])

Mr. Null & Void

Try the following

=Avg(DateDiff("d",[Date of Placement],[Date child(ren) returned home]))

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

"Nick CWT" wrote in message
...
Below is the calculation in the field that I want to avg. in the report.
=DateDiff("d",[Date of Placement],[Date child(ren) returned home])
I'm still getting 0. I appreciate your time and help in this matter.

"John Spencer" wrote:

Pardon me, but the aggregate functions ignore nulls in the calculations.
So
I doubt that having null in a field will break the calculation of an
average.

Avg([Some Field]) should return a number or null if all the field values
that are being aggregated are null.

What type of field or value is [# of days in Custody]? Is it a field
from a
table or is it a calculation? If it is a calculated column in a query
has
it been formatted or does it use NZ in the query to ensure it returns a
value?

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

"Klatuu" wrote in message
...
The Null value is cause the problem. You can use the Nz function to
prevent
this. When you use the Nz function, you must put each element of the
calculation that could possibly be null in a function, not the results
for
example, assume x = Null

Nz(x + 10, 0) will return 0
Nz(x, 0) + 10 will return 10

=Avg(Nz([# of days in Custody],0))

BTW, # of days in Custody breaks almost every naming rule there is.

Use only letters, numbers, and the underscore in names.
Do not use spaces or any special character other than the underscore.
Do not use any Access reserved words (Date, Value, Description, etc)

Typical database naming stanards dictate all upper case with words
separated
with the underscore, so a good name would be:
DAYS_IN_CUSTODY

--
Dave Hargis, Microsoft Access MVP


"Nick CWT" wrote:

I'm using the expression below to figure out the Average Number of
days
in
custody from said field. I'm getting a 0 value in the report. Some
of
the
data has Null values depending on the case; is this why I'm getting
the
0?
Need all the help I can get.
=Avg([# of days in Custody])

Mr. Null & Void

You da Man!!! That did it. Thanks John.

"John Spencer" wrote:

Try the following

=Avg(DateDiff("d",[Date of Placement],[Date child(ren) returned home]))

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

"Nick CWT" wrote in message
...
Below is the calculation in the field that I want to avg. in the report.
=DateDiff("d",[Date of Placement],[Date child(ren) returned home])
I'm still getting 0. I appreciate your time and help in this matter.

"John Spencer" wrote:

Pardon me, but the aggregate functions ignore nulls in the calculations.
So
I doubt that having null in a field will break the calculation of an
average.

Avg([Some Field]) should return a number or null if all the field values
that are being aggregated are null.

What type of field or value is [# of days in Custody]? Is it a field
from a
table or is it a calculation? If it is a calculated column in a query
has
it been formatted or does it use NZ in the query to ensure it returns a
value?

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

"Klatuu" wrote in message
...
The Null value is cause the problem. You can use the Nz function to
prevent
this. When you use the Nz function, you must put each element of the
calculation that could possibly be null in a function, not the results
for
example, assume x = Null

Nz(x + 10, 0) will return 0
Nz(x, 0) + 10 will return 10

=Avg(Nz([# of days in Custody],0))

BTW, # of days in Custody breaks almost every naming rule there is.

Use only letters, numbers, and the underscore in names.
Do not use spaces or any special character other than the underscore.
Do not use any Access reserved words (Date, Value, Description, etc)

Typical database naming stanards dictate all upper case with words
separated
with the underscore, so a good name would be:
DAYS_IN_CUSTODY

--
Dave Hargis, Microsoft Access MVP


"Nick CWT" wrote:

I'm using the expression below to figure out the Average Number of
days
in
custody from said field. I'm getting a 0 value in the report. Some
of
the
data has Null values depending on the case; is this why I'm getting
the
0?
Need all the help I can get.
=Avg([# of days in Custody])

Mr. Null & Void