Error using append from linked text file

[NeoFax]

Every linked text file I have in Access 2007 causes an error saying
that the information does not meet the criteria or some such(forgot to
write down the exact error). However, when I change the criteria in
the query the append then works fine. I would like to know how to fix
this so I can deploy the database and allow non-computer savvy
individuals maintain it.

 

[John W. Vinson]

Every linked text file I have in Access 2007 causes an error saying
that the information does not meet the criteria or some such(forgot to
write down the exact error). However, when I change the criteria in
the query the append then works fine. I would like to know how to fix
this so I can deploy the database and allow non-computer savvy
individuals maintain it.

Correct the error in the criteria.

Nobody will be able to help you do so if you do not post the actual expression
you're using and the error message it's generating. You can see your computer
- we cannot!

 

[NeoFax]

Here is the SQL:

INSERT INTO tblZ_ANAG_DIV ( Material, MaterialDesc, MType, MRPGrp,
ABC, Unit, PGrp, MRPType, MRP, LS, ProcType, SPT, Bulk, Batch, SMKey,
AV, MX, IC, Stock, Plt, ProductSup, ProfitCtr, Grp, CtrKey, PDT, GRT,
IPT, Net, FixedLot, MaxLot, MinLot, SafetyStock, ReorderPnt, ESloc,
SLoc, Critical, UnitIssue, LastChg, CreateDate, ProductLine, Profile,
N, StdPrice, PlndPrice, MvAvgPrice )
SELECT [tblImport-Z_ANAG_DIV].Material, [tblImport-
Z_ANAG_DIV].MaterialDesc, [tblImport-Z_ANAG_DIV].MType, [tblImport-
Z_ANAG_DIV].MRPGrp, [tblImport-Z_ANAG_DIV].ABC, [tblImport-
Z_ANAG_DIV].Unit, [tblImport-Z_ANAG_DIV].PGrp, [tblImport-
Z_ANAG_DIV].MRPType, [tblImport-Z_ANAG_DIV].MRP, [tblImport-
Z_ANAG_DIV].LS, [tblImport-Z_ANAG_DIV].ProcType, [tblImport-
Z_ANAG_DIV].SPT, [tblImport-Z_ANAG_DIV].Bulk, [tblImport-
Z_ANAG_DIV].Batch, [tblImport-Z_ANAG_DIV].SMKey, [tblImport-
Z_ANAG_DIV].AV, [tblImport-Z_ANAG_DIV].MX, [tblImport-Z_ANAG_DIV].IC,
[tblImport-Z_ANAG_DIV].Stock, [tblImport-Z_ANAG_DIV].Plt, [tblImport-
Z_ANAG_DIV].ProductSup, [tblImport-Z_ANAG_DIV].ProfitCtr, [tblImport-
Z_ANAG_DIV].Grp, [tblImport-Z_ANAG_DIV].CtrKey, NZ([PDT],0) AS Expr1,
NZ([GRT],0) AS Expr2, NZ([IPT],0) AS Expr3, NZ([Net],0) AS Expr4,
NZ([FixedLot],0) AS Expr5, NZ([MaxLot],0) AS Expr6, NZ([MinLot],0) AS
Expr7, NZ([SafetyStock],0) AS Expr8, NZ([ReorderPnt],0) AS Expr9,
[tblImport-Z_ANAG_DIV].ESloc, [tblImport-Z_ANAG_DIV].SLoc, [tblImport-
Z_ANAG_DIV].Critical, [tblImport-Z_ANAG_DIV].UnitIssue,
CDate([LastChg]) AS Expr10, CDate([CreateDate]) AS Expr11, [tblImport-
Z_ANAG_DIV].ProductLine, [tblImport-Z_ANAG_DIV].Profile, nz([N],0) AS
Expr12, NZ([StdPrice],0) AS Expr13, NZ([PlndPrice],0) AS Expr14,
NZ([MvAvgPrice],0) AS Expr15
FROM [tblImport-Z_ANAG_DIV]
WHERE ((Not (NZ([PDT],0)) Is Null));

And the error:

"You cannot record your changes because a value you entered violates
the settings defined for this table or list(for example, a value is
less than the minimum or greater than the maximum). Correct the error
and try again."

However, if I move the Where from ((Not (NZ([PDT],0)) Is Null)) to
((Not (NZ([GRT],0)) Is Null)) and then back the append query works
fine. So this tells me that the error message is generic and
unhelpful. I can see by going in to design view and then worksheet
view that the Net field has #Num! errors, that should not be as the
field does contain a number.

 

[John W. Vinson]

Here is the SQL:

INSERT INTO tblZ_ANAG_DIV ( Material, MaterialDesc, MType, MRPGrp,
ABC, Unit, PGrp, MRPType, MRP, LS, ProcType, SPT, Bulk, Batch, SMKey,
AV, MX, IC, Stock, Plt, ProductSup, ProfitCtr, Grp, CtrKey, PDT, GRT,
IPT, Net, FixedLot, MaxLot, MinLot, SafetyStock, ReorderPnt, ESloc,
SLoc, Critical, UnitIssue, LastChg, CreateDate, ProductLine, Profile,
N, StdPrice, PlndPrice, MvAvgPrice )
SELECT [tblImport-Z_ANAG_DIV].Material, [tblImport-
Z_ANAG_DIV].MaterialDesc, [tblImport-Z_ANAG_DIV].MType, [tblImport-
Z_ANAG_DIV].MRPGrp, [tblImport-Z_ANAG_DIV].ABC, [tblImport-
Z_ANAG_DIV].Unit, [tblImport-Z_ANAG_DIV].PGrp, [tblImport-
Z_ANAG_DIV].MRPType, [tblImport-Z_ANAG_DIV].MRP, [tblImport-
Z_ANAG_DIV].LS, [tblImport-Z_ANAG_DIV].ProcType, [tblImport-
Z_ANAG_DIV].SPT, [tblImport-Z_ANAG_DIV].Bulk, [tblImport-
Z_ANAG_DIV].Batch, [tblImport-Z_ANAG_DIV].SMKey, [tblImport-
Z_ANAG_DIV].AV, [tblImport-Z_ANAG_DIV].MX, [tblImport-Z_ANAG_DIV].IC,
[tblImport-Z_ANAG_DIV].Stock, [tblImport-Z_ANAG_DIV].Plt, [tblImport-
Z_ANAG_DIV].ProductSup, [tblImport-Z_ANAG_DIV].ProfitCtr, [tblImport-
Z_ANAG_DIV].Grp, [tblImport-Z_ANAG_DIV].CtrKey, NZ([PDT],0) AS Expr1,
NZ([GRT],0) AS Expr2, NZ([IPT],0) AS Expr3, NZ([Net],0) AS Expr4,
NZ([FixedLot],0) AS Expr5, NZ([MaxLot],0) AS Expr6, NZ([MinLot],0) AS
Expr7, NZ([SafetyStock],0) AS Expr8, NZ([ReorderPnt],0) AS Expr9,
[tblImport-Z_ANAG_DIV].ESloc, [tblImport-Z_ANAG_DIV].SLoc, [tblImport-
Z_ANAG_DIV].Critical, [tblImport-Z_ANAG_DIV].UnitIssue,
CDate([LastChg]) AS Expr10, CDate([CreateDate]) AS Expr11, [tblImport-
Z_ANAG_DIV].ProductLine, [tblImport-Z_ANAG_DIV].Profile, nz([N],0) AS
Expr12, NZ([StdPrice],0) AS Expr13, NZ([PlndPrice],0) AS Expr14,
NZ([MvAvgPrice],0) AS Expr15
FROM [tblImport-Z_ANAG_DIV]
WHERE ((Not (NZ([PDT],0)) Is Null));

This criterion will never under any circumstances be TRUE: Even if PDT is
null, the NZ function will convert it to a non-null 0, and the criterion will
be false. What's the intent? Which values do you want to see, or not see?

The error

"You cannot record your changes because a value you entered violates
the settings defined for this table or list(for example, a value is
less than the minimum or greater than the maximum). Correct the error
and try again."

suggests that you have a Validation Rule in the target table on one of the
fields - it may just be Required (non NULL) or there may be a validation rule
such as >0.

Remove the criterion (since it's not doing anything!!!) and temporarily change
the query from an Append query to a Select query. Open the datasheet. Do you
have NULL or invalid values in any of the fields? Then correct the criterion
and try again.

 

[John W. Vinson]

WHERE ((Not (NZ([PDT],0)) Is Null));

This criterion will never under any circumstances be TRUE:

oops... got my negations backward. This criterion will ALWAYS be true - the
expression

NZ([PDT],0)

can never be NULL; so

(NZ([PDT],0)) Is Null

will always be FALSE; therefore

NOT (NZ([PDT],0)) Is Null)

will always be TRUE.

Thanks to my friend Ken Snell for catching my mistake.