Update query validation errors

  • Thread starter Thread starter jp
  • Start date Start date
J

jp

The following query occasionally gives me validation rule
violations and I suspect it may be because sometimes
the "update from" fields are empty. The CMaterial and
CRatio and CContract fields are not required and are the
same type as the "from" fields. I am verifying that
SampleId exists in tblMfg before running the query. Do I
need to handle nulls in my update and if so can someone
please show me how or is something else causing me
problems?
UPDATE tblMfg INNER JOIN tblCombineComp ON tblMfg.SampleID
= tblCombineComp.SampleID SET tblMfg.CMaterial =
[tblCombineComp].[Material], tblMfg.CContracts =
[tblCombineComp].[Contracts], tblMfg.CRatio =
[tblCombineComp].[Ratio];
 
Dear jp:

My rule for doing this is to write two queries. One reports the rows
that cannot be updated for whatever reasons by filtering to find them.
The other updates only those records which can be updated without
generating exceptions.

The criteria used to generate this will depend on the situation,
including the constraints on the table being updated.

Generally, this should be attempted only during a time when database
activity has ceased and the process can have exclusive access to the
database. If you allow there to be changes in the data while the
process is proceeding, it is possible you could have a change that
invalidates an update between the time it is reported and the time it
is updated.

Your specific question is about nulls. If you have constrained your
table to prevent nulls in certain columns, then your update query can
eliminate updating the rows that would violate this rule. Such a
problem is not subject to changes in the data and would avoid the
difficulty requiring the database to be shut down for maintenance.
There are often questions of database integrity that do make exclusive
access to the database necessary, such as your verification that
SampleId exists in the foreign table.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
How do you know what to filter for? In my case I'm
somewhat guessing why I get the error. As for the nulls,
what I mean is that if I am updating 3 fields but one or 2
are empty, the update fails. I want to update the one that
isn't empty if that's all that's there is to update.
-----Original Message-----
Dear jp:

My rule for doing this is to write two queries. One reports the rows
that cannot be updated for whatever reasons by filtering to find them.
The other updates only those records which can be updated without
generating exceptions.

The criteria used to generate this will depend on the situation,
including the constraints on the table being updated.

Generally, this should be attempted only during a time when database
activity has ceased and the process can have exclusive access to the
database. If you allow there to be changes in the data while the
process is proceeding, it is possible you could have a change that
invalidates an update between the time it is reported and the time it
is updated.

Your specific question is about nulls. If you have constrained your
table to prevent nulls in certain columns, then your update query can
eliminate updating the rows that would violate this rule. Such a
problem is not subject to changes in the data and would avoid the
difficulty requiring the database to be shut down for maintenance.
There are often questions of database integrity that do make exclusive
access to the database necessary, such as your verification that
SampleId exists in the foreign table.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


The following query occasionally gives me validation rule
violations and I suspect it may be because sometimes
the "update from" fields are empty. The CMaterial and
CRatio and CContract fields are not required and are the
same type as the "from" fields. I am verifying that
SampleId exists in tblMfg before running the query. Do I
need to handle nulls in my update and if so can someone
please show me how or is something else causing me
problems?
UPDATE tblMfg INNER JOIN tblCombineComp ON tblMfg.SampleID
= tblCombineComp.SampleID SET tblMfg.CMaterial =
[tblCombineComp].[Material], tblMfg.CContracts =
[tblCombineComp].[Contracts], tblMfg.CRatio =
[tblCombineComp].[Ratio];
Click to expand...

.
Click to expand...
 
Dear jp:

If you want to update the 3 columns independently and only as each is
not null, use 3 separate update queries. An alternative would be to
use logic to tell the database engine to update all 3 at once, and to
update each to the new value when it is not null, and update to the
old value when the new value is null.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


How do you know what to filter for? In my case I'm
somewhat guessing why I get the error. As for the nulls,
what I mean is that if I am updating 3 fields but one or 2
are empty, the update fails. I want to update the one that
isn't empty if that's all that's there is to update.
-----Original Message-----
Dear jp:

My rule for doing this is to write two queries. One reports the rows
that cannot be updated for whatever reasons by filtering to find them.
The other updates only those records which can be updated without
generating exceptions.

The criteria used to generate this will depend on the situation,
including the constraints on the table being updated.

Generally, this should be attempted only during a time when database
activity has ceased and the process can have exclusive access to the
database. If you allow there to be changes in the data while the
process is proceeding, it is possible you could have a change that
invalidates an update between the time it is reported and the time it
is updated.

Your specific question is about nulls. If you have constrained your
table to prevent nulls in certain columns, then your update query can
eliminate updating the rows that would violate this rule. Such a
problem is not subject to changes in the data and would avoid the
difficulty requiring the database to be shut down for maintenance.
There are often questions of database integrity that do make exclusive
access to the database necessary, such as your verification that
SampleId exists in the foreign table.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


The following query occasionally gives me validation rule
violations and I suspect it may be because sometimes
the "update from" fields are empty. The CMaterial and
CRatio and CContract fields are not required and are the
same type as the "from" fields. I am verifying that
SampleId exists in tblMfg before running the query. Do I
need to handle nulls in my update and if so can someone
please show me how or is something else causing me
problems?
UPDATE tblMfg INNER JOIN tblCombineComp ON tblMfg.SampleID
= tblCombineComp.SampleID SET tblMfg.CMaterial =
[tblCombineComp].[Material], tblMfg.CContracts =
[tblCombineComp].[Contracts], tblMfg.CRatio =
[tblCombineComp].[Ratio];
Click to expand...

.
Click to expand...
Click to expand...
 
Back
Top