URGENT: select CASE?

  • Thread starter Thread starter marco_pb via AccessMonster.com
  • Start date Start date
M

marco_pb via AccessMonster.com

Hi, I am a real noob in programing vbscript in Access 2003.

I have got a problem here..
I want to count how many null and 0 values that are in a ROW.
Then I want to have the average of the rest of data in that row without
having the null and 0 values calculated.
sample:
------------
row A -> v w x y z avg=(v+w+x+y+z)/5
row B -> f g 0 h avg=(f+g+h)/3

I figured it that I can use SELECT CASE, but since I am noob here, I dont
know how to make it.
I really need your helps. Thank you in advance.

PLease.. I really need to get it all done in a week deadline...
I really thank you all for your help..
 
If you are seeking to average values across the record, then you have
repeating fields. The solution will be to create a table with many related
records instead of this table with many repeating fields. It will then be
child's play to count or average the non-null values, because that is the
default behavior in Access.

If you cannot change the structure for some reason, you will need to write
your own function to sum and count the non-null values passed in and return
the average. The function will accept a ParamArray of values, and you will
loop though from LBound to UBound. There is an example of that kind of
function in this link:
MinOfList() and MaxOfList() functions
at:
http://allenbrowne.com/func-09.html
 
Hi, I am a real noob in programing vbscript in Access 2003.

I have got a problem here..
I want to count how many null and 0 values that are in a ROW.
Then I want to have the average of the rest of data in that row without
having the null and 0 values calculated.
sample:
------------
row A -> v w x y z avg=(v+w+x+y+z)/5
row B -> f g 0 h avg=(f+g+h)/3

I figured it that I can use SELECT CASE, but since I am noob here, I dont
know how to make it.
I really need your helps. Thank you in advance.

PLease.. I really need to get it all done in a week deadline...
I really thank you all for your help..
Click to expand...

I have to agree with Allen's suggestions: the root of your problem
appears to be a flawed table design. But you can use an expression
like

(NZ([V]) + NZ([W]) + NZ([X]) + NZ([Y]) + NZ([Z])) / (IIF(NZ[V]) = 0,
0, 1) + IIF(NZ[W]) = 0, 0, 1) + IIF(NZ[X]) = 0, 0, 1) + IIF(NZ[Y]) =
0, 0, 1) + IIF(NZ[Z]) = 0, 0, 1))

This will err if all five fields are NULL or zero (and a custom
function would be better).

John W. Vinson[MVP]
 
Thank you all..

for some reason, I cant change the table design.. I have to stick with the
existing design.

I have tried the solution using NZ:
(NZ([Technik_Elektronik]) + NZ([Informatik]) + NZ([Mathe]) + NZ([Deutsch]) +
NZ([Englisch])) / (IIF(NZ[Technik_Elektronik] = 0, 0, 1) + IIF(NZ[Informatik]
= 0, 0, 1) + IIF(NZ[Mathe] = 0, 0, 1) + IIF(NZ[Deutsch] =0, 0, 1) + IIF(NZ
[Englisch] = 0, 0, 1))

but still I have got an error. I am using ACCESS 2003 in Germany version. the
error seems like: You have no operator. I am not clear since I only speak a
little deutsch. (error while compile - before saving the query)

I have tried it in my laptop (english version of ms access 2003) and it says
DATATYPE MISMATCH IN CRITERIA EXPRESSION (error after I save my query - error
while I RUN the query)

I dont know what the error is. I have tried changing the IIF with WENN (in
deutsch), but still it delivers the same error message.

I really need to have this done.. And I really need your help guys.. THANK
YOU ALL in advance... thank you
 
I am really sorry, but i think i have found what i need.

Avg = (Nz(v, 0) + Nz(w, 0) + Nz(x, 0) + Nz(y, 0) + Nz(z, 0) + Nz(a, 0)) /
(IIf(v = 0, 0, 1) + IIf(w = 0, 0, 1) + IIf(x = 0, 0, 1) + IIf(y = 0, 0, 1) +
IIf(z = 0, 0, 1) + IIf(a = 0, 0, 1))

it is the code that works with my form. Thank you very much for all your help
and support.
I really thank you

have a nice day ^^

thank you
 
Actually, I think your denominator is incorrect. You need to check whether
or not the variables are Null there as well.
 
for now i think it is enough.. But I also want it to convert null values into
0.
I have tried any ways i know, but still it can not works.

I would love to hear from you all..

thank you anyway for all of your helps and inputs..
 
Back
Top