Error Appending records

[phong919]

Hello,

i'm new with access and was wondering if i can get some help with an append query?

I'm getting an error message that state the following below:

*Microsoft Access can't append all the records in the append query.
Microsoft Access set 36 field(s) to Null due to a type conversion failure, and it didn't add 0 records to the table due to key violations, 0 records due to lock violations, and 0 records due to validation rule violations.

This is my append query below:

INSERT INTO CustExportHoldCiti ( Custodian, CustAcct, Ticker, Cusip, CurrCode, SecurityName, Shares, CustCost, CustValue, POI, OriginalDiscount, HoldingType, Loc, Expr1 )
SELECT DISTINCT [CitiBank Current Holdings].Custodian, [CitiBank Current Holdings].[Account Number], SecSymbolsAll.SecSymbol AS Ticker1, [CitiBank Current Holdings].CUSIP1, [CitiBank Current Holdings].Curr, [CitiBank Current Holdings].[Security Description], [CitiBank Current Holdings].Shares, [CitiBank Current Holdings].CustCost, [CitiBank Current Holdings].CustValue, [CitiBank Current Holdings].POI, [CitiBank Current Holdings].OriginalDiscount, [CitiBank Current Holdings].HoldingType, dbo_Locations.Location, [date] AS Expr1
FROM [CitiBank Current Holdings] LEFT JOIN (SecSymbolsAll LEFT JOIN dbo_Locations ON SecSymbolsAll.LocationID = dbo_Locations.LocationID) ON [CitiBank Current Holdings].Ticker = SecSymbolsAll.SecSymbol
WHERE (((SecSymbolsAll.SecSymbol) Is Not Null));

I can't figure out what is wrong with it. Thank you for the help.

 

[ejstefl]

Your source table has fields of a different type than your destination table. For 36 of the records, it couldn't convert the source field's data into the destination field's data type.

For instance, you are trying to trying to append a letter into a number field...

 

[phong919]

I setup all the fields datatype to text and i'm still getting that problem. Is there any other way i can resolve this issue?

 

[ejstefl]

Are you sure that in all cases the source field's type matches the destination field's type? Setting them ALL to text is not necessary (for example, you probably want to keep your date fields set as dates). It is only necessary that both the source and destination match.

If that still doesn't help, you can always look at what fields are coming over as null, sometimes looking at the data that did not make it over can provide a clue as to why.

 

[phong919]

I have a field that requires the user to enter in a date before the append query. i have that set as a txt data type. Could that be the issue i'm having? When i do run the whole append query, the date field comes in as nulls.

 

[ejstefl]

Most likely - you can try two things:

If you want the field to be a date, change the destination field's data type to date.

If you want the field to remain text, use the cstr (convert string) function to convert the date to text before you append. In the append query, you can use CStr([DateField]).

 

[phong919]

OMG the CSTR worked. Thank you so much.