All have possible values of -1 to +1 in .5 increments. some of the fields will contain null values, I need to find an average in a new field called Total Slant, that will average all three fields, taking nulls into consideration e.g, it can't just divide by three as nulls will of course make that incorrect.
i am not sure what you want here.
if it is null is that consider one of the field you divided by 2?
if you want to replace null with 0 use nz(fieldname, 0)
this will put zero in place where it is null.
i am not sure what you want here.
if it is null is that consider one of the field you divided by 2?
if you want to replace null with 0 use nz(fieldname, 0)
this will put zero in place where it is null.
I would use two different calculations for this. One to determine what to divide by, and another to find the average. To find the number to divide by:
Code:
="Name of Column":
iif(isnull(field1) and isnull(field2) and is null(field3),0,
iif(isnull(field1) and isnull(field2),1,
iif(isnull(field1) and isnull(field3),1,
iif(isnull(field2) and is null(field3),1,
iif(isnull(field1),2,
iif(isnull(field2),2,
iif(isnull(field3),2,
,3)))))))
Here is my last answer, with an important improvement.
Code:
=iif(nz(field1,0)+nz(field2,0)+nz(field3,0)=0,"Result you want for an error Divided by 0 here", (nz(field1,0)+nz(field2,0)+nz(field3,0))/"Name of Column")[/quote]
Please not that the long if statement above may not be the best way to do it, because it may be slow. Also, Excel has a nesting limit of 7, I am not sure if Access does to. If so, this method wont work. Also, the if statement may not be spot on, because I am not entirely sure if I used the function AND right. I dont work with operators much.
I would use two different calculations for this. One to determine what to divide by, and another to find the average. To find the number to divide by:
Code:
="Name of Column":
iif(isnull(field1) and isnull(field2) and is null(field3),0,
iif(isnull(field1) and isnull(field2),1,
iif(isnull(field1) and isnull(field3),1,
iif(isnull(field2) and is null(field3),1,
iif(isnull(field1),2,
iif(isnull(field2),2,
iif(isnull(field3),2,
,3)))))))
Here is my last answer, with an important improvement.
Code:
=iif(nz(field1,0)+nz(field2,0)+nz(field3,0)=0,"Result you want for an error Divided by 0 here", (nz(field1,0)+nz(field2,0)+nz(field3,0))/"Name of Column")[/quote]
Please not that the long if statement above may not be the best way to do it, because it may be slow. Also, Excel has a nesting limit of 7, I am not sure if Access does to. If so, this method wont work. Also, the if statement may not be spot on, because I am not entirely sure if I used the function AND right. I dont work with operators much.
You sir are a genius, that worked pretty much by the book, the only problem with it is that when the query is run it asks for a parameter value related to the last line of the code - "Name of Column", when you press return without entering the value it works perfectly. Not quite sure why it asks for that as the parameter is there.
But thats really good, thanks alot for the help!!!!
If you are getting that parameter dialog, then the code cannot be working.
"Name of column" in the second line of code is a reference to the first part of the code.
Lets say that the first calculated column in your query is called "CalculateNonNulls". Then this is what that column will look like:
Code:
=[B]CalculateNonNulls[/B]:
iif(isnull(field1) and isnull(field2) and is null(field3),0,
iif(isnull(field1) and isnull(field2),1,
iif(isnull(field1) and isnull(field3),1,
iif(isnull(field2) and is null(field3),1,
iif(isnull(field1),2,
iif(isnull(field2),2,
iif(isnull(field3),2,
,3)))))))
In order for this next line to work, you have to reference the result from the above formula. That reference is bolded.
Code:
=iif(nz(field1,0)+nz(field2,0)+nz(field3,0)=0,"Result you want for an error Divided by 0 here", (nz(field1,0)+nz(field2,0)+nz(field3,0))/[B]CalculateNonNulls[/B]
See the reference? That reference changes what the sum of the non-null fields is divided by. So...
3 Null fields
Returns whatever you put here "Result you want for an error Divided by 0 here"
If you are getting that parameter dialog, then the code cannot be working.
"Name of column" in the second line of code is a reference to the first part of the code.
Lets say that the first calculated column in your query is called "CalculateNonNulls". Then this is what that column will look like:
Code:
=[B]CalculateNonNulls[/B]:
iif(isnull(field1) and isnull(field2) and is null(field3),0,
iif(isnull(field1) and isnull(field2),1,
iif(isnull(field1) and isnull(field3),1,
iif(isnull(field2) and is null(field3),1,
iif(isnull(field1),2,
iif(isnull(field2),2,
iif(isnull(field3),2,
,3)))))))
In order for this next line to work, you have to reference the result from the above formula. That reference is bolded.
Code:
=iif(nz(field1,0)+nz(field2,0)+nz(field3,0)=0,"Result you want for an error Divided by 0 here", (nz(field1,0)+nz(field2,0)+nz(field3,0))/[B]CalculateNonNulls[/B]
See the reference? That reference changes what the sum of the non-null fields is divided by. So...
3 Null fields
Returns whatever you put here "Result you want for an error Divided by 0 here"
Thanks for looking at it again, I still can't find why the parameter box is appearing, the two fields are calculating the numbers correctly, but the box appears when you run the query, then when you return it without entering a figure it calculates correctly.
The first field I have called *slantcalc*, this is the code I have in the query for it -
slantcalc: IIf(IsNull([head slant]) And IsNull([first slant]) And IsNull([text slant]),0,IIf(IsNull([head slant]) And IsNull([first slant]),1,IIf(IsNull([head slant]) And IsNull([text slant]),1,IIf(IsNull([first slant]) And IsNull([text slant]),1,IIf(IsNull([head slant]),2,IIf(IsNull([first slant]),2,IIf(IsNull([text slant]),2,3)))))))
The second I have called *Total Slant*, I have referenced calcslant at the end of the code to look for the / ref. Here is the code -
Total Slant: IIf(nz([head slant],0)+nz([first slant],0)+nz([text slant],0)=0,0,(nz([head slant],0)+nz([first slant],0)+nz([text slant],0))/[slantcalc])
Check the spelling. Make sure the reference to [slantcalc] is spelled the same. Also, look at the parameter box and note how slantcalc is spelled, see if that points to what is wrong.
If that doesn't do it, you can post your db, or you can try switch() like wiklen suggested. According to the link wiklen posted, the limit on switch is 7, so your just in. But if you ever add a field, then switch won't work anymore.
Code:
switch(
isnull(field1) and isnull(field2) and is null(field3),0,
isnull(field1) and isnull(field3),[field2],
isnull(field2) and isnull(field3),[field1],
isnull(field1) and isnull(field2),[field3],
isnull(field1),[field2]+[field3]/2,
isnull(field2),[field1]+[field3]/2,
isnull(field3),[field1]+[field2]/2,
)
Oops, that doesn't work if all three fields are not null. If there is a limit of 7, you cannot use switch().
Bizarrely, I think I may have fixed this now. There were no problems with missing references and all the spellings were consistent, but I have changed the TOTAL row to SUM instead of GROUP BY, which seems to have fixed it, it has got rid of the enter parameter box, and hasn't altered the totals.
I can't quite see why that would remove the parameter box, but I'm not complaining!!!
Thanks a lot for the time you guys have spent, I'm sure I will have some more problems to solve soon!!!
Check the spelling. Make sure the reference to [slantcalc] is spelled the same. Also, look at the parameter box and note how slantcalc is spelled, see if that points to what is wrong.
If that doesn't do it, you can post your db, or you can try switch() like wiklen suggested. According to the link wiklen posted, the limit on switch is 7, so your just in. But if you ever add a field, then switch won't work anymore.
Code:
switch(
isnull(field1) and isnull(field2) and is null(field3),0,
isnull(field1) and isnull(field3),[field2],
isnull(field2) and isnull(field3),[field1],
isnull(field1) and isnull(field2),[field3],
isnull(field1),[field2]+[field3]/2,
isnull(field2),[field1]+[field3]/2,
isnull(field3),[field1]+[field2]/2,
)
Oops, that doesn't work if all three fields are not null. If there is a limit of 7, you cannot use switch().
If you are getting that parameter dialog, then the code cannot be working.
"Name of column" in the second line of code is a reference to the first part of the code.
Lets say that the first calculated column in your query is called "CalculateNonNulls". Then this is what that column will look like:
Code:
=[B]CalculateNonNulls[/B]:
iif(isnull(field1) and isnull(field2) and is null(field3),0,
iif(isnull(field1) and isnull(field2),1,
iif(isnull(field1) and isnull(field3),1,
iif(isnull(field2) and is null(field3),1,
iif(isnull(field1),2,
iif(isnull(field2),2,
iif(isnull(field3),2,
,3)))))))
In order for this next line to work, you have to reference the result from the above formula. That reference is bolded.
Code:
=iif(nz(field1,0)+nz(field2,0)+nz(field3,0)=0,"Result you want for an error Divided by 0 here", (nz(field1,0)+nz(field2,0)+nz(field3,0))/[B]CalculateNonNulls[/B]
See the reference? That reference changes what the sum of the non-null fields is divided by. So...
3 Null fields
Returns whatever you put here "Result you want for an error Divided by 0 here"
Wow, this is exactly what I need, and I'm trying to get it to work. I have 3 fields (sometimes null value) that need to be averaged.
In my query, using the expression builder, I placed all of your code into the "Field" of column named "Average Tare:". (I did substitue my field and column names.) I'm getting the following error:
"The expression you entered contains invalid syntax. You may have entered a comma without a preceding value or identifier."
Any thoughts would be so appreciated.
*Edit: Thanks so much for your post and answers to my questions. I double checked my code AND I put the last line of code in it's own field. It's working wonderfully, can't thank you enough.
