If Null Update

  • Thread starter Thread starter Aaron
  • Start date Start date
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
 
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])
 
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.
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
Back
Top