dave2007 wrote:Villeroy wrote:Set the default value of the column in the target column to 0
OK, I just tried that and it doesn't work.

Here is what I did: I set the default value of the column in the target column to '
No' (OOo Base lets me set it to either 'Yes' or 'No' - no '0' or '1'). Using Find/Replace I changed all values in the spreadsheet column from 'Y' to 'Yes' (empty cells remained empty i.e. only whitespace). I then imported it.
The result was again that all empty cells translated to <none> (grey check mark) and all cells containing 'Yes' translated to 'No' (empty check mark).
What am I (or Base) doing wrong?
Thanks,
Dave
After setting the field's default value in the GUI you have to save the table definition (click some other control and save before closing the table's design view). Test manually if a new record actually sets the default value. If not, save the database and reload.
Now there should be no need to change anything on the spreadsheet side as far as numbers are mapped to your boolean field. Since you decided to represent booleans as "Y"/"N" or "Yes"/"No" respectively, the import must fail. Import fails in most cases where text values are mapped to non-text database fields (however, ISO-date strings maps fine to date fields).
Numbers work regardless of their number format. Zero (formatted as FALSE or not) becomes FALSE ("No"), non-zero (formatted as TRUE or not) becomes TRUE ("Yes").
Add another column with a header in first cell and =IF(A2="Yes";1;0) and import that. [A2 is supposed to be the first "Yes"/"No"]
I wish this database GUI would not introduce those misleading text representations "Yes", "No", "Empty", german "Ja", "Nein", "Leer"