Importing an Excel spreadsheet with Yes/No fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a Table in a Database that has two rows "updated" and "Cancel" both
are Yes/No fields. I have my data in an Excel spreadsheet and the columns
"updated" and "Cancel" are either a Yes or a No (when I right click on those
columns in Excel I have formatted the columns to be Custom and set to
"Yes";"No"...when I try and Import to Access in an existing table, I get an
error "Cannot import file...". So, to maybe view an error file, I try and
import to a NEW table. It works fine with no errors...but when I view the new
table the columns "updated" and "Cancel" are formatted in TEXT and I see
"Yes" and "No" instead of the checkbox.

Any thoughts?

John
 
That's normal behaviour. You've got text values "Yes" and "No" in Excel,
so Access recognises it as a text field and complains when you try to
import it to a boolean field.

If you want to be able to import directly to the table, you need to make
the data in Excel match the Access field type (e.g. 0 for No and 1 for
Yes, or the logical values TRUE and FALSE) or else change the Access
field to text, with values "Yes" and "No".

Otherwise you'll need to import or link to a temporary table and use a
query with a calculated field that changes the "Yes" and "No" into -1
and 0 respectively.
 
Back
Top