Re: Null value question - simple.. should be anyway

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" <Mischa@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:19884522-7FBB-408F-9E61-37DBE50080FE@xxxxxxxxxxxxxxxx
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


.

Relevant Pages

  • Re: Form in macro to not open if query result is empty recordset
    ... basically a redisign of a previous tool that was written solely in vba. ... macro means that one will not run until the previous query has completed. ... If not all the vendors will be in your vendor list and you have RI ...
    (microsoft.public.access.forms)
  • Re: Form in macro to not open if query result is empty recordset
    ... is if the recognition process does not find any new vendors the user is still ... important that I stay away from VBA unless absolutely necessary. ... event...so if code is evoked before teh previous code (generally a query) has ... macro means that one will not run until the previous query has completed. ...
    (microsoft.public.access.forms)
  • Re: Using VBA to Loop through Access Table
    ... VBA is the only way to automate the process so a non-programmer could run ... UNION query, but to do that, VBA would need data from other tables. ... normalize the data: ... There are about 1,000 Parts and many Vendors that are subject to change. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Form in macro to not open if query result is empty recordset
    ... not queries because they do not hold data -- information on what your tables contain and how your tables are related ... ... Once you make the temporary table, is there Referential Integrity on the relationship between Vendors and the related tables? ... it is necessary to use VBA. ... Another consideration is that vba will run because of an event...so if code is evoked before teh previous code (generally a query) has completed it tends to make the tool bomb out...having the queries run in a macro means that one will not run until the previous query has completed. ...
    (microsoft.public.access.forms)
  • Re: Form in macro to not open if query result is empty recordset
    ... If several people will be working with your process, whether it is VBA or macros, it is especially important to add comments. ... Another consideration is that vba will run because of an event...so if code is evoked before teh previous code (generally a query) has completed it tends to make the tool bomb out...having the queries run in a macro means that one will not run until the previous query has completed. ... If not all the vendors will be in your vendor list and you have RI enforced, you will first need to add the new vendors before you append records to the vendors table. ...
    (microsoft.public.access.forms)