query with zero records

I

inungh

I have one query which get data from 3 queries.

I want to return 1 when 2 qeries return no record.
I use iif(isnull([MyField1]),1, MyField1) which works if only one
query returns zero record, if 2 queries return zero record then I get
divided by zero error using following expression

iif(isnull([MyField1]) and isnull([MyField2]),1, [MyField1]/
[MyField2])

It should return 1, if both fields are null.
Please let me know if I am wrong.

Your information is great appreciated,
 
K

kc-mass

The logic is wrong.
Try:
IIF((Isnull(Myfield1) AND IsNull(MyField2)), 1,IIf((NZ(MyField1) = 0 OR
NZ(MyField2) = 0)),0,(Myfield1/MyField2))

Kevin
 
J

John Spencer

Null and ZERO are not the same. Null is the absence of a value. Zero is
a value.

So the question is what do you want if either field is null, both fields
are null, or if field2 is zero.

IIF(Field1 is Null and Field2 is Null,0
IIF(Field1 is Null, 0
IIF (Field2 = 0,0,Field1/Field2)))

Now, that will return
zero if both field are null
zero if field1 is null
zero if field 2 = 0
null if field1 is null and field2 has a value
a number if field1 has a value and field2 has a value

In other words there are several cases
Field 1 is Null or Not Null
Field 2 is Null or Zero or some other number

So you have up to six cases you need to handle.
Field1 is null // Field 2 is Null
Field1 is Null // Field2 = 0
Field1 is null // Field2 is some value other than zero
and then three cases where field1 is not null

You may be able to condense these down into just a few cases depending
on you rules. For instance if Field 1 is Null or zero always return zero.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
K

kc-mass

Hi John

For my own edification please tell me how this fails:

The logic is wrong.
Try:
IIF((Isnull(Myfield1) AND IsNull(MyField2)), 1,IIf((NZ(MyField1) = 0 OR
NZ(MyField2) = 0)),0,(Myfield1/MyField2))

Kevin



John Spencer said:
Null and ZERO are not the same. Null is the absence of a value. Zero is a
value.

So the question is what do you want if either field is null, both fields
are null, or if field2 is zero.

IIF(Field1 is Null and Field2 is Null,0
IIF(Field1 is Null, 0
IIF (Field2 = 0,0,Field1/Field2)))

Now, that will return
zero if both field are null
zero if field1 is null
zero if field 2 = 0
null if field1 is null and field2 has a value
a number if field1 has a value and field2 has a value

In other words there are several cases
Field 1 is Null or Not Null
Field 2 is Null or Zero or some other number

So you have up to six cases you need to handle.
Field1 is null // Field 2 is Null
Field1 is Null // Field2 = 0
Field1 is null // Field2 is some value other than zero
and then three cases where field1 is not null

You may be able to condense these down into just a few cases depending on
you rules. For instance if Field 1 is Null or zero always return zero.

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

I have one query which get data from 3 queries.

I want to return 1 when 2 qeries return no record.
I use iif(isnull([MyField1]),1, MyField1) which works if only one
query returns zero record, if 2 queries return zero record then I get
divided by zero error using following expression

iif(isnull([MyField1]) and isnull([MyField2]),1, [MyField1]/
[MyField2])

It should return 1, if both fields are null.
Please let me know if I am wrong.

Your information is great appreciated,
Click to expand...
Click to expand...
 
J

John Spencer

I don't see anything wrong with the IIF other than I would specify the
value to be returned by the NZ function. If MyField1 or MyField2 is a
text field then you will get a zero-length string instead of zero.

IIF((Isnull(Myfield1) AND IsNull(MyField2)), 1,
IIF((NZ(MyField1,0) = 0 OR NZ(MyField2,0) = 0)),0,
(Myfield1/MyField2))

Also you talk about three queries. So I'm not sure what your query
actually is. If No record is returned from one query and you are using
an outer join (left Join or Right Join) then the field is Null and will
never be zero.


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


kc-mass said:
Hi John

For my own edification please tell me how this fails:

The logic is wrong.
Try:
IIF((Isnull(Myfield1) AND IsNull(MyField2)), 1,IIf((NZ(MyField1) = 0 OR
NZ(MyField2) = 0)),0,(Myfield1/MyField2))

Kevin



John Spencer said:
Null and ZERO are not the same. Null is the absence of a value. Zero is a
value.

So the question is what do you want if either field is null, both fields
are null, or if field2 is zero.

IIF(Field1 is Null and Field2 is Null,0
IIF(Field1 is Null, 0
IIF (Field2 = 0,0,Field1/Field2)))

Now, that will return
zero if both field are null
zero if field1 is null
zero if field 2 = 0
null if field1 is null and field2 has a value
a number if field1 has a value and field2 has a value

In other words there are several cases
Field 1 is Null or Not Null
Field 2 is Null or Zero or some other number

So you have up to six cases you need to handle.
Field1 is null // Field 2 is Null
Field1 is Null // Field2 = 0
Field1 is null // Field2 is some value other than zero
and then three cases where field1 is not null

You may be able to condense these down into just a few cases depending on
you rules. For instance if Field 1 is Null or zero always return zero.

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

I have one query which get data from 3 queries.

I want to return 1 when 2 qeries return no record.
I use iif(isnull([MyField1]),1, MyField1) which works if only one
query returns zero record, if 2 queries return zero record then I get
divided by zero error using following expression

iif(isnull([MyField1]) and isnull([MyField2]),1, [MyField1]/
[MyField2])

It should return 1, if both fields are null.
Please let me know if I am wrong.

Your information is great appreciated,
Click to expand...
Click to expand...
Click to expand...
 
K

kc-mass

John

I never mentioned any queries never mind three and if MyField1 is text then
the whole thing will blow up in
(Myfield1/MyField2) or your "Field1/Field2". If Field1 = "Smith" and field2
= 2 - what will be the result?

Doesn't any numeric field processed through the NZ function return 0 if it
is Null or Zero or its value if it isn't?

Confused.






John Spencer said:
I don't see anything wrong with the IIF other than I would specify the
value to be returned by the NZ function. If MyField1 or MyField2 is a text
field then you will get a zero-length string instead of zero.

IIF((Isnull(Myfield1) AND IsNull(MyField2)), 1,
IIF((NZ(MyField1,0) = 0 OR NZ(MyField2,0) = 0)),0,
(Myfield1/MyField2))

Also you talk about three queries. So I'm not sure what your query
actually is. If No record is returned from one query and you are using an
outer join (left Join or Right Join) then the field is Null and will never
be zero.


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


kc-mass said:
Hi John

For my own edification please tell me how this fails:

The logic is wrong.
Try:
IIF((Isnull(Myfield1) AND IsNull(MyField2)), 1,IIf((NZ(MyField1) = 0 OR
NZ(MyField2) = 0)),0,(Myfield1/MyField2))

Kevin



John Spencer said:
Null and ZERO are not the same. Null is the absence of a value. Zero is
a value.

So the question is what do you want if either field is null, both fields
are null, or if field2 is zero.

IIF(Field1 is Null and Field2 is Null,0
IIF(Field1 is Null, 0
IIF (Field2 = 0,0,Field1/Field2)))

Now, that will return
zero if both field are null
zero if field1 is null
zero if field 2 = 0
null if field1 is null and field2 has a value
a number if field1 has a value and field2 has a value

In other words there are several cases
Field 1 is Null or Not Null
Field 2 is Null or Zero or some other number

So you have up to six cases you need to handle.
Field1 is null // Field 2 is Null
Field1 is Null // Field2 = 0
Field1 is null // Field2 is some value other than zero
and then three cases where field1 is not null

You may be able to condense these down into just a few cases depending
on you rules. For instance if Field 1 is Null or zero always return
zero.

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


inungh wrote:
I have one query which get data from 3 queries.

I want to return 1 when 2 qeries return no record.
I use iif(isnull([MyField1]),1, MyField1) which works if only one
query returns zero record, if 2 queries return zero record then I get
divided by zero error using following expression

iif(isnull([MyField1]) and isnull([MyField2]),1, [MyField1]/
[MyField2])

It should return 1, if both fields are null.
Please let me know if I am wrong.

Your information is great appreciated,
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Sorry, I was confused. I was looking at the original post.

I thought you were saying that there was a problem with your IIF
statement. I was struggling to find something wrong with it. And I
couldn't really see much of a reason for it to fail. So I was trying to
think of any reason for it to fail and probably went overboard.

My earlier post was referring to the original poster and what he or she
might want to see returned depending on the different values in the fields.

If both fields are Null
If one field is null and the other is zero
If both fields are zero
If one field is null and the other has a value
If both fields have a value other than zero
If one field has a value other than zero and the other is null

In many cases, the answer might be
if any field is null then return null
If the divisor is zero return null
In all other cases return the result of the division.

Which breaks down to a rather simple statement
IIF(Field2=0,0,Field1/Field2)


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


kc-mass said:
John

I never mentioned any queries never mind three and if MyField1 is text then
the whole thing will blow up in
(Myfield1/MyField2) or your "Field1/Field2". If Field1 = "Smith" and field2
= 2 - what will be the result?

Doesn't any numeric field processed through the NZ function return 0 if it
is Null or Zero or its value if it isn't?

Confused.






John Spencer said:
I don't see anything wrong with the IIF other than I would specify the
value to be returned by the NZ function. If MyField1 or MyField2 is a text
field then you will get a zero-length string instead of zero.

IIF((Isnull(Myfield1) AND IsNull(MyField2)), 1,
IIF((NZ(MyField1,0) = 0 OR NZ(MyField2,0) = 0)),0,
(Myfield1/MyField2))

Also you talk about three queries. So I'm not sure what your query
actually is. If No record is returned from one query and you are using an
outer join (left Join or Right Join) then the field is Null and will never
be zero.


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


kc-mass said:
Hi John

For my own edification please tell me how this fails:

The logic is wrong.
Try:
IIF((Isnull(Myfield1) AND IsNull(MyField2)), 1,IIf((NZ(MyField1) = 0 OR
NZ(MyField2) = 0)),0,(Myfield1/MyField2))

Kevin



Null and ZERO are not the same. Null is the absence of a value. Zero is
a value.

So the question is what do you want if either field is null, both fields
are null, or if field2 is zero.

IIF(Field1 is Null and Field2 is Null,0
IIF(Field1 is Null, 0
IIF (Field2 = 0,0,Field1/Field2)))

Now, that will return
zero if both field are null
zero if field1 is null
zero if field 2 = 0
null if field1 is null and field2 has a value
a number if field1 has a value and field2 has a value

In other words there are several cases
Field 1 is Null or Not Null
Field 2 is Null or Zero or some other number

So you have up to six cases you need to handle.
Field1 is null // Field 2 is Null
Field1 is Null // Field2 = 0
Field1 is null // Field2 is some value other than zero
and then three cases where field1 is not null

You may be able to condense these down into just a few cases depending
on you rules. For instance if Field 1 is Null or zero always return
zero.

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


inungh wrote:
I have one query which get data from 3 queries.

I want to return 1 when 2 qeries return no record.
I use iif(isnull([MyField1]),1, MyField1) which works if only one
query returns zero record, if 2 queries return zero record then I get
divided by zero error using following expression

iif(isnull([MyField1]) and isnull([MyField2]),1, [MyField1]/
[MyField2])

It should return 1, if both fields are null.
Please let me know if I am wrong.

Your information is great appreciated,
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

IsError function 1
sub query 3
Division by Zero Error 1
Iif/IsNull Question 6
Testing for null 4
stack query from a group query 1
Field query expression 1
crosstab query 1

Top