MS ACCESS 97 Query change related question

[pnad]

Hello all,

We currently use a MS Access 97 DB.

I am trying to change a query to include the memo field. There is a text field called Memo that has a length of 4.This is the error that I get:

Microsoft Access can't append all the records in the append query.

Microsoft Access set 0 field(s) to Null due to a type conversion failure, and it didn't add <number> record(s) to the table due to key violations, 0 record(s) due to lock violations, and 560 record(s) due to validation rule violations.

Do you want to run the action query anyway?
To ignore the error(s) and run the query, click Yes.
For an explanation of the causes of the violations, click Help.

The query is as follows:

INSERT INTO ALS_Accounting ( SYSTEM, Company, [Cycle Date], Policy, Amount, [D/C], Account, LOB, PROD, REICH, REICO, TAXST, OPC, BCTR, SAD, [Effective Date], Trx, State, Memo )
SELECT ALS_Accounting_Lookup.SYSTEM, Company.GENERAL_OUT, Admin_ALS.[Cycle Date], Admin_ALS.Policy, Right("000000000000" & [Admin_ALS]![Amount],12) AS Amount, Admin_ALS.[D/C], Left([Admin_ALS]![Account],8) AS Account, "SURPLUS" AS LOB, "" AS PROD, "" AS REICH, "" AS REICO, "" AS TAXST, "" AS OPC, "" AS BCTR, ALS_Accounting_Lookup.SAD, Admin_ALS.[Eff Date], Admin_ALS.Trx, Admin_ALS.State, Admin_ALS.Memo
FROM (ALS_Accounting_Lookup INNER JOIN Admin_ALS ON (ALS_Accounting_Lookup.PLAN_CODE = Admin_ALS.[Plan Cd]) AND (ALS_Accounting_Lookup.FUND_NO = Admin_ALS.Fund)) INNER JOIN Company ON Admin_ALS.Company = Company.IN_COMPANY
WHERE (((Left([Admin_ALS]![Account],3))="214"));

 

[LittleSmudge]

First off pnad create a new query and paste into it the SELECT .. .. part of the query above and then run that.

If it runs okay and gives you 560 records then you need to look at the format of the data in the memo column. Is it as you expected ?

( By the way - remember to shoot the person that design the db and decided to use a data type name for the name of a field - especially when the field does not contain data of the type that the name suggests ! )

Then go to the ALS_Accounting table and look at it in design view.

Look at the Validation Rule entry for the memo field.

Will the data from your SELECT .. .. query comply with the validation rule as defined in ALS_Accounting ?
I suspect not.

Let us know how you get on.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.