Syntax for nulls in query

[Guest]

I have a query that selects from a group of clinicians, then averages their
ratings on certain variables by quarter. If I want to properly handle null
values (not all clinicians are rated every quarter), I've tried both the Nz
and the IIf(IsNull) functions in the query to no avail. Using Nz I've said:
RatingToDisplay = Nz(Rating Assigned, 0) or RatingToDisplay = Nz(Rating
Assigned, " "). Have also tried: IIf(IsNull([Rating Assigned]),0,[Rating
Assigned]). The latter requests a parameter. The former returns only values
of 1 or zero.

Suggestions?

Many thanks,

Meredith

 

[Marshall Barton]

I have a query that selects from a group of clinicians, then averages their
ratings on certain variables by quarter. If I want to properly handle null
values (not all clinicians are rated every quarter), I've tried both the Nz
and the IIf(IsNull) functions in the query to no avail. Using Nz I've said:
RatingToDisplay = Nz(Rating Assigned, 0) or RatingToDisplay = Nz(Rating
Assigned, " "). Have also tried: IIf(IsNull([Rating Assigned]),0,[Rating
Assigned]). The latter requests a parameter. The former returns only values
of 1 or zero.

Any field (or control) name that contains a space or any
other funky character must be enclosed in square brackets:

Nz([Rating Assigned], 0)

If you are calculating an average on that, you probably are
much better off leaving the Null values alone. Otherwise,
your average will be dragged down by and zeros. Tha
aggregate functions (Count, Sum, Ave, etc) all ignore Nulls
to avoid that issue.

 

[Guest]

I would leave them alone, but when I try to run a report based on the query,
it won't run if there are any of the quarters with null values -- so I'm
assuming I need to deal with them at the query level?

I have a query that selects from a group of clinicians, then averages their
ratings on certain variables by quarter. If I want to properly handle null
values (not all clinicians are rated every quarter), I've tried both the Nz
and the IIf(IsNull) functions in the query to no avail. Using Nz I've said:
RatingToDisplay = Nz(Rating Assigned, 0) or RatingToDisplay = Nz(Rating
Assigned, " "). Have also tried: IIf(IsNull([Rating Assigned]),0,[Rating
Assigned]). The latter requests a parameter. The former returns only values
of 1 or zero.

Any field (or control) name that contains a space or any
other funky character must be enclosed in square brackets:

Nz([Rating Assigned], 0)

If you are calculating an average on that, you probably are
much better off leaving the Null values alone. Otherwise,
your average will be dragged down by and zeros. Tha
aggregate functions (Count, Sum, Ave, etc) all ignore Nulls
to avoid that issue.

 

[Marshall Barton]

I don't understand what you mean by "it won't run if there
are any of the quarters with null values". That definitely
does not sound right to me.

Regardless of that, you can use the Nz function anywhere you
need a zero instead of a potential Null. In a VBA procedure
upi would just use Nz(Me.[Rating Assigned], 0) instead of
[Rating Assigned]. In a text box's control source
enpression, you would use =Nz([Rating Assigned], 0) instead
of binding the text box directly to the [Rating Assigned]
field.
--
Marsh
MVP [MS Access]

I would leave them alone, but when I try to run a report based on the query,
it won't run if there are any of the quarters with null values -- so I'm
assuming I need to deal with them at the query level?

I have a query that selects from a group of clinicians, then averages their
ratings on certain variables by quarter. If I want to properly handle null
values (not all clinicians are rated every quarter), I've tried both the Nz
and the IIf(IsNull) functions in the query to no avail. Using Nz I've said:
RatingToDisplay = Nz(Rating Assigned, 0) or RatingToDisplay = Nz(Rating
Assigned, " "). Have also tried: IIf(IsNull([Rating Assigned]),0,[Rating
Assigned]). The latter requests a parameter. The former returns only values
of 1 or zero.

Any field (or control) name that contains a space or any
other funky character must be enclosed in square brackets:

Nz([Rating Assigned], 0)

If you are calculating an average on that, you probably are
much better off leaving the Null values alone. Otherwise,
your average will be dragged down by and zeros. Tha
aggregate functions (Count, Sum, Ave, etc) all ignore Nulls
to avoid that issue.

 

[Guest]

thank you. I'm still wrestling with the correct syntax for the Nz function.
When I try to insert it into a query field I end up with a parameter request
-- so I'm still not sure what's happening there.

What I meant by the report not running if there are null values is that, if
I run the report based on the query specifying a clinician who, say, only had
ratings for one quarter out of four, the report won't run at all and gives an
error message to the effect that it doesn't recognize the variables ... It
will run for specified clinicians who have ratings in all 4 quarters which is
why I assumed the null values are the problem. Could be something else
entirely.

Thank you for your help with this; I'm a novice and somewhat in over my
head. However, there's no other way to do it than this, right?

Meredith

I don't understand what you mean by "it won't run if there
are any of the quarters with null values". That definitely
does not sound right to me.

Regardless of that, you can use the Nz function anywhere you
need a zero instead of a potential Null. In a VBA procedure
upi would just use Nz(Me.[Rating Assigned], 0) instead of
[Rating Assigned]. In a text box's control source
enpression, you would use =Nz([Rating Assigned], 0) instead
of binding the text box directly to the [Rating Assigned]
field.
--
Marsh
MVP [MS Access]

I would leave them alone, but when I try to run a report based on the query,
it won't run if there are any of the quarters with null values -- so I'm
assuming I need to deal with them at the query level?

MeredithS wrote:
I have a query that selects from a group of clinicians, then averages their
ratings on certain variables by quarter. If I want to properly handle null
values (not all clinicians are rated every quarter), I've tried both the Nz
and the IIf(IsNull) functions in the query to no avail. Using Nz I've said:
RatingToDisplay = Nz(Rating Assigned, 0) or RatingToDisplay = Nz(Rating
Assigned, " "). Have also tried: IIf(IsNull([Rating Assigned]),0,[Rating
Assigned]). The latter requests a parameter. The former returns only values
of 1 or zero.

Any field (or control) name that contains a space or any
other funky character must be enclosed in square brackets:

Nz([Rating Assigned], 0)

If you are calculating an average on that, you probably are
much better off leaving the Null values alone. Otherwise,
your average will be dragged down by and zeros. Tha
aggregate functions (Count, Sum, Ave, etc) all ignore Nulls
to avoid that issue.

 

[Marshall Barton]

A parameter prompt means that you used a name that is not a
field in the query's table(s). The prompt tells you exactly
what name it can not resolve.

I'm sorry, but I have no idea what your report does or does
not do when "a clinician only had ratings for one quarter
out of four". The report must do something even if it only
generates an error message.

If the query is a crosstab query, then you should set the
query's ColumnHeadings property to all the quarters so the
field exists even if there is no data.

Mayve if you could provide more specific information along
with a Copy/Paste of the query's SQL view, I might be able
to spot something.

As for "other ways", I can't say because I just don't know
enough about your report.
--
Marsh
MVP [MS Access]

thank you. I'm still wrestling with the correct syntax for the Nz function.
When I try to insert it into a query field I end up with a parameter request
-- so I'm still not sure what's happening there.

What I meant by the report not running if there are null values is that, if
I run the report based on the query specifying a clinician who, say, only had
ratings for one quarter out of four, the report won't run at all and gives an
error message to the effect that it doesn't recognize the variables ... It
will run for specified clinicians who have ratings in all 4 quarters which is
why I assumed the null values are the problem. Could be something else
entirely.

Thank you for your help with this; I'm a novice and somewhat in over my
head. However, there's no other way to do it than this, right?

I don't understand what you mean by "it won't run if there
are any of the quarters with null values". That definitely
does not sound right to me.

Regardless of that, you can use the Nz function anywhere you
need a zero instead of a potential Null. In a VBA procedure
upi would just use Nz(Me.[Rating Assigned], 0) instead of
[Rating Assigned]. In a text box's control source
enpression, you would use =Nz([Rating Assigned], 0) instead
of binding the text box directly to the [Rating Assigned]
field.
--
Marsh
MVP [MS Access]

I would leave them alone, but when I try to run a report based on the query,
it won't run if there are any of the quarters with null values -- so I'm
assuming I need to deal with them at the query level?


MeredithS wrote:
I have a query that selects from a group of clinicians, then averages their
ratings on certain variables by quarter. If I want to properly handle null
values (not all clinicians are rated every quarter), I've tried both the Nz
and the IIf(IsNull) functions in the query to no avail. Using Nz I've said:
RatingToDisplay = Nz(Rating Assigned, 0) or RatingToDisplay = Nz(Rating
Assigned, " "). Have also tried: IIf(IsNull([Rating Assigned]),0,[Rating
Assigned]). The latter requests a parameter. The former returns only values
of 1 or zero.


:
Any field (or control) name that contains a space or any
other funky character must be enclosed in square brackets:

Nz([Rating Assigned], 0)

If you are calculating an average on that, you probably are
much better off leaving the Null values alone. Otherwise,
your average will be dragged down by and zeros. Tha
aggregate functions (Count, Sum, Ave, etc) all ignore Nulls
to avoid that issue.

 

[Guest]

I will paste and post syntax Monday when I get back to work because I don't
have it at home, but ... the crosstab query is set up as you suggest, but
what happens when I try to run a very simple report from it, I get a message
to the effect (not exact syntax here) that "Access does not recognize
Variable Q3," for example. The report won't go any further than that. There
are different messages depending on which clinician's data is in the
underlying query, but basically the report is always returning a message that
it doesn't recognize the variables in the query. I don't understand that
because I'm creating the report from a wizard and referencing the query as
data source. I just assumed that since it couldn't find/recognize certain
variables, it must have something to do with null data but it could be a
completely different issue.

The underlying crosstab query is based on a table. There are no other
detours -- I create the query from the table. The query runs fine by itself.
I create the report from the query using the wizard. The report won't run.
I'm not sure where the problem is; do you have any thoughts?

Which syntax would you prefer to see Monday (if you still respond to this)?
The query or the report?

Thank you so much. I know this can be done; it seems like such a simple
project!

Meredith

A parameter prompt means that you used a name that is not a
field in the query's table(s). The prompt tells you exactly
what name it can not resolve.

I'm sorry, but I have no idea what your report does or does
not do when "a clinician only had ratings for one quarter
out of four". The report must do something even if it only
generates an error message.

If the query is a crosstab query, then you should set the
query's ColumnHeadings property to all the quarters so the
field exists even if there is no data.

Mayve if you could provide more specific information along
with a Copy/Paste of the query's SQL view, I might be able
to spot something.

As for "other ways", I can't say because I just don't know
enough about your report.
--
Marsh
MVP [MS Access]

thank you. I'm still wrestling with the correct syntax for the Nz function.
When I try to insert it into a query field I end up with a parameter request
-- so I'm still not sure what's happening there.

What I meant by the report not running if there are null values is that, if
I run the report based on the query specifying a clinician who, say, only had
ratings for one quarter out of four, the report won't run at all and gives an
error message to the effect that it doesn't recognize the variables ... It
will run for specified clinicians who have ratings in all 4 quarters which is
why I assumed the null values are the problem. Could be something else
entirely.

Thank you for your help with this; I'm a novice and somewhat in over my
head. However, there's no other way to do it than this, right?

I don't understand what you mean by "it won't run if there
are any of the quarters with null values". That definitely
does not sound right to me.

Regardless of that, you can use the Nz function anywhere you
need a zero instead of a potential Null. In a VBA procedure
upi would just use Nz(Me.[Rating Assigned], 0) instead of
[Rating Assigned]. In a text box's control source
enpression, you would use =Nz([Rating Assigned], 0) instead
of binding the text box directly to the [Rating Assigned]
field.
--
Marsh
MVP [MS Access]


MeredithS wrote:
I would leave them alone, but when I try to run a report based on the query,
it won't run if there are any of the quarters with null values -- so I'm
assuming I need to deal with them at the query level?


MeredithS wrote:
I have a query that selects from a group of clinicians, then averages their
ratings on certain variables by quarter. If I want to properly handle null
values (not all clinicians are rated every quarter), I've tried both the Nz
and the IIf(IsNull) functions in the query to no avail. Using Nz I've said:
RatingToDisplay = Nz(Rating Assigned, 0) or RatingToDisplay = Nz(Rating
Assigned, " "). Have also tried: IIf(IsNull([Rating Assigned]),0,[Rating
Assigned]). The latter requests a parameter. The former returns only values
of 1 or zero.


:
Any field (or control) name that contains a space or any
other funky character must be enclosed in square brackets:

Nz([Rating Assigned], 0)

If you are calculating an average on that, you probably are
much better off leaving the Null values alone. Otherwise,
your average will be dragged down by and zeros. Tha
aggregate functions (Count, Sum, Ave, etc) all ignore Nulls
to avoid that issue.

 

[Marshall Barton]

Ahhh, so it is a crosstab query. Now I'm almost certain
that setting the query's ColumnHeadings property to all the
names you expect in the quarter(?) field will resolve this
issue. From hints in your messages I suspect that the
ColumnHeadings should be set to
Q1,Q2,Q3,Q4
--
Marsh
MVP [MS Access]

I will paste and post syntax Monday when I get back to work because I don't
have it at home, but ... the crosstab query is set up as you suggest, but
what happens when I try to run a very simple report from it, I get a message
to the effect (not exact syntax here) that "Access does not recognize
Variable Q3," for example. The report won't go any further than that. There
are different messages depending on which clinician's data is in the
underlying query, but basically the report is always returning a message that
it doesn't recognize the variables in the query. I don't understand that
because I'm creating the report from a wizard and referencing the query as
data source. I just assumed that since it couldn't find/recognize certain
variables, it must have something to do with null data but it could be a
completely different issue.

The underlying crosstab query is based on a table. There are no other
detours -- I create the query from the table. The query runs fine by itself.
I create the report from the query using the wizard. The report won't run.
I'm not sure where the problem is; do you have any thoughts?

Which syntax would you prefer to see Monday (if you still respond to this)?
The query or the report?

A parameter prompt means that you used a name that is not a
field in the query's table(s). The prompt tells you exactly
what name it can not resolve.

I'm sorry, but I have no idea what your report does or does
not do when "a clinician only had ratings for one quarter
out of four". The report must do something even if it only
generates an error message.

If the query is a crosstab query, then you should set the
query's ColumnHeadings property to all the quarters so the
field exists even if there is no data.

Mayve if you could provide more specific information along
with a Copy/Paste of the query's SQL view, I might be able
to spot something.

As for "other ways", I can't say because I just don't know
enough about your report.

thank you. I'm still wrestling with the correct syntax for the Nz function.
When I try to insert it into a query field I end up with a parameter request
-- so I'm still not sure what's happening there.

What I meant by the report not running if there are null values is that, if
I run the report based on the query specifying a clinician who, say, only had
ratings for one quarter out of four, the report won't run at all and gives an
error message to the effect that it doesn't recognize the variables ... It
will run for specified clinicians who have ratings in all 4 quarters which is
why I assumed the null values are the problem. Could be something else
entirely.

Thank you for your help with this; I'm a novice and somewhat in over my
head. However, there's no other way to do it than this, right?


:
I don't understand what you mean by "it won't run if there
are any of the quarters with null values". That definitely
does not sound right to me.

Regardless of that, you can use the Nz function anywhere you
need a zero instead of a potential Null. In a VBA procedure
upi would just use Nz(Me.[Rating Assigned], 0) instead of
[Rating Assigned]. In a text box's control source
enpression, you would use =Nz([Rating Assigned], 0) instead
of binding the text box directly to the [Rating Assigned]
field.


MeredithS wrote:
I would leave them alone, but when I try to run a report based on the query,
it won't run if there are any of the quarters with null values -- so I'm
assuming I need to deal with them at the query level?


MeredithS wrote:
I have a query that selects from a group of clinicians, then averages their
ratings on certain variables by quarter. If I want to properly handle null
values (not all clinicians are rated every quarter), I've tried both the Nz
and the IIf(IsNull) functions in the query to no avail. Using Nz I've said:
RatingToDisplay = Nz(Rating Assigned, 0) or RatingToDisplay = Nz(Rating
Assigned, " "). Have also tried: IIf(IsNull([Rating Assigned]),0,[Rating
Assigned]). The latter requests a parameter. The former returns only values
of 1 or zero.


:
Any field (or control) name that contains a space or any
other funky character must be enclosed in square brackets:

Nz([Rating Assigned], 0)

If you are calculating an average on that, you probably are
much better off leaving the Null values alone. Otherwise,
your average will be dragged down by and zeros. Tha
aggregate functions (Count, Sum, Ave, etc) all ignore Nulls
to avoid that issue.

 

[Guest]

Where is the ColumnHeads property found in the query design? I have
designated the DateAssigned(formatted to quarters) as the column heading for
the crosstab query; why wouldn't that pick up all the quarters? Particularly,
since when I run the query by itself, any quarters that contain values
(ratings) are displayed. And, no error messages ... ?

Thanks,

Meredith

Ahhh, so it is a crosstab query. Now I'm almost certain
that setting the query's ColumnHeadings property to all the
names you expect in the quarter(?) field will resolve this
issue. From hints in your messages I suspect that the
ColumnHeadings should be set to
Q1,Q2,Q3,Q4
--
Marsh
MVP [MS Access]

I will paste and post syntax Monday when I get back to work because I don't
have it at home, but ... the crosstab query is set up as you suggest, but
what happens when I try to run a very simple report from it, I get a message
to the effect (not exact syntax here) that "Access does not recognize
Variable Q3," for example. The report won't go any further than that. There
are different messages depending on which clinician's data is in the
underlying query, but basically the report is always returning a message that
it doesn't recognize the variables in the query. I don't understand that
because I'm creating the report from a wizard and referencing the query as
data source. I just assumed that since it couldn't find/recognize certain
variables, it must have something to do with null data but it could be a
completely different issue.

The underlying crosstab query is based on a table. There are no other
detours -- I create the query from the table. The query runs fine by itself.
I create the report from the query using the wizard. The report won't run.
I'm not sure where the problem is; do you have any thoughts?

Which syntax would you prefer to see Monday (if you still respond to this)?
The query or the report?

A parameter prompt means that you used a name that is not a
field in the query's table(s). The prompt tells you exactly
what name it can not resolve.

I'm sorry, but I have no idea what your report does or does
not do when "a clinician only had ratings for one quarter
out of four". The report must do something even if it only
generates an error message.

If the query is a crosstab query, then you should set the
query's ColumnHeadings property to all the quarters so the
field exists even if there is no data.

Mayve if you could provide more specific information along
with a Copy/Paste of the query's SQL view, I might be able
to spot something.

As for "other ways", I can't say because I just don't know
enough about your report.


MeredithS wrote:
thank you. I'm still wrestling with the correct syntax for the Nz function.
When I try to insert it into a query field I end up with a parameter request
-- so I'm still not sure what's happening there.

What I meant by the report not running if there are null values is that, if
I run the report based on the query specifying a clinician who, say, only had
ratings for one quarter out of four, the report won't run at all and gives an
error message to the effect that it doesn't recognize the variables ... It
will run for specified clinicians who have ratings in all 4 quarters which is
why I assumed the null values are the problem. Could be something else
entirely.

Thank you for your help with this; I'm a novice and somewhat in over my
head. However, there's no other way to do it than this, right?


:
I don't understand what you mean by "it won't run if there
are any of the quarters with null values". That definitely
does not sound right to me.

Regardless of that, you can use the Nz function anywhere you
need a zero instead of a potential Null. In a VBA procedure
upi would just use Nz(Me.[Rating Assigned], 0) instead of
[Rating Assigned]. In a text box's control source
enpression, you would use =Nz([Rating Assigned], 0) instead
of binding the text box directly to the [Rating Assigned]
field.


MeredithS wrote:
I would leave them alone, but when I try to run a report based on the query,
it won't run if there are any of the quarters with null values -- so I'm
assuming I need to deal with them at the query level?


MeredithS wrote:
I have a query that selects from a group of clinicians, then averages their
ratings on certain variables by quarter. If I want to properly handle null
values (not all clinicians are rated every quarter), I've tried both the Nz
and the IIf(IsNull) functions in the query to no avail. Using Nz I've said:
RatingToDisplay = Nz(Rating Assigned, 0) or RatingToDisplay = Nz(Rating
Assigned, " "). Have also tried: IIf(IsNull([Rating Assigned]),0,[Rating
Assigned]). The latter requests a parameter. The former returns only values
of 1 or zero.


:
Any field (or control) name that contains a space or any
other funky character must be enclosed in square brackets:

Nz([Rating Assigned], 0)

If you are calculating an average on that, you probably are
much better off leaving the Null values alone. Otherwise,
your average will be dragged down by and zeros. Tha
aggregate functions (Count, Sum, Ave, etc) all ignore Nulls
to avoid that issue.

 

[Marshall Barton]

Crosstab queries without any specified values in
ColumnHeadings only return columns that actually have values
in your table's data. The query can not make up these
things, it has to either be told what values you want or it
will just use what it finds in the table.

It's your report that was designed to use all possible
values and fails when the query doesn't have a column for
some values.

Open the query in design view. Then display the properties
sheet (View menu). Click in a blank area in the top part of
the query design window (where the table is, but not on the
table itself), the query's property list will appear.
ColumnHeadings is the third property from the top.
--
Marsh
MVP [MS Access]

Where is the ColumnHeads property found in the query design? I have
designated the DateAssigned(formatted to quarters) as the column heading for
the crosstab query; why wouldn't that pick up all the quarters? Particularly,
since when I run the query by itself, any quarters that contain values
(ratings) are displayed. And, no error messages ... ?

Ahhh, so it is a crosstab query. Now I'm almost certain
that setting the query's ColumnHeadings property to all the
names you expect in the quarter(?) field will resolve this
issue. From hints in your messages I suspect that the
ColumnHeadings should be set to
Q1,Q2,Q3,Q4

I will paste and post syntax Monday when I get back to work because I don't
have it at home, but ... the crosstab query is set up as you suggest, but
what happens when I try to run a very simple report from it, I get a message
to the effect (not exact syntax here) that "Access does not recognize
Variable Q3," for example. The report won't go any further than that. There
are different messages depending on which clinician's data is in the
underlying query, but basically the report is always returning a message that
it doesn't recognize the variables in the query. I don't understand that
because I'm creating the report from a wizard and referencing the query as
data source. I just assumed that since it couldn't find/recognize certain
variables, it must have something to do with null data but it could be a
completely different issue.

The underlying crosstab query is based on a table. There are no other
detours -- I create the query from the table. The query runs fine by itself.
I create the report from the query using the wizard. The report won't run.
I'm not sure where the problem is; do you have any thoughts?

Which syntax would you prefer to see Monday (if you still respond to this)?
The query or the report?


:
A parameter prompt means that you used a name that is not a
field in the query's table(s). The prompt tells you exactly
what name it can not resolve.

I'm sorry, but I have no idea what your report does or does
not do when "a clinician only had ratings for one quarter
out of four". The report must do something even if it only
generates an error message.

If the query is a crosstab query, then you should set the
query's ColumnHeadings property to all the quarters so the
field exists even if there is no data.

Mayve if you could provide more specific information along
with a Copy/Paste of the query's SQL view, I might be able
to spot something.

As for "other ways", I can't say because I just don't know
enough about your report.


MeredithS wrote:
thank you. I'm still wrestling with the correct syntax for the Nz function.
When I try to insert it into a query field I end up with a parameter request
-- so I'm still not sure what's happening there.

What I meant by the report not running if there are null values is that, if
I run the report based on the query specifying a clinician who, say, only had
ratings for one quarter out of four, the report won't run at all and gives an
error message to the effect that it doesn't recognize the variables ... It
will run for specified clinicians who have ratings in all 4 quarters which is
why I assumed the null values are the problem. Could be something else
entirely.

Thank you for your help with this; I'm a novice and somewhat in over my
head. However, there's no other way to do it than this, right?


:
I don't understand what you mean by "it won't run if there
are any of the quarters with null values". That definitely
does not sound right to me.

Regardless of that, you can use the Nz function anywhere you
need a zero instead of a potential Null. In a VBA procedure
upi would just use Nz(Me.[Rating Assigned], 0) instead of
[Rating Assigned]. In a text box's control source
enpression, you would use =Nz([Rating Assigned], 0) instead
of binding the text box directly to the [Rating Assigned]
field.


MeredithS wrote:
I would leave them alone, but when I try to run a report based on the query,
it won't run if there are any of the quarters with null values -- so I'm
assuming I need to deal with them at the query level?


MeredithS wrote:
I have a query that selects from a group of clinicians, then averages their
ratings on certain variables by quarter. If I want to properly handle null
values (not all clinicians are rated every quarter), I've tried both the Nz
and the IIf(IsNull) functions in the query to no avail. Using Nz I've said:
RatingToDisplay = Nz(Rating Assigned, 0) or RatingToDisplay = Nz(Rating
Assigned, " "). Have also tried: IIf(IsNull([Rating Assigned]),0,[Rating
Assigned]). The latter requests a parameter. The former returns only values
of 1 or zero.


:
Any field (or control) name that contains a space or any
other funky character must be enclosed in square brackets:

Nz([Rating Assigned], 0)

If you are calculating an average on that, you probably are
much better off leaving the Null values alone. Otherwise,
your average will be dragged down by and zeros. Tha
aggregate functions (Count, Sum, Ave, etc) all ignore Nulls
to avoid that issue.

 

[Guest]

Marshall, this makes a lot of sense -- I had no idea there even was such a
requirement or a property sheet for the query ... I will try this Monday and
assume from what you're saying that it will fix the problem.

Many thanks

Meredith

Crosstab queries without any specified values in
ColumnHeadings only return columns that actually have values
in your table's data. The query can not make up these
things, it has to either be told what values you want or it
will just use what it finds in the table.

It's your report that was designed to use all possible
values and fails when the query doesn't have a column for
some values.

Open the query in design view. Then display the properties
sheet (View menu). Click in a blank area in the top part of
the query design window (where the table is, but not on the
table itself), the query's property list will appear.
ColumnHeadings is the third property from the top.
--
Marsh
MVP [MS Access]

Where is the ColumnHeads property found in the query design? I have
designated the DateAssigned(formatted to quarters) as the column heading for
the crosstab query; why wouldn't that pick up all the quarters? Particularly,
since when I run the query by itself, any quarters that contain values
(ratings) are displayed. And, no error messages ... ?

Ahhh, so it is a crosstab query. Now I'm almost certain
that setting the query's ColumnHeadings property to all the
names you expect in the quarter(?) field will resolve this
issue. From hints in your messages I suspect that the
ColumnHeadings should be set to
Q1,Q2,Q3,Q4


MeredithS wrote:
I will paste and post syntax Monday when I get back to work because I don't
have it at home, but ... the crosstab query is set up as you suggest, but
what happens when I try to run a very simple report from it, I get a message
to the effect (not exact syntax here) that "Access does not recognize
Variable Q3," for example. The report won't go any further than that. There
are different messages depending on which clinician's data is in the
underlying query, but basically the report is always returning a message that
it doesn't recognize the variables in the query. I don't understand that
because I'm creating the report from a wizard and referencing the query as
data source. I just assumed that since it couldn't find/recognize certain
variables, it must have something to do with null data but it could be a
completely different issue.

The underlying crosstab query is based on a table. There are no other
detours -- I create the query from the table. The query runs fine by itself.
I create the report from the query using the wizard. The report won't run.
I'm not sure where the problem is; do you have any thoughts?

Which syntax would you prefer to see Monday (if you still respond to this)?
The query or the report?


:
A parameter prompt means that you used a name that is not a
field in the query's table(s). The prompt tells you exactly
what name it can not resolve.

I'm sorry, but I have no idea what your report does or does
not do when "a clinician only had ratings for one quarter
out of four". The report must do something even if it only
generates an error message.

If the query is a crosstab query, then you should set the
query's ColumnHeadings property to all the quarters so the
field exists even if there is no data.

Mayve if you could provide more specific information along
with a Copy/Paste of the query's SQL view, I might be able
to spot something.

As for "other ways", I can't say because I just don't know
enough about your report.


MeredithS wrote:
thank you. I'm still wrestling with the correct syntax for the Nz function.
When I try to insert it into a query field I end up with a parameter request
-- so I'm still not sure what's happening there.

What I meant by the report not running if there are null values is that, if
I run the report based on the query specifying a clinician who, say, only had
ratings for one quarter out of four, the report won't run at all and gives an
error message to the effect that it doesn't recognize the variables ... It
will run for specified clinicians who have ratings in all 4 quarters which is
why I assumed the null values are the problem. Could be something else
entirely.

Thank you for your help with this; I'm a novice and somewhat in over my
head. However, there's no other way to do it than this, right?


:
I don't understand what you mean by "it won't run if there
are any of the quarters with null values". That definitely
does not sound right to me.

Regardless of that, you can use the Nz function anywhere you
need a zero instead of a potential Null. In a VBA procedure
upi would just use Nz(Me.[Rating Assigned], 0) instead of
[Rating Assigned]. In a text box's control source
enpression, you would use =Nz([Rating Assigned], 0) instead
of binding the text box directly to the [Rating Assigned]
field.


MeredithS wrote:
I would leave them alone, but when I try to run a report based on the query,
it won't run if there are any of the quarters with null values -- so I'm
assuming I need to deal with them at the query level?


MeredithS wrote:
I have a query that selects from a group of clinicians, then averages their
ratings on certain variables by quarter. If I want to properly handle null
values (not all clinicians are rated every quarter), I've tried both the Nz
and the IIf(IsNull) functions in the query to no avail. Using Nz I've said:
RatingToDisplay = Nz(Rating Assigned, 0) or RatingToDisplay = Nz(Rating
Assigned, " "). Have also tried: IIf(IsNull([Rating Assigned]),0,[Rating
Assigned]). The latter requests a parameter. The former returns only values
of 1 or zero.


:
Any field (or control) name that contains a space or any
other funky character must be enclosed in square brackets:

Nz([Rating Assigned], 0)

If you are calculating an average on that, you probably are
much better off leaving the Null values alone. Otherwise,
your average will be dragged down by and zeros. Tha
aggregate functions (Count, Sum, Ave, etc) all ignore Nulls
to avoid that issue.