Null value question - simple.. should be anyway

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hey all,

I have trouble figuring out a simple way to do the following:

1) I have two tables of vendors, one of which is from this month and the
other from last month.

2) I'm trying to determine for which vendors changes took place.

This is a very straightforward query, but I am running into problems where
fields (e.g. address line1) contain null values in both tables. Now I
expected the standard query to see that both fields where the same (is null)
and should therefore not earmark it as 'changed'... however it does.

Now I am trying to build a query that in case both fields are empty (is
null) that it should return me a value. That value I will use as a condition
to exclude it.

I've done the following:

test4: IIf([DUB Vendor Master - Import]![Vendor Address Line1] Is Null,1 And
IIf([DUB Vendor Master - OLD]![Vendor Address Line1] Is Null,1,0))


Both fields for a particular vendor are null. However instead of the
expected value '1', it returns '-1'... which has me puzzled


any clues...? hints... tips...? What I am doing wrong?

cheers,
mischa
 
I believe what you want is the following

test4: IIf([DUB Vendor Master - Import]![Vendor Address Line1] Is Null And
[DUB Vendor Master - OLD]![Vendor Address Line1] Is Null,1,0)

Another way of doing this would be to use the NZ function to reutrn True
(-1) or False (0)
NZ([DUB Vendor Master - Import]![Vendor Address Line1],"ZZ") = NZ([DUB
Vendor Master - OLD]![Vendor Address Line1] ,"ZZ)"

The reason your expression was returning -1 was that it was true (or at
least it wasn't zero).
 
Thanks John - that was indeed the query I was looking for!

best regards,
mischa

John Spencer said:
I believe what you want is the following

test4: IIf([DUB Vendor Master - Import]![Vendor Address Line1] Is Null And
[DUB Vendor Master - OLD]![Vendor Address Line1] Is Null,1,0)

Another way of doing this would be to use the NZ function to reutrn True
(-1) or False (0)
NZ([DUB Vendor Master - Import]![Vendor Address Line1],"ZZ") = NZ([DUB
Vendor Master - OLD]![Vendor Address Line1] ,"ZZ)"

The reason your expression was returning -1 was that it was true (or at
least it wasn't zero).

Mischa said:
Hey all,

I have trouble figuring out a simple way to do the following:

1) I have two tables of vendors, one of which is from this month and the
other from last month.

2) I'm trying to determine for which vendors changes took place.

This is a very straightforward query, but I am running into problems where
fields (e.g. address line1) contain null values in both tables. Now I
expected the standard query to see that both fields where the same (is
null)
and should therefore not earmark it as 'changed'... however it does.

Now I am trying to build a query that in case both fields are empty (is
null) that it should return me a value. That value I will use as a
condition
to exclude it.

I've done the following:

test4: IIf([DUB Vendor Master - Import]![Vendor Address Line1] Is Null,1
And
IIf([DUB Vendor Master - OLD]![Vendor Address Line1] Is Null,1,0))


Both fields for a particular vendor are null. However instead of the
expected value '1', it returns '-1'... which has me puzzled


any clues...? hints... tips...? What I am doing wrong?

cheers,
mischa
Click to expand...
Click to expand...
 
Back
Top