|
| 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
October 31st, 2008, 04:48 PM
posted to microsoft.public.access
|
|||
|
|||
Conditional Expression
In a query, Access calculates a utilization rate by dividing the number of
units used by the number of units approved. (Named "burnrate" in the expression entered in the query) No problem with that. However, Access returns an error if a value for the number of units approved has not been entered, is null or zero. And it's impossible to get an average of all the records. I'm sure there must be a simple solution, but I haven't been able to come up with it. I will appreciate your help! -- Nona 
|
|
#2
October 31st, 2008, 05:15 PM
posted to microsoft.public.access
|
|||
|
|||
|
Conditional Expression
Use the Nz function to convert nulls. In your case it might look like;
[UnitsUsed]/Nz([UnitsApproved], 0) or something similar -- _________ Sean Bailey "Nona" wrote: In a query, Access calculates a utilization rate by dividing the number of units used by the number of units approved. (Named "burnrate" in the expression entered in the query) No problem with that. However, Access returns an error if a value for the number of units approved has not been entered, is null or zero. And it's impossible to get an average of all the records. I'm sure there must be a simple solution, but I haven't been able to come up with it. I will appreciate your help! -- Nona
|
|
#3
October 31st, 2008, 05:28 PM
posted to microsoft.public.access
|
|||
|
|||
|
Conditional Expression
Fraid not; that would involve division by zero, which is mathematically
impossible, so would still give an error. If a result of zero is required for Null or zero units approved then: IIF(Nz([UnitsApproved],0)0,[UnitsUsed]/[UnitsApproved],0) If a result of Null is required: IIF(Nz([UnitsApproved],0)0,[UnitsUsed]/[UnitsApproved],NULL) Ken Sheridan Stafford, England "Beetle" wrote: Use the Nz function to convert nulls. In your case it might look like; [UnitsUsed]/Nz([UnitsApproved], 0) or something similar -- _________ Sean Bailey "Nona" wrote: In a query, Access calculates a utilization rate by dividing the number of units used by the number of units approved. (Named "burnrate" in the expression entered in the query) No problem with that. However, Access returns an error if a value for the number of units approved has not been entered, is null or zero. And it's impossible to get an average of all the records. I'm sure there must be a simple solution, but I haven't been able to come up with it. I will appreciate your help! -- Nonap
|
|
#4
October 31st, 2008, 05:41 PM
posted to microsoft.public.access
|
|||
|
|||
|
Conditional Expression
I get the error message even if the number of approved units is zero. If I
enter "1" as the value, then I no longer get the error message. I don't particularly want to have "1" as a default value for new auths since it would skew the numbers (but not by much) so that's one possible solution. -- Nona "Beetle" wrote: Use the Nz function to convert nulls. In your case it might look like; [UnitsUsed]/Nz([UnitsApproved], 0) or something similar -- _________ Sean Bailey "Nona" wrote: In a query, Access calculates a utilization rate by dividing the number of units used by the number of units approved. (Named "burnrate" in the expression entered in the query) No problem with that. However, Access returns an error if a value for the number of units approved has not been entered, is null or zero. And it's impossible to get an average of all the records. I'm sure there must be a simple solution, but I haven't been able to come up with it. I will appreciate your help! -- Nona
|
|
#5
October 31st, 2008, 06:02 PM
posted to microsoft.public.access
|
|||
|
|||
|
Conditional Expression
Nona:
See my response above. Ken Sheridan Stafford, England "Nona" wrote: I get the error message even if the number of approved units is zero. If I enter "1" as the value, then I no longer get the error message. I don't particularly want to have "1" as a default value for new auths since it would skew the numbers (but not by much) so that's one possible solution. -- Nona "Beetle" wrote: Use the Nz function to convert nulls. In your case it might look like; [UnitsUsed]/Nz([UnitsApproved], 0) or something similar -- _________ Sean Bailey "Nona" wrote: In a query, Access calculates a utilization rate by dividing the number of units used by the number of units approved. (Named "burnrate" in the expression entered in the query) No problem with that. However, Access returns an error if a value for the number of units approved has not been entered, is null or zero. And it's impossible to get an average of all the records. I'm sure there must be a simple solution, but I haven't been able to come up with it. I will appreciate your help! -- Nona
|
|
#6
October 31st, 2008, 06:36 PM
posted to microsoft.public.access
|
|||
|
|||
|
Conditional Expression
Perfect!
Thanks Very Very Much! -- Nona "Ken Sheridan" wrote: Nona: See my response above. Ken Sheridan Stafford, England "Nona" wrote: I get the error message even if the number of approved units is zero. If I enter "1" as the value, then I no longer get the error message. I don't particularly want to have "1" as a default value for new auths since it would skew the numbers (but not by much) so that's one possible solution. -- Nona "Beetle" wrote: Use the Nz function to convert nulls. In your case it might look like; [UnitsUsed]/Nz([UnitsApproved], 0) or something similar -- _________ Sean Bailey "Nona" wrote: In a query, Access calculates a utilization rate by dividing the number of units used by the number of units approved. (Named "burnrate" in the expression entered in the query) No problem with that. However, Access returns an error if a value for the number of units approved has not been entered, is null or zero. And it's impossible to get an average of all the records. I'm sure there must be a simple solution, but I haven't been able to come up with it. I will appreciate your help! -- Nona
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
