CODEWITHSUNDEEP
ms-accessms-access-2010ms-access-2007ms-access-2013
SJaka
SJaka

Reputation: 830

How to convert data type from Text to Yes/No inside an append query in MS Access?

In MS Access, I'm trying to append data from one table (Table A) into another table (Table B). Table A has a column of type 'Short Text' with a single value 'Even' or empty/null. Table B has an equivalent column of type Yes/No. I've to insert data from Table A to Table B, while doing this conversion - if Table A's column has 'Even' set Table B's value to True, otherwise, set it to False.

I've tried a query like the following but it didn't work:

INSERT INTO TableB( BookName, ChapterName, PageNo, Even)
SELECT name, chapter, page,  IIf(UCase([Even or Odd]) = 'EVEN', True,False)
FROM TableA;

The above query gives me following error:

Access Error

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 117 record(s) to the table due to key violations....

How can I make access change the value while appending the data?

Upvotes: 2

Views: 2630

Answers (2)

SJaka
SJaka

Reputation: 830

For the sake of any future searches, Here is my final query which did work:

INSERT INTO TableB( BookName, ChapterName, PageNo, Even)
SELECT name, chapter, page,  IIf(UCase([Even or Odd])="EVEN",-1,0) AS EvenOdd FROM TableA;

Things to check, if there are data type differences between source and destination tables. In my case, the page column in source table was number and in destination was Short Text.

Also, the Primary key, which isn't part of the query, was Number instead of AutoNumber, that was failing, and hence I was getting the error.

Upvotes: 1

Tedo G.
Tedo G.

Reputation: 1565

As your error screen says, the problem is not in converting data types, the error is due to key violation. Try inserting without this yes/no field, and you'll ensure in this. You may have relationships between tables so that it doesn't allow to insert certainn values.

Upvotes: 2

Related Questions