If Null Update

A

Aaron

Hello,

I am trying to create a update qry that if the value in tbl ITEM is null
update from table SUB on Field Description. So far I have..

Field: Description
Table: SUB
Update to: [tblItemData]![Description]

Thanks in advance, Aaron
 
K

Klatuu

Use the Nz function. It returns the value in the first argument if it is not
null. If it is, it returns the value in the second argument.

Field: Description
Table: SUB
Update to: Nz([tblItemData]![Description], [Sub]![Description])
 
A

Aaron

Hi klatuu,

I have put this is the qry and ran it but it says it changes zero rows.
I have the two fields in both tables linked within the qry. Is there
something else I need to do? I can see that the tbl SUB has data that needs
to be updated in tblItemData.
 
K

Klatuu

What you posted show you updating the Sub table, not tblItemData. Which is
it you want to do?
--
Dave Hargis, Microsoft Access MVP


Aaron said:
Hi klatuu,

I have put this is the qry and ran it but it says it changes zero rows.
I have the two fields in both tables linked within the qry. Is there
something else I need to do? I can see that the tbl SUB has data that needs
to be updated in tblItemData.



Klatuu said:
Use the Nz function. It returns the value in the first argument if it is not
null. If it is, it returns the value in the second argument.

Field: Description
Table: SUB
Update to: Nz([tblItemData]![Description], [Sub]![Description])
Click to expand...
Click to expand...
 
A

Aaron

I am wanting to update tblItemData from table SUB

Klatuu said:
What you posted show you updating the Sub table, not tblItemData. Which is
it you want to do?
--
Dave Hargis, Microsoft Access MVP


Aaron said:
Hi klatuu,

I have put this is the qry and ran it but it says it changes zero rows.
I have the two fields in both tables linked within the qry. Is there
something else I need to do? I can see that the tbl SUB has data that needs
to be updated in tblItemData.



Klatuu said:
Use the Nz function. It returns the value in the first argument if it is not
null. If it is, it returns the value in the second argument.

Field: Description
Table: SUB
Update to: Nz([tblItemData]![Description], [Sub]![Description])
Click to expand...
Click to expand...
Click to expand...
 
K

Klatuu

If you want to update the Description field in tblItemData with the
Description field in Sub where the Description field in tblItemData is Null
then use this:

Field: Description
Table: tblItemData
Update to: [Sub]![Description]
Criteria: Is Null

--
Dave Hargis, Microsoft Access MVP


Aaron said:
I am wanting to update tblItemData from table SUB

Klatuu said:
What you posted show you updating the Sub table, not tblItemData. Which is
it you want to do?
--
Dave Hargis, Microsoft Access MVP


Aaron said:
Hi klatuu,

I have put this is the qry and ran it but it says it changes zero rows.
I have the two fields in both tables linked within the qry. Is there
something else I need to do? I can see that the tbl SUB has data that needs
to be updated in tblItemData.



:

Use the Nz function. It returns the value in the first argument if it is not
null. If it is, it returns the value in the second argument.

Field: Description
Table: SUB
Update to: Nz([tblItemData]![Description], [Sub]![Description])
Click to expand...
Click to expand...
Click to expand...
 
A

Aaron

Yes, this is exactly what I want to do. I put this and it still says
updating Zero rows?


Klatuu said:
If you want to update the Description field in tblItemData with the
Description field in Sub where the Description field in tblItemData is Null
then use this:

Field: Description
Table: tblItemData
Update to: [Sub]![Description]
Criteria: Is Null

--
Dave Hargis, Microsoft Access MVP


Aaron said:
I am wanting to update tblItemData from table SUB

Klatuu said:
What you posted show you updating the Sub table, not tblItemData. Which is
it you want to do?
--
Dave Hargis, Microsoft Access MVP


:

Hi klatuu,

I have put this is the qry and ran it but it says it changes zero rows.
I have the two fields in both tables linked within the qry. Is there
something else I need to do? I can see that the tbl SUB has data that needs
to be updated in tblItemData.



:

Use the Nz function. It returns the value in the first argument if it is not
null. If it is, it returns the value in the second argument.

Field: Description
Table: SUB
Update to: Nz([tblItemData]![Description], [Sub]![Description])
Click to expand...
Click to expand...
Click to expand...
 
K

Klatuu

Post the SQL of your query, please.
--
Dave Hargis, Microsoft Access MVP


Aaron said:
Yes, this is exactly what I want to do. I put this and it still says
updating Zero rows?


Klatuu said:
If you want to update the Description field in tblItemData with the
Description field in Sub where the Description field in tblItemData is Null
then use this:

Field: Description
Table: tblItemData
Update to: [Sub]![Description]
Criteria: Is Null

--
Dave Hargis, Microsoft Access MVP


Aaron said:
I am wanting to update tblItemData from table SUB

:

What you posted show you updating the Sub table, not tblItemData. Which is
it you want to do?
--
Dave Hargis, Microsoft Access MVP


:

Hi klatuu,

I have put this is the qry and ran it but it says it changes zero rows.
I have the two fields in both tables linked within the qry. Is there
something else I need to do? I can see that the tbl SUB has data that needs
to be updated in tblItemData.



:

Use the Nz function. It returns the value in the first argument if it is not
null. If it is, it returns the value in the second argument.

Field: Description
Table: SUB
Update to: Nz([tblItemData]![Description], [Sub]![Description])
Click to expand...
Click to expand...
Click to expand...
 
A

Aaron

UPDATE SubKAvl INNER JOIN tblItemData ON (SubKAvl.PN =
tblItemData.[Component PN]) AND (SubKAvl.Description =
tblItemData.Description) AND (SubKAvl.OEM = tblItemData.OEM) AND
(SubKAvl.OEM_PN = tblItemData.[OEM PN]) SET tblItemData.OEM = SubKAvl!OEM
WHERE (((tblItemData.OEM) Is Null));

Klatuu said:
Post the SQL of your query, please.
--
Dave Hargis, Microsoft Access MVP


Aaron said:
Yes, this is exactly what I want to do. I put this and it still says
updating Zero rows?


Klatuu said:
If you want to update the Description field in tblItemData with the
Description field in Sub where the Description field in tblItemData is Null
then use this:

Field: Description
Table: tblItemData
Update to: [Sub]![Description]
Criteria: Is Null

--
Dave Hargis, Microsoft Access MVP


:

I am wanting to update tblItemData from table SUB

:

What you posted show you updating the Sub table, not tblItemData. Which is
it you want to do?
--
Dave Hargis, Microsoft Access MVP


:

Hi klatuu,

I have put this is the qry and ran it but it says it changes zero rows.
I have the two fields in both tables linked within the qry. Is there
something else I need to do? I can see that the tbl SUB has data that needs
to be updated in tblItemData.



:

Use the Nz function. It returns the value in the first argument if it is not
null. If it is, it returns the value in the second argument.

Field: Description
Table: SUB
Update to: Nz([tblItemData]![Description], [Sub]![Description])
Click to expand...
Click to expand...
Click to expand...
 
K

Klatuu

Still updating the wrong table. You had joins that would filter out any
records you want to update. Try it this way:

UPDATE tblItemData INNER JOIN SubKAvl ON (SubKAvl.PN =
tblItemData.[Component PN]) SET tblItemData.OEM = SubKAvl!OEM
WHERE (((tblItemData.OEM) Is Null));
--
Dave Hargis, Microsoft Access MVP


Aaron said:
UPDATE SubKAvl INNER JOIN tblItemData ON (SubKAvl.PN =
tblItemData.[Component PN]) AND (SubKAvl.Description =
tblItemData.Description) AND (SubKAvl.OEM = tblItemData.OEM) AND
(SubKAvl.OEM_PN = tblItemData.[OEM PN]) SET tblItemData.OEM = SubKAvl!OEM
WHERE (((tblItemData.OEM) Is Null));

Klatuu said:
Post the SQL of your query, please.
--
Dave Hargis, Microsoft Access MVP


Aaron said:
Yes, this is exactly what I want to do. I put this and it still says
updating Zero rows?


:

If you want to update the Description field in tblItemData with the
Description field in Sub where the Description field in tblItemData is Null
then use this:

Field: Description
Table: tblItemData
Update to: [Sub]![Description]
Criteria: Is Null

--
Dave Hargis, Microsoft Access MVP


:

I am wanting to update tblItemData from table SUB

:

What you posted show you updating the Sub table, not tblItemData. Which is
it you want to do?
--
Dave Hargis, Microsoft Access MVP


:

Hi klatuu,

I have put this is the qry and ran it but it says it changes zero rows.
I have the two fields in both tables linked within the qry. Is there
something else I need to do? I can see that the tbl SUB has data that needs
to be updated in tblItemData.



:

Use the Nz function. It returns the value in the first argument if it is not
null. If it is, it returns the value in the second argument.

Field: Description
Table: SUB
Update to: Nz([tblItemData]![Description], [Sub]![Description])
Click to expand...
Click to expand...
Click to expand...
 
A

Aaron

Pasted your sql in and it errored saying that I cannot update a linked
table(SubKAvl)


Klatuu said:
Still updating the wrong table. You had joins that would filter out any
records you want to update. Try it this way:

UPDATE tblItemData INNER JOIN SubKAvl ON (SubKAvl.PN =
tblItemData.[Component PN]) SET tblItemData.OEM = SubKAvl!OEM
WHERE (((tblItemData.OEM) Is Null));
--
Dave Hargis, Microsoft Access MVP


Aaron said:
UPDATE SubKAvl INNER JOIN tblItemData ON (SubKAvl.PN =
tblItemData.[Component PN]) AND (SubKAvl.Description =
tblItemData.Description) AND (SubKAvl.OEM = tblItemData.OEM) AND
(SubKAvl.OEM_PN = tblItemData.[OEM PN]) SET tblItemData.OEM = SubKAvl!OEM
WHERE (((tblItemData.OEM) Is Null));

Klatuu said:
Post the SQL of your query, please.
--
Dave Hargis, Microsoft Access MVP


:

Yes, this is exactly what I want to do. I put this and it still says
updating Zero rows?


:

If you want to update the Description field in tblItemData with the
Description field in Sub where the Description field in tblItemData is Null
then use this:

Field: Description
Table: tblItemData
Update to: [Sub]![Description]
Criteria: Is Null

--
Dave Hargis, Microsoft Access MVP


:

I am wanting to update tblItemData from table SUB

:

What you posted show you updating the Sub table, not tblItemData. Which is
it you want to do?
--
Dave Hargis, Microsoft Access MVP


:

Hi klatuu,

I have put this is the qry and ran it but it says it changes zero rows.
I have the two fields in both tables linked within the qry. Is there
something else I need to do? I can see that the tbl SUB has data that needs
to be updated in tblItemData.



:

Use the Nz function. It returns the value in the first argument if it is not
null. If it is, it returns the value in the second argument.

Field: Description
Table: SUB
Update to: Nz([tblItemData]![Description], [Sub]![Description])
Click to expand...
Click to expand...
Click to expand...
 
K

Klatuu

Something is still wrong with your query. As I posted back, it should not be
trying to update SubKAval. It should be updating tblItemData.

I suggest you start over.
Start by adding tblItemData in your query builder
Then add SubKAval.

Join them ONLY on the PN.

--
Dave Hargis, Microsoft Access MVP


Aaron said:
Pasted your sql in and it errored saying that I cannot update a linked
table(SubKAvl)


Klatuu said:
Still updating the wrong table. You had joins that would filter out any
records you want to update. Try it this way:

UPDATE tblItemData INNER JOIN SubKAvl ON (SubKAvl.PN =
tblItemData.[Component PN]) SET tblItemData.OEM = SubKAvl!OEM
WHERE (((tblItemData.OEM) Is Null));
--
Dave Hargis, Microsoft Access MVP


Aaron said:
UPDATE SubKAvl INNER JOIN tblItemData ON (SubKAvl.PN =
tblItemData.[Component PN]) AND (SubKAvl.Description =
tblItemData.Description) AND (SubKAvl.OEM = tblItemData.OEM) AND
(SubKAvl.OEM_PN = tblItemData.[OEM PN]) SET tblItemData.OEM = SubKAvl!OEM
WHERE (((tblItemData.OEM) Is Null));

:

Post the SQL of your query, please.
--
Dave Hargis, Microsoft Access MVP


:

Yes, this is exactly what I want to do. I put this and it still says
updating Zero rows?


:

If you want to update the Description field in tblItemData with the
Description field in Sub where the Description field in tblItemData is Null
then use this:

Field: Description
Table: tblItemData
Update to: [Sub]![Description]
Criteria: Is Null

--
Dave Hargis, Microsoft Access MVP


:

I am wanting to update tblItemData from table SUB

:

What you posted show you updating the Sub table, not tblItemData. Which is
it you want to do?
--
Dave Hargis, Microsoft Access MVP


:

Hi klatuu,

I have put this is the qry and ran it but it says it changes zero rows.
I have the two fields in both tables linked within the qry. Is there
something else I need to do? I can see that the tbl SUB has data that needs
to be updated in tblItemData.



:

Use the Nz function. It returns the value in the first argument if it is not
null. If it is, it returns the value in the second argument.

Field: Description
Table: SUB
Update to: Nz([tblItemData]![Description], [Sub]![Description])
Click to expand...
Click to expand...
Click to expand...
 
Top