If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Syntax for nulls in query
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 |
#2
|
|||
|
|||
Syntax for nulls in query
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. -- Marsh MVP [MS Access] |
#3
|
|||
|
|||
Syntax for nulls in query
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? "Marshall Barton" wrote: 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. -- Marsh MVP [MS Access] |
#4
|
|||
|
|||
Syntax for nulls in query
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. "Marshall Barton" wrote: 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. |
#5
|
|||
|
|||
Syntax for nulls in query
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 "Marshall Barton" wrote: 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. "Marshall Barton" wrote: 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. |
#6
|
|||
|
|||
Syntax for nulls in query
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] 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? "Marshall Barton" wrote: 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. "Marshall Barton" wrote: 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. |
#7
|
|||
|
|||
Syntax for nulls in query
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 "Marshall Barton" wrote: 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] 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? "Marshall Barton" wrote: 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. "Marshall Barton" wrote: 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. |
#8
|
|||
|
|||
Syntax for nulls in query
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] 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? "Marshall Barton" wrote: 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? "Marshall Barton" wrote: 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. "Marshall Barton" wrote: 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. |
#9
|
|||
|
|||
Syntax for nulls in query
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 "Marshall Barton" wrote: 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] 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? "Marshall Barton" wrote: 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? "Marshall Barton" wrote: 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. "Marshall Barton" wrote: 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. |
#10
|
|||
|
|||
Syntax for nulls in query
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] MeredithS wrote: 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 ... ? "Marshall Barton" wrote: 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? "Marshall Barton" wrote: 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? "Marshall Barton" wrote: 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. "Marshall Barton" wrote: 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. |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Advanced SQL Query | Wes Henry | General Discussion | 3 | December 14th, 2005 09:55 PM |
SQL query showing diff between actual and budget | Bon | Running & Setting Up Queries | 3 | August 25th, 2005 12:07 PM |
adding 2 fields including null entries | Jesse | Running & Setting Up Queries | 26 | January 18th, 2005 05:31 PM |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |
Hidden files in Ms-Query cause ODBC connect errors or Query is wac | needyourhelp | General Discussion | 4 | July 12th, 2004 09:38 PM |