|
| 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 27th, 2008, 07:04 PM
posted to microsoft.public.access.reports
|
|||
|
|||
#Num! Error
Here is the expression I'm using. If there are no returns in the field
[Warranty Income] then I'm getting the #Num! error. If there are no warranties in the range I'd like to have "0" returned instead of #Num!. =Abs(Sum([Warranty Income])/Sum([Warranty Income]=1)) 
|
|
#2
October 27th, 2008, 07:58 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
#Num! Error
You might want to check the answer to the question posted immediately prior
to yours. If you don't find the answer, post back. -- Duane Hookom Microsoft Access MVP "Reese" wrote: Here is the expression I'm using. If there are no returns in the field [Warranty Income] then I'm getting the #Num! error. If there are no warranties in the range I'd like to have "0" returned instead of #Num!. =Abs(Sum([Warranty Income])/Sum([Warranty Income]=1))
|
|
#3
October 27th, 2008, 09:11 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
#Num! Error
That doesn't address my question. My problem is that if there are no records
to count in the fields in question I get a #Num! showing up on my report. I'd rather have that return a "0" instead. "Duane Hookom" wrote: You might want to check the answer to the question posted immediately prior to yours. If you don't find the answer, post back. -- Duane Hookom Microsoft Access MVP "Reese" wrote: Here is the expression I'm using. If there are no returns in the field [Warranty Income] then I'm getting the #Num! error. If there are no warranties in the range I'd like to have "0" returned instead of #Num!. =Abs(Sum([Warranty Income])/Sum([Warranty Income]=1))
|
|
#4
October 27th, 2008, 10:14 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
#Num! Error
Do not understand what you are trying to do --
Sum([Warranty Income]=1) Is this supose to only sum those amounts that are equal or larger than one? If that is what you want then use -- Sum(IIF(Nz([Warranty Income], 0) =1, [Warranty Income], 0)) Apply Nz like this Nz([Warranty Income], 0) to change any nulls to zero. -- KARL DEWEY Build a little - Test a little "Reese" wrote: That doesn't address my question. My problem is that if there are no records to count in the fields in question I get a #Num! showing up on my report. I'd rather have that return a "0" instead. "Duane Hookom" wrote: You might want to check the answer to the question posted immediately prior to yours. If you don't find the answer, post back. -- Duane Hookom Microsoft Access MVP "Reese" wrote: Here is the expression I'm using. If there are no returns in the field [Warranty Income] then I'm getting the #Num! error. If there are no warranties in the range I'd like to have "0" returned instead of #Num!. =Abs(Sum([Warranty Income])/Sum([Warranty Income]=1))
|
|
#5
October 28th, 2008, 01:07 AM
posted to microsoft.public.access.reports
|
|||
|
|||
|
#Num! Error
I can't figure if you are getting divide by zero issues or nulls. If it is
divide by 0 then use =IIf( {some expression}=0,0, {another expression}/{some expression}) If it is a problem with Nulls, use Nz({another expression},{substitute expression}) Duane Hookom Microsoft Access MVP "Reese" wrote: That doesn't address my question. My problem is that if there are no records to count in the fields in question I get a #Num! showing up on my report. I'd rather have that return a "0" instead. "Duane Hookom" wrote: You might want to check the answer to the question posted immediately prior to yours. If you don't find the answer, post back. -- Duane Hookom Microsoft Access MVP "Reese" wrote: Here is the expression I'm using. If there are no returns in the field [Warranty Income] then I'm getting the #Num! error. If there are no warranties in the range I'd like to have "0" returned instead of #Num!. =Abs(Sum([Warranty Income])/Sum([Warranty Income]=1))
|
|
#6
October 28th, 2008, 04:52 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
#Num! Error
I'm trying to add the total income from the [Warranty Income] field, then to
divide that number by the number of instances that the field [Warranty Income] had a value of greater than 1. ex. total income 1644 divided by 4 warranties sold to give a value of 411. However, if in any of the subgroups that the report is running has no warranties sold, which would mean no [Warranty Income] with a value greater than 1, the return I get is #Num!. The expression I'm using is: =Abs(Sum([Warranty Income]))/Abs(Sum([Warranty Income]=1)) "Duane Hookom" wrote: I can't figure if you are getting divide by zero issues or nulls. If it is divide by 0 then use =IIf( {some expression}=0,0, {another expression}/{some expression}) If it is a problem with Nulls, use Nz({another expression},{substitute expression}) Duane Hookom Microsoft Access MVP "Reese" wrote: That doesn't address my question. My problem is that if there are no records to count in the fields in question I get a #Num! showing up on my report. I'd rather have that return a "0" instead. "Duane Hookom" wrote: You might want to check the answer to the question posted immediately prior to yours. If you don't find the answer, post back. -- Duane Hookom Microsoft Access MVP "Reese" wrote: Here is the expression I'm using. If there are no returns in the field [Warranty Income] then I'm getting the #Num! error. If there are no warranties in the range I'd like to have "0" returned instead of #Num!. =Abs(Sum([Warranty Income])/Sum([Warranty Income]=1))
|
|
#7
October 28th, 2008, 05:51 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
#Num! Error
Try:
=IIf(Abs(Sum([Warranty Income]=1)) = 0,0,Abs(Sum([Warranty Income]))/Abs(Sum([Warranty Income]=1)) ) -- Duane Hookom Microsoft Access MVP "Reese" wrote: I'm trying to add the total income from the [Warranty Income] field, then to divide that number by the number of instances that the field [Warranty Income] had a value of greater than 1. ex. total income 1644 divided by 4 warranties sold to give a value of 411. However, if in any of the subgroups that the report is running has no warranties sold, which would mean no [Warranty Income] with a value greater than 1, the return I get is #Num!. The expression I'm using is: =Abs(Sum([Warranty Income]))/Abs(Sum([Warranty Income]=1)) "Duane Hookom" wrote: I can't figure if you are getting divide by zero issues or nulls. If it is divide by 0 then use =IIf( {some expression}=0,0, {another expression}/{some expression}) If it is a problem with Nulls, use Nz({another expression},{substitute expression}) Duane Hookom Microsoft Access MVP "Reese" wrote: That doesn't address my question. My problem is that if there are no records to count in the fields in question I get a #Num! showing up on my report. I'd rather have that return a "0" instead. "Duane Hookom" wrote: You might want to check the answer to the question posted immediately prior to yours. If you don't find the answer, post back. -- Duane Hookom Microsoft Access MVP "Reese" wrote: Here is the expression I'm using. If there are no returns in the field [Warranty Income] then I'm getting the #Num! error. If there are no warranties in the range I'd like to have "0" returned instead of #Num!. =Abs(Sum([Warranty Income])/Sum([Warranty Income]=1))
|
|
#8
October 28th, 2008, 06:06 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
#Num! Error
Wow, that's complicated, but it worked like a charm. Thank you!
"Duane Hookom" wrote: Try: =IIf(Abs(Sum([Warranty Income]=1)) = 0,0,Abs(Sum([Warranty Income]))/Abs(Sum([Warranty Income]=1)) ) -- Duane Hookom Microsoft Access MVP "Reese" wrote: I'm trying to add the total income from the [Warranty Income] field, then to divide that number by the number of instances that the field [Warranty Income] had a value of greater than 1. ex. total income 1644 divided by 4 warranties sold to give a value of 411. However, if in any of the subgroups that the report is running has no warranties sold, which would mean no [Warranty Income] with a value greater than 1, the return I get is #Num!. The expression I'm using is: =Abs(Sum([Warranty Income]))/Abs(Sum([Warranty Income]=1)) "Duane Hookom" wrote: I can't figure if you are getting divide by zero issues or nulls. If it is divide by 0 then use =IIf( {some expression}=0,0, {another expression}/{some expression}) If it is a problem with Nulls, use Nz({another expression},{substitute expression}) Duane Hookom Microsoft Access MVP "Reese" wrote: That doesn't address my question. My problem is that if there are no records to count in the fields in question I get a #Num! showing up on my report. I'd rather have that return a "0" instead. "Duane Hookom" wrote: You might want to check the answer to the question posted immediately prior to yours. If you don't find the answer, post back. -- Duane Hookom Microsoft Access MVP "Reese" wrote: Here is the expression I'm using. If there are no returns in the field [Warranty Income] then I'm getting the #Num! error. If there are no warranties in the range I'd like to have "0" returned instead of #Num!. =Abs(Sum([Warranty Income])/Sum([Warranty Income]=1))
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
