calculating an area value

[jlute]

Three fields:
IDL
IDW
IDH

When each field has data then they're calculated to arrive at a cubic
value. The trick is to calculate an area value when only two fields
have data.

The possible combinations are:
IDLxIDW
IDLxIDH
IDWxIDH

Does anyone have a simple approach to this? I can write the code but
with my skills it will most likely be ridiculously long and
inefficient.

Thanks for any help you might have!

 

[Al Campagna]

Try the NZ function...

= NZ(IDL,1) * NZ(IDW,1) * NZ(IDH,1)

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[John Spencer]

IF you mean the value is Null then you can use the formula below.

NZ(IDL,1) * NZ(IDW,1) * Nz(IDH,1)

There are two potential problems with this. If all the values are null, the
formula will return 1. If two values are null, the formula will return the
value in the non-null field.

If that is a problem then test to see if more than one is null
IIF(IsNull(IDL)+IsNull(IDW)+IsNull(IDH)<-1,NZ(IDL,1)*NZ(IDW,1)*Nz(IDH,1),Null)


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[jlute]

Thanks, John and Al! Very thorough! I use Nz but it never occurred to
me to write it like this.

I've tried the test for more than one null but it returned null! I
fiddled with it and the dreaded "-" in the "<" is the culprit. I
didn't see it initially. Thanks!!!

 

[jlute]

I've been fiddling with this:
Areasq: IIf(IsNull([ODLength])+IsNull([ODWidth])
+IsNull([ODHeight])>=0,Null,IIf(IsNull([ODLength])+IsNull([ODWidth])
+IsNull([ODHeight])<1,Nz([ODLength],1)*Nz([ODWidth],1)*Nz([ODHeight],
1),Null))

Obviously, I still have the problem of "1" being returned when all are
Null. Is there any way I can work that out? I threw in the first IIf
to return Null if two fields are Null and it seems to work.

Thanks for you continued help!

 

[John Spencer]

Try it this way.

Areasq: IIf(Abs(IsNull([ODLength])+IsNull([ODWidth])+IsNull([ODHeight]))IN
(2,3),Nz([ODLength],1)*Nz([ODWidth],1)*Nz([ODHeight],1),Null)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I've been fiddling with this:
Areasq: IIf(IsNull([ODLength])+IsNull([ODWidth])
+IsNull([ODHeight])>=0,Null,IIf(IsNull([ODLength])+IsNull([ODWidth])
+IsNull([ODHeight])<1,Nz([ODLength],1)*Nz([ODWidth],1)*Nz([ODHeight],
1),Null))

Obviously, I still have the problem of "1" being returned when all are
Null. Is there any way I can work that out? I threw in the first IIf
to return Null if two fields are Null and it seems to work.

Thanks for you continued help!

IF you mean the value is Null then you can use the formula below.

NZ(IDL,1) * NZ(IDW,1) * Nz(IDH,1)

There are two potential problems with this. If all the values are null, the
formula will return 1. If two values are null, the formula will return the
value in the non-null field.

If that is a problem then test to see if more than one is null
IIF(IsNull(IDL)+IsNull(IDW)+IsNull(IDH)<-1,NZ(IDL,1)*NZ(IDW,1)*Nz(IDH,1),Nu­ll)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County




- Show quoted text -

 

[jlute]

Well, I had never encountered Abs and had to look it up! Still not
sure I fully understand it but...I gave it a whirl and it returns
Null:
-If all have values (yes!).
-If two have values and one is null (drat! this is when it needs to
multiply the two).

If one has a value and the other two are Null then it returns the
value from the non-null field (super drat!).

Since I don't fully understand Abs I can't see how to edit the code -
if it can be done at all.
Returns a value of the same type that is passed to it specifying the
absolute value of a number.

The required number argument can be any valid numeric expression. If
number contains Null, Null is returned; if it is an uninitialized
variable, zero is returned.

The absolute value of a number is its unsigned magnitude. For example,
ABS(-1) and ABS(1) both return 1.

Try it this way.

Areasq: IIf(Abs(IsNull([ODLength])+IsNull([ODWidth])+IsNull([ODHeight]))IN
(2,3),Nz([ODLength],1)*Nz([ODWidth],1)*Nz([ODHeight],1),Null)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I've been fiddling with this:
Areasq: IIf(IsNull([ODLength])+IsNull([ODWidth])
+IsNull([ODHeight])>=0,Null,IIf(IsNull([ODLength])+IsNull([ODWidth])
+IsNull([ODHeight])<1,Nz([ODLength],1)*Nz([ODWidth],1)*Nz([ODHeight],
1),Null))

Obviously, I still have the problem of "1" being returned when all are
Null. Is there any way I can work that out? I threw in the first IIf
to return Null if two fields are Null and it seems to work.

Thanks for you continued help!

- Show quoted text -

 

[John Spencer]

Perhaps I don't understand what you are trying to do.

AUUUGH!!!!!!

Testing the wrong way

Areasq: IIf(Abs([ODLength] is Not Null + [ODWidth] is Not Null + [ODHeight] Is
Not Null) IN (2,3), Nz([ODLength],1)*Nz([ODWidth],1)*Nz([ODHeight],1),Null)

or

Areasq: IIf(Abs(IsNull([ODLength])+IsNull([ODWidth])+IsNull([ODHeight]))IN
(0,1),Nz([ODLength],1)*Nz([ODWidth],1)*Nz([ODHeight],1),Null)

In words, if there are zero or one null value then calculate, but if there 2
or 3 nulls just return null.

The reason for the abs is just to strip of adding three true (-1) values. You
could drop the abs and use In (0,-1) as the test

Areasq: IIf(IsNull([ODLength])+IsNull([ODWidth])+IsNull([ODHeight])IN
(0,-1),Nz([ODLength],1)*Nz([ODWidth],1)*Nz([ODHeight],1),Null)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Well, I had never encountered Abs and had to look it up! Still not
sure I fully understand it but...I gave it a whirl and it returns
Null:
-If all have values (yes!).
-If two have values and one is null (drat! this is when it needs to
multiply the two).

If one has a value and the other two are Null then it returns the
value from the non-null field (super drat!).

Since I don't fully understand Abs I can't see how to edit the code -
if it can be done at all.
Returns a value of the same type that is passed to it specifying the
absolute value of a number.

The required number argument can be any valid numeric expression. If
number contains Null, Null is returned; if it is an uninitialized
variable, zero is returned.

The absolute value of a number is its unsigned magnitude. For example,
ABS(-1) and ABS(1) both return 1.

Try it this way.

Areasq: IIf(Abs(IsNull([ODLength])+IsNull([ODWidth])+IsNull([ODHeight]))IN
(2,3),Nz([ODLength],1)*Nz([ODWidth],1)*Nz([ODHeight],1),Null)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I've been fiddling with this:
Areasq: IIf(IsNull([ODLength])+IsNull([ODWidth])
+IsNull([ODHeight])>=0,Null,IIf(IsNull([ODLength])+IsNull([ODWidth])
+IsNull([ODHeight])<1,Nz([ODLength],1)*Nz([ODWidth],1)*Nz([ODHeight],
1),Null))
Obviously, I still have the problem of "1" being returned when all are
Null. Is there any way I can work that out? I threw in the first IIf
to return Null if two fields are Null and it seems to work.
Thanks for you continued help!
IF you mean the value is Null then you can use the formula below.
NZ(IDL,1) * NZ(IDW,1) * Nz(IDH,1)
There are two potential problems with this. If all the values are null, the
formula will return 1. If two values are null, the formula will return the
value in the non-null field.
If that is a problem then test to see if more than one is null
IIF(IsNull(IDL)+IsNull(IDW)+IsNull(IDH)<-1,NZ(IDL,1)*NZ(IDW,1)*Nz(IDH,1),Nu­­ll)
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
(e-mail address removed) wrote:
Three fields:
IDL
IDW
IDH
When each field has data then they're calculated to arrive at a cubic
value. The trick is to calculate an area value when only two fields
have data.
The possible combinations are:
IDLxIDW
IDLxIDH
IDWxIDH
Does anyone have a simple approach to this? I can write the code but
with my skills it will most likely be ridiculously long and
inefficient.
Thanks for any help you might have!- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[jlute]

Sorry, John! I wasn't very clear.

In words, if there are zero or one null value then calculate, but if there 2
or 3 nulls just return null.

Whew!

Somehow you made sense of what I was babbling about! I guess that's
why you are where you are and I am where I am

Abs or no Abs worked! I'm going with Abs as it's an intriguing new
approach that I may need for something else. I just can't believe how
much vocabulary there is.

Thanks!!! You've now made my month! Once again I find myself in awe
with how you MVP's so readily help resolve these things. I bought an
Access book a couple years ago but I can't make heads or tails of it
and it's collected more dust than helped me. This forum is without
doubt the best resource.

Thanks! Have a great night!

Perhaps I don't understand what you are trying to do.

AUUUGH!!!!!!

Testing the wrong way

Areasq: IIf(Abs([ODLength] is Not Null + [ODWidth] is Not Null + [ODHeight] Is
Not Null) IN (2,3), Nz([ODLength],1)*Nz([ODWidth],1)*Nz([ODHeight],1),Null)

or

Areasq: IIf(Abs(IsNull([ODLength])+IsNull([ODWidth])+IsNull([ODHeight]))IN
(0,1),Nz([ODLength],1)*Nz([ODWidth],1)*Nz([ODHeight],1),Null)

In words, if there are zero or one null value then calculate, but if there 2
or 3 nulls just return null.

The reason for the abs is just to strip of adding three true (-1) values. You
could drop the abs and use In (0,-1) as the test

Areasq: IIf(IsNull([ODLength])+IsNull([ODWidth])+IsNull([ODHeight])IN
(0,-1),Nz([ODLength],1)*Nz([ODWidth],1)*Nz([ODHeight],1),Null)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Well, I had never encountered Abs and had to look it up! Still not
sure I fully understand it but...I gave it a whirl and it returns
Null:
-If all have values (yes!).
-If two have values and one is null (drat! this is when it needs to
multiply the two).

If one has a value and the other two are Null then it returns the
value from the non-null field (super drat!).

Since I don't fully understand Abs I can't see how to edit the code -
if it can be done at all.
Returns a value of the same type that is passed to it specifying the
absolute value of a number.

The required number argument can be any valid numeric expression. If
number contains Null, Null is returned; if it is an uninitialized
variable, zero is returned.

The absolute value of a number is its unsigned magnitude. For example,
ABS(-1) and ABS(1) both return 1.

Try it this way.
Areasq: IIf(Abs(IsNull([ODLength])+IsNull([ODWidth])+IsNull([ODHeight]))IN
(2,3),Nz([ODLength],1)*Nz([ODWidth],1)*Nz([ODHeight],1),Null)
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
(e-mail address removed) wrote:
I've been fiddling with this:
Areasq: IIf(IsNull([ODLength])+IsNull([ODWidth])
+IsNull([ODHeight])>=0,Null,IIf(IsNull([ODLength])+IsNull([ODWidth])
+IsNull([ODHeight])<1,Nz([ODLength],1)*Nz([ODWidth],1)*Nz([ODHeight],
1),Null))
Obviously, I still have the problem of "1" being returned when all are
Null. Is there any way I can work that out? I threw in the first IIf
to return Null if two fields are Null and it seems to work.
Thanks for you continued help!
IF you mean the value is Null then you can use the formula below.
    NZ(IDL,1) * NZ(IDW,1) * Nz(IDH,1)
There are two potential problems with this.  If all the values arenull, the
formula will return 1.  If two values are null, the formula will return the
value in the non-null field.
If that is a problem then test to see if more than one is null
IIF(IsNull(IDL)+IsNull(IDW)+IsNull(IDH)<-1,NZ(IDL,1)*NZ(IDW,1)*Nz(IDH,1),Nu­­­ll)
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
(e-mail address removed) wrote:
Three fields:
IDL
IDW
IDH
When each field has data then they're calculated to arrive at a cubic
value. The trick is to calculate an area value when only two fields
have data.
The possible combinations are:
IDLxIDW
IDLxIDH
IDWxIDH
Does anyone have a simple approach to this? I can write the code but
with my skills it will most likely be ridiculously long and
inefficient.
Thanks for any help you might have!- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[jlute]

Double AUUUGH!!!!!!

Yesterday after I posted that it worked I went back and realized that
it doesn't go the whole nine yards because it's calculating all 3
rather than returning Null.

In words, if there are zero or one null value then calculate, but if there 2
or 3 nulls just return null.

What's missing from this is returning Null if all 3 have values.

Here's the complete argument:
-If 3 are Null return Null.
-If 1 has a value and 2 are Null then return Null.
-If 1 is Null and 2 have values then calculate those 2 values.

I threw this together:
Areasq: IIf([ODLength]+[ODWidth]+[ODHeight],Null,IIf(IsNull([ODLength])
+IsNull([ODWidth])+IsNull([ODHeight]) In (0,-1),Nz([ODLength],
1)*Nz([ODWidth],1)*Nz([ODHeight],1),Null))

I've checked and double-checked and now it's behaving as expected.

Just an FYI and thanks, again!

Perhaps I don't understand what you are trying to do.

AUUUGH!!!!!!

Testing the wrong way

Areasq: IIf(Abs([ODLength] is Not Null + [ODWidth] is Not Null + [ODHeight] Is
Not Null) IN (2,3), Nz([ODLength],1)*Nz([ODWidth],1)*Nz([ODHeight],1),Null)

or

Areasq: IIf(Abs(IsNull([ODLength])+IsNull([ODWidth])+IsNull([ODHeight]))IN
(0,1),Nz([ODLength],1)*Nz([ODWidth],1)*Nz([ODHeight],1),Null)

In words, if there are zero or one null value then calculate, but if there 2
or 3 nulls just return null.

The reason for the abs is just to strip of adding three true (-1) values. You
could drop the abs and use In (0,-1) as the test

Areasq: IIf(IsNull([ODLength])+IsNull([ODWidth])+IsNull([ODHeight])IN
(0,-1),Nz([ODLength],1)*Nz([ODWidth],1)*Nz([ODHeight],1),Null)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Well, I had never encountered Abs and had to look it up! Still not
sure I fully understand it but...I gave it a whirl and it returns
Null:
-If all have values (yes!).
-If two have values and one is null (drat! this is when it needs to
multiply the two).

If one has a value and the other two are Null then it returns the
value from the non-null field (super drat!).

Since I don't fully understand Abs I can't see how to edit the code -
if it can be done at all.
Returns a value of the same type that is passed to it specifying the
absolute value of a number.

The required number argument can be any valid numeric expression. If
number contains Null, Null is returned; if it is an uninitialized
variable, zero is returned.

The absolute value of a number is its unsigned magnitude. For example,
ABS(-1) and ABS(1) both return 1.

Try it this way.
Areasq: IIf(Abs(IsNull([ODLength])+IsNull([ODWidth])+IsNull([ODHeight]))IN
(2,3),Nz([ODLength],1)*Nz([ODWidth],1)*Nz([ODHeight],1),Null)
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
(e-mail address removed) wrote:
I've been fiddling with this:
Areasq: IIf(IsNull([ODLength])+IsNull([ODWidth])
+IsNull([ODHeight])>=0,Null,IIf(IsNull([ODLength])+IsNull([ODWidth])
+IsNull([ODHeight])<1,Nz([ODLength],1)*Nz([ODWidth],1)*Nz([ODHeight],
1),Null))
Obviously, I still have the problem of "1" being returned when all are
Null. Is there any way I can work that out? I threw in the first IIf
to return Null if two fields are Null and it seems to work.
Thanks for you continued help!
IF you mean the value is Null then you can use the formula below.
    NZ(IDL,1) * NZ(IDW,1) * Nz(IDH,1)
There are two potential problems with this.  If all the values arenull, the
formula will return 1.  If two values are null, the formula will return the
value in the non-null field.
If that is a problem then test to see if more than one is null
IIF(IsNull(IDL)+IsNull(IDW)+IsNull(IDH)<-1,NZ(IDL,1)*NZ(IDW,1)*Nz(IDH,1),Nu­­­ll)
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
(e-mail address removed) wrote:
Three fields:
IDL
IDW
IDH
When each field has data then they're calculated to arrive at a cubic
value. The trick is to calculate an area value when only two fields
have data.
The possible combinations are:
IDLxIDW
IDLxIDH
IDWxIDH
Does anyone have a simple approach to this? I can write the code but
with my skills it will most likely be ridiculously long and
inefficient.
Thanks for any help you might have!- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -