getting a report to recognize a calculated field as a number

  • Thread starter Thread starter Paul James
  • Start date Start date
P

Paul James

I'm trying (in a report field) to obtain the Average of a (calculated) query
field, and I keep getting #Error in the report. The Query field "Days" is
calculated from an expression involving several dates. I've noticed that in
the query datasheet view, the numbers in that Days column are
left-justified, implying that the query thinks they're text values.

I've tried calculating the Average number of Days in my report field using
expressions like

=Avg(txtDays),
=Avg([txtDays]), and
=Avg(CInt([txtDays])

but to no avail.

So how can I
a) get the query to realize that the calculated field "Days" is an
integer, and
b) calculate it's average value in a report field?

Thanks in advance,

Paul
 
Paul said:
I'm trying (in a report field) to obtain the Average of a (calculated) query
field, and I keep getting #Error in the report. The Query field "Days" is
calculated from an expression involving several dates. I've noticed that in
the query datasheet view, the numbers in that Days column are
left-justified, implying that the query thinks they're text values.

I've tried calculating the Average number of Days in my report field using
expressions like

=Avg(txtDays),
=Avg([txtDays]), and
=Avg(CInt([txtDays])

but to no avail.

So how can I
a) get the query to realize that the calculated field "Days" is an
integer, and
b) calculate it's average value in a report field?
Click to expand...

Try using:
=CInt(Avg(txtDays))
or
=Val(Avg(txtDays))
 
Try using:
=CInt(Avg(txtDays))
or
=Val(Avg(txtDays))
Click to expand...

Both of those expression gave me a Data Type Mismatch, so I figured you
unintentionally reversed the functions, because

=Avg(Val([txtDays]))

worked fine.

However, I still got a Data Type Mismatch with

=CInt(Avg([txtDays])).

But since the Avg(Val([txtDays])) works, you've solved my immediate need.

Thanks, Marsh
 
Paul said:
Try using:
=CInt(Avg(txtDays))
or
=Val(Avg(txtDays))
Click to expand...

Both of those expression gave me a Data Type Mismatch, so I figured you
unintentionally reversed the functions, because

=Avg(Val([txtDays]))

worked fine.

However, I still got a Data Type Mismatch with

=CInt(Avg([txtDays])).

But since the Avg(Val([txtDays])) works, you've solved my immediate need.
Click to expand...


Yes I did reverse both of them.

You should probably determine how the field values got
converted to text.

Actually, now that I think about it more deeply, you should
use Int, since it deals with Nulls.

=Avg(Int(txtDays))
 
Back
Top