Show 0 for null

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database that is being used to calculate monthly evaluations for the
boys in our program. The report is being generated from a query that pulls
data from three seperate queries.
The main query is named: qryTrial
The 3 queries that make up qryTrial are: qryStuAvgScoreByMonth;
qryLastMonthStuAvgScoreByMonth; qryStandardDeviation

qryStuAvgScoreByMonth gives the average score for the current
qryLastMonthStuAvgScoreByMonth gives the average score for the prior month.
qryStandardDeviation gives the Standard Deviation above and below the
overall average by month.

The report works great unless a boy doesn't have a prior months evaluation.
This only occurs in a boys first month in our program. How can I have the
values for qryLastMonthStuAvgScoreByMonth = 0 if the field is null?

When I run the report I only get the values for those boys with Evaluations
from the current month and the prior month. If both sets of numbers are not
present the report doesn't include them.

Are there any ideas as to how this can be fixed?
 
You can put it in the query

Select Nz(FieldName,0) As NewFieldName From TableName

Or in the control source of a field in the report/form
=Nz(FieldName,0)
 
This is what I don't understand. I have values being pulled from two
different queries. For example:
BoysName: Johnny
EvalDate: 11/11/05
ObedienceScore: 4.5

BoysName: Johnny
EvalDate: 12/30/05
ObedienceScore: 5

If I have all of this data then everything works perfectly. I can get the
data from the current month (12) and the last month (11). However, this
example won't work:

BoysName: Johnny
EvalDate: 12/15/05
ObedienceScore: 3

Without a previous months score the current months score won't show up on
the report. This has something to do with the query. When I run my query for
hte current months scores, only those boys with scores for the current and
the previous month show up. How can I fix this?
 
Back
Top