Update Query, updating wrong table

[matt17]

My update query should be updating tblTestUpdate.Each from
tblImportProducts.Each. My question is... why is it updating
tblImportProducts from tblTestUpdate instead.

The SQL for the update query:
UPDATE tblTestUpdate INNER JOIN tblImportProducts ON
tblImportProducts.MaterialNumber=tblTestUpdate.MaterialNumber SET
tblImportProducts.Each = tblTestUpdate!Each
WHERE (((tblImportProducts.Each)<>IsNull(tblImportProducts.Each)));

Thanks,
Matt Pierringer

 

[Guest]

That's what your SQL tells it to do.

SET tblImportProducts.Each = tblTestUpdate!Each

You need to swap the above statement around like so:

SET tblTestUpdate.Each = tblImportProducts!Each

 

[matt17]

Yeah I have had the set switched around before and all that did was
change where it was grabbing it from an updating the values in the
same spot.

Let me try to explain myself better, here are the values I am trying
to grab:

tblImportProducts!Each:
I set all the values to-
99999
99999
99999
99999

tblTestUpdate.Each:
I know the values are-
42.3
57.7
98.2
26.4

So when I reversed the SET it updated to the right table but grabbed
the wrong values, and before I changed the SET it would grab the right
values but it updated the wrong table. So, I guess I am closer to the
right combination, but I still need it to grab from the right table.

Thanks,
Matt Pierringer

 

[matt17]

Yeah I have had the set switched around before and all that did was
change where it was grabbing it from an updating the values in the
same spot.

Let me try to explain myself better, here are the values I am trying
to grab:

tblImportProducts!Each:
I set all the values to-
99999
99999
99999
99999

tblTestUpdate.Each:
I know the values are-
42.3
57.7
98.2
26.4

So when I reversed the SET it updated to the right table but grabbed
the wrong values, and before I changed the SET it would grab the right
values but it updated the wrong table. So, I guess I am closer to the
right combination, but I still need it to grab from the right table.

Thanks,
Matt Pierringer

I don't know why it didn't work before, but it works now... Thanks!!!

 

[matt17]

I don't know why it didn't work before, but it works now... Thanks!!!

Ok, well my problem is now I added in all the other values to have it
update, I followed the same format I currently have. When I go to
update it displays "Update 0 Rows" when I know that there is
information that is different... do I have to make 9 separate update
queries and then make a Union query to join them all together? I
guess I will get started on that, but it would be nice if I didn't
have to.

SQL:
UPDATE tblTestUpdate INNER JOIN tblImportProducts ON
tblTestUpdate.MaterialNumber = tblImportProducts.MaterialNumber SET
tblTestUpdate.Each = tblImportProducts!Each, tblTestUpdate.[Catalog] =
[tblImportProducts]![Catalog], tblTestUpdate.Manufacturer =
[tblImportProducts]![Manufacturer], tblTestUpdate.Category =
[tblImportProducts]![Category], tblTestUpdate.Description =
[tblImportProducts]![Description], tblTestUpdate.[Sub-Category] =
[tblImportProducts]![Sub-Category], tblTestUpdate.AddedNote =
[tblImportProducts]![AddedNote], tblTestUpdate.Required =
[tblImportProducts]![Required], tblTestUpdate.NoList =
[tblImportProducts]![NoList]
WHERE (((tblImportProducts.Each)<>IsNull([tblImportProducts].[Each]))
AND ((tblImportProducts.Catalog)<>IsNull([tblImportProducts].
[Catalog])) AND
((tblImportProducts.Manufacturer)<>IsNull([tblImportProducts].
[Manufacturer])) AND
((tblImportProducts.Category)<>IsNull([tblImportProducts].[Category]))
AND ((tblImportProducts.Description)<>IsNull([tblImportProducts].
[Description])) AND ((tblImportProducts.[Sub-
Category])<>IsNull([tblImportProducts].[Sub-Category])) AND
((tblImportProducts.AddedNote)<>IsNull([tblImportProducts].
[AddedNote])) AND
((tblImportProducts.Required)<>IsNull([tblImportProducts].[Required]))
AND ((tblImportProducts.NoList)<>IsNull([tblImportProducts].
[NoList])));


Thanks,
Matt Pierringer

 

[John Spencer]

No, you don't (actually you can't) union action queries. You would have to
run the 9 queries sequentially.

The reason you are getting zero records to update is that you have no
records that meet the criteria you have set up. IsNull() in Access is a VBA
function that checks to see if a value is Null or not. It returns true or
false. So

tblImportProducts.Each<>IsNull([tblImportProducts].[Each])
Is checking to see if tblImportProducts.Each is equal to True or False

Perhaps you can explain what you are trying to test for?
Do you want to know if tblImportProducts.Each is not Null? That test is
tblImportProducts is Null or

IsNull(tblImportProducts.Each) = False

If you are just testing to see if the fields are not null, then the query
requires all nine sets to be not nullbefore it will return any record to be
updated.

I've aliased the tables to make life easier for me

UPDATE tblTestUpdate as U INNER JOIN tblImportProducts as I
ON U.MaterialNumber = I.MaterialNumber
SET
U.Each = ![Each]
, U.[Catalog] =![Catalog]
, U.Manufacturer =![Manufacturer]
, U.Category =![Category]
, U.Description =![Description]
, U.[Sub-Category] =![Sub-Category]
, U.AddedNote =![AddedNote]
, U.Required =![Required]
, U.NoList =![NoList]

WHERE I.Each is not Null
OR I.Catalog is not Null
OR I.Manufacturer is not Null
OR I.Category is Not Null
OR I.Description is Not Null
OR I.[Sub-Category] is not Null
OR I.AddedNote is Not Null
OR I.[Required] is Not Null
OR I.NoList is Not Null

IF you want U.Each replace with I.Each unless I.Each is null you can use the
NZ Function, which says to use the first value unless it is null and then
use the second value.
For example.
U.Each = NZ(.[Each],.[Each])

So you could rewrite the above to do that for each item you are updating.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I don't know why it didn't work before, but it works now... Thanks!!!

Ok, well my problem is now I added in all the other values to have it
update, I followed the same format I currently have. When I go to
update it displays "Update 0 Rows" when I know that there is
information that is different... do I have to make 9 separate update
queries and then make a Union query to join them all together? I
guess I will get started on that, but it would be nice if I didn't
have to.

SQL:
UPDATE tblTestUpdate INNER JOIN tblImportProducts ON
tblTestUpdate.MaterialNumber = tblImportProducts.MaterialNumber SET
tblTestUpdate.Each = tblImportProducts!Each, tblTestUpdate.[Catalog] =
[tblImportProducts]![Catalog], tblTestUpdate.Manufacturer =
[tblImportProducts]![Manufacturer], tblTestUpdate.Category =
[tblImportProducts]![Category], tblTestUpdate.Description =
[tblImportProducts]![Description], tblTestUpdate.[Sub-Category] =
[tblImportProducts]![Sub-Category], tblTestUpdate.AddedNote =
[tblImportProducts]![AddedNote], tblTestUpdate.Required =
[tblImportProducts]![Required], tblTestUpdate.NoList =
[tblImportProducts]![NoList]
WHERE (((tblImportProducts.Each)<>IsNull([tblImportProducts].[Each]))
AND ((tblImportProducts.Catalog)<>IsNull([tblImportProducts].
[Catalog])) AND
((tblImportProducts.Manufacturer)<>IsNull([tblImportProducts].
[Manufacturer])) AND
((tblImportProducts.Category)<>IsNull([tblImportProducts].[Category]))
AND ((tblImportProducts.Description)<>IsNull([tblImportProducts].
[Description])) AND ((tblImportProducts.[Sub-
Category])<>IsNull([tblImportProducts].[Sub-Category])) AND
((tblImportProducts.AddedNote)<>IsNull([tblImportProducts].
[AddedNote])) AND
((tblImportProducts.Required)<>IsNull([tblImportProducts].[Required]))
AND ((tblImportProducts.NoList)<>IsNull([tblImportProducts].
[NoList])));


Thanks,
Matt Pierringer

 

[matt17]

No, you don't (actually you can't) union action queries. You would have to
run the 9 queries sequentially.

The reason you are getting zero records to update is that you have no
records that meet the criteria you have set up. IsNull() in Access is a VBA
function that checks to see if a value is Null or not. It returns true or
false. So

tblImportProducts.Each<>IsNull([tblImportProducts].[Each])
Is checking to see if tblImportProducts.Each is equal to True or False

Perhaps you can explain what you are trying to test for?
Do you want to know if tblImportProducts.Each is not Null? That test is
tblImportProducts is Null or

IsNull(tblImportProducts.Each) = False

If you are just testing to see if the fields are not null, then the query
requires all nine sets to be not nullbefore it will return any record to be
updated.

I've aliased the tables to make life easier for me

UPDATE tblTestUpdate as U INNER JOIN tblImportProducts as I
ON U.MaterialNumber = I.MaterialNumber
SET
U.Each = ![Each]
, U.[Catalog] =![Catalog]
, U.Manufacturer =![Manufacturer]
, U.Category =![Category]
, U.Description =![Description]
, U.[Sub-Category] =![Sub-Category]
, U.AddedNote =![AddedNote]
, U.Required =![Required]
, U.NoList =![NoList]

WHERE I.Each is not Null
OR I.Catalog is not Null
OR I.Manufacturer is not Null
OR I.Category is Not Null
OR I.Description is Not Null
OR I.[Sub-Category] is not Null
OR I.AddedNote is Not Null
OR I.[Required] is Not Null
OR I.NoList is Not Null

IF you want U.Each replace with I.Each unless I.Each is null you can use the
NZ Function, which says to use the first value unless it is null and then
use the second value.
For example.
U.Each = NZ(.[Each],.[Each])

So you could rewrite the above to do that for each item you are updating.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Ok, well my problem is now I added in all the other values to have it
update, I followed the same format I currently have. When I go to
update it displays "Update 0 Rows" when I know that there is
information that is different... do I have to make 9 separate update
queries and then make a Union query to join them all together? I
guess I will get started on that, but it would be nice if I didn't
have to.

SQL:
UPDATE tblTestUpdate INNER JOIN tblImportProducts ON
tblTestUpdate.MaterialNumber = tblImportProducts.MaterialNumber SET
tblTestUpdate.Each = tblImportProducts!Each, tblTestUpdate.[Catalog] =
[tblImportProducts]![Catalog], tblTestUpdate.Manufacturer =
[tblImportProducts]![Manufacturer], tblTestUpdate.Category =
[tblImportProducts]![Category], tblTestUpdate.Description =
[tblImportProducts]![Description], tblTestUpdate.[Sub-Category] =
[tblImportProducts]![Sub-Category], tblTestUpdate.AddedNote =
[tblImportProducts]![AddedNote], tblTestUpdate.Required =
[tblImportProducts]![Required], tblTestUpdate.NoList =
[tblImportProducts]![NoList]
WHERE (((tblImportProducts.Each)<>IsNull([tblImportProducts].[Each]))
AND ((tblImportProducts.Catalog)<>IsNull([tblImportProducts].
[Catalog])) AND
((tblImportProducts.Manufacturer)<>IsNull([tblImportProducts].
[Manufacturer])) AND
((tblImportProducts.Category)<>IsNull([tblImportProducts].[Category]))
AND ((tblImportProducts.Description)<>IsNull([tblImportProducts].
[Description])) AND ((tblImportProducts.[Sub-
Category])<>IsNull([tblImportProducts].[Sub-Category])) AND
((tblImportProducts.AddedNote)<>IsNull([tblImportProducts].
[AddedNote])) AND
((tblImportProducts.Required)<>IsNull([tblImportProducts].[Required]))
AND ((tblImportProducts.NoList)<>IsNull([tblImportProducts].
[NoList])));

Thanks,
Matt Pierringer

Here is what I am trying to do: I have a form setup so that people
can select an excel file and then the form updates a "resting"
table(tblTestImport) with the new information in the designated
fields. I then made the update query to find out what the new
information was which would update only the fields that contained
text(<>Null). The only thing I always want to stay the same is the
MaterialNumber field and I want it to be matched by this. The update
query would then take the information from the resting table and dump
the usable information into my real table(tblTestUpdate). The whole
database is a catalog which is constantly changing with prices and
other information stemming off of the MaterialNumber.

Thanks for your help, I hope that gave you a better understanding... I
don't think the Nz func would work.

 

[John Spencer]

Actually, I do think that using the NZ would work for you.

You want to update tblTestUpdate with information if the equivalent
tblImportProducts is not null. If it is null, you want to retain the value.

The choices I see are to do one field at a time in separate Update queries
or to do something like the following.

UPDATE tblTestUpdate as U INNER JOIN tblImportProducts as I
ON U.MaterialNumber = I.MaterialNumber
SET
U.Each = NZ(.[Each],.[Each])
, U.[Catalog] = NZ(.[Catalog],.[Catalog])
, U.Manufacturer = Nz(.[Manufacturer], .[Manufacturer])
, U.Category = Nz(.[Category] , .[Category])
, U.Description = Nz(.[Description] , .[Description])
, U.[Sub-Category] = Nz(.[Sub-Category] , .[Sub-category])
, U.AddedNote = Nz(.[AddedNote] , .[AddedNote])
, U.Required = Nz(.[Required] , .[Required])
, U.NoList = Nz(.[NoList] , .[NoList])

WHERE I.Each is not Null
OR I.Catalog is not Null
OR I.Manufacturer is not Null
OR I.Category is Not Null
OR I.Description is Not Null
OR I.[Sub-Category] is not Null
OR I.AddedNote is Not Null
OR I.[Required] is Not Null
OR I.NoList is Not Null



You can make the update criteria more complex by using something like the
following which will eliminate records where the values are equal.
WHERE (I.Each is Not Null and (U.Each is Null Or U.Each <> I.Each))
OR (I.Catalog is Not Null and (U.Catalog is Null or U.Catalog <> I.Catalog))
OR ...


Actually, you can simplify that by using the following. This might be
slower but not by much if you don't have indexes set up on the fields.
WHERE Nz(I.Each,"") <> Nz(U.Each,"") OR NZ(I.Catalog,"")<> NZ(U.Catalog,"")
OR ...

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

No, you don't (actually you can't) union action queries. You would have
to
run the 9 queries sequentially.

The reason you are getting zero records to update is that you have no
records that meet the criteria you have set up. IsNull() in Access is a
VBA
function that checks to see if a value is Null or not. It returns true
or
false. So

tblImportProducts.Each<>IsNull([tblImportProducts].[Each])
Is checking to see if tblImportProducts.Each is equal to True or False

Perhaps you can explain what you are trying to test for?
Do you want to know if tblImportProducts.Each is not Null? That test
is
tblImportProducts is Null or

IsNull(tblImportProducts.Each) = False

If you are just testing to see if the fields are not null, then the query
requires all nine sets to be not nullbefore it will return any record to
be
updated.

I've aliased the tables to make life easier for me

UPDATE tblTestUpdate as U INNER JOIN tblImportProducts as I
ON U.MaterialNumber = I.MaterialNumber
SET
U.Each = ![Each]
, U.[Catalog] =![Catalog]
, U.Manufacturer =![Manufacturer]
, U.Category =![Category]
, U.Description =![Description]
, U.[Sub-Category] =![Sub-Category]
, U.AddedNote =![AddedNote]
, U.Required =![Required]
, U.NoList =![NoList]

WHERE I.Each is not Null
OR I.Catalog is not Null
OR I.Manufacturer is not Null
OR I.Category is Not Null
OR I.Description is Not Null
OR I.[Sub-Category] is not Null
OR I.AddedNote is Not Null
OR I.[Required] is Not Null
OR I.NoList is Not Null

IF you want U.Each replace with I.Each unless I.Each is null you can use
the
NZ Function, which says to use the first value unless it is null and then
use the second value.
For example.
U.Each = NZ(.[Each],.[Each])

So you could rewrite the above to do that for each item you are updating.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

On Feb 2, 8:07 am, "(e-mail address removed)" <[email protected]> wrote:

Yeah I have had the set switched around before and all that did was
change where it was grabbing it from an updating the values in the
same spot.

Let me try to explain myself better, here are the values I am trying
to grab:

tblImportProducts!Each:
I set all the values to-
99999
99999
99999
99999

tblTestUpdate.Each:
I know the values are-
42.3
57.7
98.2
26.4

So when I reversed the SET it updated to the right table but grabbed
the wrong values, and before I changed the SET it would grab the
right
values but it updated the wrong table. So, I guess I am closer to
the
right combination, but I still need it to grab from the right table.

Thanks,
Matt Pierringer

I don't know why it didn't work before, but it works now... Thanks!!!

Ok, well my problem is now I added in all the other values to have it
update, I followed the same format I currently have. When I go to
update it displays "Update 0 Rows" when I know that there is
information that is different... do I have to make 9 separate update
queries and then make a Union query to join them all together? I
guess I will get started on that, but it would be nice if I didn't
have to.

SQL:
UPDATE tblTestUpdate INNER JOIN tblImportProducts ON
tblTestUpdate.MaterialNumber = tblImportProducts.MaterialNumber SET
tblTestUpdate.Each = tblImportProducts!Each, tblTestUpdate.[Catalog] =
[tblImportProducts]![Catalog], tblTestUpdate.Manufacturer =
[tblImportProducts]![Manufacturer], tblTestUpdate.Category =
[tblImportProducts]![Category], tblTestUpdate.Description =
[tblImportProducts]![Description], tblTestUpdate.[Sub-Category] =
[tblImportProducts]![Sub-Category], tblTestUpdate.AddedNote =
[tblImportProducts]![AddedNote], tblTestUpdate.Required =
[tblImportProducts]![Required], tblTestUpdate.NoList =
[tblImportProducts]![NoList]
WHERE (((tblImportProducts.Each)<>IsNull([tblImportProducts].[Each]))
AND ((tblImportProducts.Catalog)<>IsNull([tblImportProducts].
[Catalog])) AND
((tblImportProducts.Manufacturer)<>IsNull([tblImportProducts].
[Manufacturer])) AND
((tblImportProducts.Category)<>IsNull([tblImportProducts].[Category]))
AND ((tblImportProducts.Description)<>IsNull([tblImportProducts].
[Description])) AND ((tblImportProducts.[Sub-
Category])<>IsNull([tblImportProducts].[Sub-Category])) AND
((tblImportProducts.AddedNote)<>IsNull([tblImportProducts].
[AddedNote])) AND
((tblImportProducts.Required)<>IsNull([tblImportProducts].[Required]))
AND ((tblImportProducts.NoList)<>IsNull([tblImportProducts].
[NoList])));

Thanks,
Matt Pierringer

Here is what I am trying to do: I have a form setup so that people
can select an excel file and then the form updates a "resting"
table(tblTestImport) with the new information in the designated
fields. I then made the update query to find out what the new
information was which would update only the fields that contained
text(<>Null). The only thing I always want to stay the same is the
MaterialNumber field and I want it to be matched by this. The update
query would then take the information from the resting table and dump
the usable information into my real table(tblTestUpdate). The whole
database is a catalog which is constantly changing with prices and
other information stemming off of the MaterialNumber.

Thanks for your help, I hope that gave you a better understanding... I
don't think the Nz func would work.

 

[Matt Pierringer]

Actually, I do think that using the NZ would work for you.

You want to update tblTestUpdate with information if the equivalent
tblImportProducts is not null. If it is null, you want to retain the value.

The choices I see are to do one field at a time in separate Update queries
or to do something like the following.

UPDATE tblTestUpdate as U INNER JOIN tblImportProducts as I
ON U.MaterialNumber = I.MaterialNumber
SET
U.Each = NZ(.[Each],.[Each])
, U.[Catalog] = NZ(.[Catalog],.[Catalog])
, U.Manufacturer = Nz(.[Manufacturer], .[Manufacturer])
, U.Category = Nz(.[Category] , .[Category])
, U.Description = Nz(.[Description] , .[Description])
, U.[Sub-Category] = Nz(.[Sub-Category] , .[Sub-category])
, U.AddedNote = Nz(.[AddedNote] , .[AddedNote])
, U.Required = Nz(.[Required] , .[Required])
, U.NoList = Nz(.[NoList] , .[NoList])

WHERE I.Each is not Null
OR I.Catalog is not Null
OR I.Manufacturer is not Null
OR I.Category is Not Null
OR I.Description is Not Null
OR I.[Sub-Category] is not Null
OR I.AddedNote is Not Null
OR I.[Required] is Not Null
OR I.NoList is Not Null

You can make the update criteria more complex by using something like the
following which will eliminate records where the values are equal.
WHERE (I.Each is Not Null and (U.Each is Null Or U.Each <> I.Each))
OR (I.Catalog is Not Null and (U.Catalog is Null or U.Catalog <> I.Catalog))
OR ...

Actually, you can simplify that by using the following. This might be
slower but not by much if you don't have indexes set up on the fields.
WHERE Nz(I.Each,"") <> Nz(U.Each,"") OR NZ(I.Catalog,"")<> NZ(U.Catalog,"")
OR ...

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

No, you don't (actually you can't) union action queries. You would have
to
run the 9 queries sequentially.
The reason you are getting zero records to update is that you have no
records that meet the criteria you have set up. IsNull() in Access is a
VBA
function that checks to see if a value is Null or not. It returns true
or
false. So
tblImportProducts.Each<>IsNull([tblImportProducts].[Each])
Is checking to see if tblImportProducts.Each is equal to True or False
Perhaps you can explain what you are trying to test for?
Do you want to know if tblImportProducts.Each is not Null? That test
is
tblImportProducts is Null or
IsNull(tblImportProducts.Each) = False
If you are just testing to see if the fields are not null, then the query
requires all nine sets to be not nullbefore it will return any record to
be
updated.
I've aliased the tables to make life easier for me
UPDATE tblTestUpdate as U INNER JOIN tblImportProducts as I
ON U.MaterialNumber = I.MaterialNumber
SET
U.Each = ![Each]
, U.[Catalog] =![Catalog]
, U.Manufacturer =![Manufacturer]
, U.Category =![Category]
, U.Description =![Description]
, U.[Sub-Category] =![Sub-Category]
, U.AddedNote =![AddedNote]
, U.Required =![Required]
, U.NoList =![NoList]
WHERE I.Each is not Null
OR I.Catalog is not Null
OR I.Manufacturer is not Null
OR I.Category is Not Null
OR I.Description is Not Null
OR I.[Sub-Category] is not Null
OR I.AddedNote is Not Null
OR I.[Required] is Not Null
OR I.NoList is Not Null
IF you want U.Each replace with I.Each unless I.Each is null you can use
the
NZ Function, which says to use the first value unless it is null and then
use the second value.
For example.
U.Each = NZ(.[Each],.[Each])
So you could rewrite the above to do that for each item you are updating.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Yeah I have had the set switched around before and all that did was
change where it was grabbing it from an updating the values in the
same spot.
Let me try to explain myself better, here are the values I am trying
to grab:
tblImportProducts!Each:
I set all the values to-
99999
99999
99999
99999
tblTestUpdate.Each:
I know the values are-
42.3
57.7
98.2
26.4
So when I reversed the SET it updated to the right table but grabbed
the wrong values, and before I changed the SET it would grab the
right
values but it updated the wrong table. So, I guess I am closer to
the
right combination, but I still need it to grab from the right table.
Thanks,
Matt Pierringer
I don't know why it didn't work before, but it works now... Thanks!!!
Ok, well my problem is now I added in all the other values to have it
update, I followed the same format I currently have. When I go to
update it displays "Update 0 Rows" when I know that there is
information that is different... do I have to make 9 separate update
queries and then make a Union query to join them all together? I
guess I will get started on that, but it would be nice if I didn't
have to.
SQL:
UPDATE tblTestUpdate INNER JOIN tblImportProducts ON
tblTestUpdate.MaterialNumber = tblImportProducts.MaterialNumber SET
tblTestUpdate.Each = tblImportProducts!Each, tblTestUpdate.[Catalog] =
[tblImportProducts]![Catalog], tblTestUpdate.Manufacturer =
[tblImportProducts]![Manufacturer], tblTestUpdate.Category =
[tblImportProducts]![Category], tblTestUpdate.Description =
[tblImportProducts]![Description], tblTestUpdate.[Sub-Category] =
[tblImportProducts]![Sub-Category], tblTestUpdate.AddedNote =
[tblImportProducts]![AddedNote], tblTestUpdate.Required =
[tblImportProducts]![Required], tblTestUpdate.NoList =
[tblImportProducts]![NoList]
WHERE (((tblImportProducts.Each)<>IsNull([tblImportProducts].[Each]))
AND ((tblImportProducts.Catalog)<>IsNull([tblImportProducts].
[Catalog])) AND
((tblImportProducts.Manufacturer)<>IsNull([tblImportProducts].
[Manufacturer])) AND
((tblImportProducts.Category)<>IsNull([tblImportProducts].[Category]))
AND ((tblImportProducts.Description)<>IsNull([tblImportProducts].
[Description])) AND ((tblImportProducts.[Sub-
Category])<>IsNull([tblImportProducts].[Sub-Category])) AND
((tblImportProducts.AddedNote)<>IsNull([tblImportProducts].
[AddedNote])) AND
((tblImportProducts.Required)<>IsNull([tblImportProducts].[Required]))
AND ((tblImportProducts.NoList)<>IsNull([tblImportProducts].
[NoList])));
Thanks,
Matt Pierringer

Here is what I am trying to do: I have a form setup so that people
can select an excel file and then the form updates a "resting"
table(tblTestImport) with the new information in the designated
fields. I then made the update query to find out what the new
information was which would update only the fields that contained
text(<>Null). The only thing I always want to stay the same is the
MaterialNumber field and I want it to be matched by this. The update
query would then take the information from the resting table and dump
the usable information into my real table(tblTestUpdate). The whole
database is a catalog which is constantly changing with prices and
other information stemming off of the MaterialNumber.

Thanks for your help, I hope that gave you a better understanding... I
don't think the Nz func would work.

Yeah your simplified Nz worked great, thanks! It is nice seeing by
example how things work

Matt Pierringer