2nd Posting Select Value In Query

[zyus]

My data are as follow

Myvalue value1 value2 value3
100 20 50 70
200 null 100 210

my goal is to divide myvalue with either value 1,2 or 3 whichever is higher

how to put the expression in query

 

[Daryl S]

Zyus -

Here is what will go in the SELECT field of your query:

=Iif(nz(value1,0)>nz(value2),
Iif(nz(value1,0)>nz(value3,0),Myvalue/value1,Myvalue/value3),
Iif(nz(value2,0)>nz(value3,0),Myvalue/value2,Myvalue/value3))

If value1 and value2 and value3 could all be null or zero, then you must
test for that (I am assuming they cannot be negative numbers):

=Iif(nz(value1,0) + nz(value2,0) + nz(value3,0) =
0,0,Iif(nz(value1,0)>nz(value2),
Iif(nz(value1,0)>nz(value3,0),Myvalue/value1,Myvalue/value3),
Iif(nz(value2,0)>nz(value3,0),Myvalue/value2,Myvalue/value3)))

 

[John W. Vinson]

My data are as follow

Myvalue value1 value2 value3
100 20 50 70
200 null 100 210

my goal is to divide myvalue with either value 1,2 or 3 whichever is higher

how to put the expression in query

You're being REALLY RUDE, zyus. We're all unpaid volunteers here, donating our
time, and costing you *nothing*.

Posting a "2nd posting" less than 30 minutes after your first one, before 6 in
the morning at that, is simply unreasonable.

I see you've gotten two good answers. I hope the service meets your
expectations; if not, I'll see that your fee is promptly refunded.

 

[zyus]

Dear John,

Sorry if my posting offended you. For your info , i can't even see my first
posting in the news group after i confirmed it and i thought it was not
successful after few attempt of retrieving. That's the reason why i put 2nd
posting in my header hoping that other group of member will not be confused

I've been with this group for sometimes and i never expect a quick & good
service from the member as i knew it's a voluntarily service.

I really appreciate those who responded to my posting professionally
including yourself which has helped me to improve my knowledge in access.

Regards

zyus

5/2/2010 3.15pm