DAvg with criteria between

  • Thread starter Thread starter dnr
  • Start date Start date
D

dnr

I am trying to average sets of data but I only want to average data with
values between 0 and 3000. Here is my code:

Average.Value = DAvg(fldtosearch, "Process Data", "[fldtosearch] Is between"
" & 0 & " and " & 3000 & "")

I keep getting a compile error.

I tried using code the following code to limit data values to less than 3000
and that worked:

Average.Value = DAvg(fldtosearch, "Process Data", [fldtosearch] & "<3000")

please help me understand how to limit data to greater then 0 and less then
3000.
 
Just a syntax problem.

Me.Average = DAvg(fldtosearch, "Process Data", "[fldtosearch] BETWEEN 0
AND 3000 AND [fldtosearch] IS NOT NULL")

Notes
Always qualify your controls
You don't need to use the Value property, it is the default
Average is not a good control name
You need to be sure you don't include records with Null values.
 
Thanks for the response. I changed the name of my control to ctlavg and put
in the following:

Me.ctlavg = DAvg(fldtosearch, "Process Data", "fldtosearch BETWEEN 0 AND
3000 AND fldtosearch IS NOT NULL")


I am getting a run-time error that says Data type mismatch in criteria
expression. Do I need to clarify something as a numeric value? The table
the data is retrieved from the field is numeric double.

Thanks


Klatuu said:
Just a syntax problem.

Me.Average = DAvg(fldtosearch, "Process Data", "[fldtosearch] BETWEEN 0
AND 3000 AND [fldtosearch] IS NOT NULL")

Notes
Always qualify your controls
You don't need to use the Value property, it is the default
Average is not a good control name
You need to be sure you don't include records with Null values.

--
Dave Hargis, Microsoft Access MVP


dnr said:
I am trying to average sets of data but I only want to average data with
values between 0 and 3000. Here is my code:

Average.Value = DAvg(fldtosearch, "Process Data", "[fldtosearch] Is between"
" & 0 & " and " & 3000 & "")

I keep getting a compile error.

I tried using code the following code to limit data values to less than 3000
and that worked:

Average.Value = DAvg(fldtosearch, "Process Data", [fldtosearch] & "<3000")

please help me understand how to limit data to greater then 0 and less then
3000.
Click to expand...
Click to expand...
 
Is fldToSearch a string that contains the name of a field? If so,

Me.ctlavg = DAvg(fldtosearch, "Process Data"
, fldtosearch & " BETWEEN 0 AND 3000")

If fldToSearch is the actual name then

Me.ctlavg = DAvg("fldtosearch", "Process Data"
, "fldtosearch BETWEEN 0 AND 3000")

By the way you don't need the is not null criteria. Null is by
definition not known to be between zero and 3000.

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

Thanks for the response. I changed the name of my control to ctlavg and put
in the following:

Me.ctlavg = DAvg(fldtosearch, "Process Data", "fldtosearch BETWEEN 0 AND
3000 AND fldtosearch IS NOT NULL")


I am getting a run-time error that says Data type mismatch in criteria
expression. Do I need to clarify something as a numeric value? The table
the data is retrieved from the field is numeric double.

Thanks


Klatuu said:
Just a syntax problem.

Me.Average = DAvg(fldtosearch, "Process Data", "[fldtosearch] BETWEEN 0
AND 3000 AND [fldtosearch] IS NOT NULL")

Notes
Always qualify your controls
You don't need to use the Value property, it is the default
Average is not a good control name
You need to be sure you don't include records with Null values.

--
Dave Hargis, Microsoft Access MVP


dnr said:
I am trying to average sets of data but I only want to average data with
values between 0 and 3000. Here is my code:

Average.Value = DAvg(fldtosearch, "Process Data", "[fldtosearch] Is between"
" & 0 & " and " & 3000 & "")

I keep getting a compile error.

I tried using code the following code to limit data values to less than 3000
and that worked:

Average.Value = DAvg(fldtosearch, "Process Data", [fldtosearch] & "<3000")

please help me understand how to limit data to greater then 0 and less then
3000.
Click to expand...
Click to expand...
Click to expand...
 
Are you certain about that, John?
Wouldn't Null values skew the results of the averaging?
--
Dave Hargis, Microsoft Access MVP


John Spencer said:
Is fldToSearch a string that contains the name of a field? If so,

Me.ctlavg = DAvg(fldtosearch, "Process Data"
, fldtosearch & " BETWEEN 0 AND 3000")

If fldToSearch is the actual name then

Me.ctlavg = DAvg("fldtosearch", "Process Data"
, "fldtosearch BETWEEN 0 AND 3000")

By the way you don't need the is not null criteria. Null is by
definition not known to be between zero and 3000.

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

Thanks for the response. I changed the name of my control to ctlavg and put
in the following:

Me.ctlavg = DAvg(fldtosearch, "Process Data", "fldtosearch BETWEEN 0 AND
3000 AND fldtosearch IS NOT NULL")


I am getting a run-time error that says Data type mismatch in criteria
expression. Do I need to clarify something as a numeric value? The table
the data is retrieved from the field is numeric double.

Thanks


Klatuu said:
Just a syntax problem.

Me.Average = DAvg(fldtosearch, "Process Data", "[fldtosearch] BETWEEN 0
AND 3000 AND [fldtosearch] IS NOT NULL")

Notes
Always qualify your controls
You don't need to use the Value property, it is the default
Average is not a good control name
You need to be sure you don't include records with Null values.

--
Dave Hargis, Microsoft Access MVP


:

I am trying to average sets of data but I only want to average data with
values between 0 and 3000. Here is my code:

Average.Value = DAvg(fldtosearch, "Process Data", "[fldtosearch] Is between"
" & 0 & " and " & 3000 & "")

I keep getting a compile error.

I tried using code the following code to limit data values to less than 3000
and that worked:

Average.Value = DAvg(fldtosearch, "Process Data", [fldtosearch] & "<3000")

please help me understand how to limit data to greater then 0 and less then
3000.
Click to expand...
Click to expand...
Click to expand...
 
Yes, I'm sure.

Count, Average, Sum, Min, and Max all ignore nulls in the calculation
DAvg, DSum, Dmin, DMax, and DCount also ignore nulls in the calculation

If you want Nulls to be handled as zero or any other value you can wrap the
field in an NZ Function to force them to be included in the calculation

And further if you say
Fieldx Between 0 and 3000
then whereever fieldx is null the record won't be returned.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
I know it ignores them, John and I know how to use Nz.
What I am not sure about is how it ignores Null.
For example, assume your recordset has 300 records of which 5 have Null
values. Will it average with a base of 300 or a base of 295?
 
Back
Top