Allow null boolean values when importing data

[dsub42]

Hi

I am using Access to import a table from a paradox database

There are several colums which are boolean values, these contain alot of null values in the source table and it is important that I keep them in the acces table,

However, when import the table, access replaces all of the null boolean values with 0, how can I stop this from happening and keep the origional null boolean values?

Any help would be appriciated

 

[HiTechCoach]

Hi

I am using Access to import a table from a paradox database

There are several colums which are boolean values, these contain alot of null values in the source table and it is important that I keep them in the acces table,

However, when import the table, access replaces all of the null boolean values with 0, how can I stop this from happening and keep the origional null boolean values?

Any help would be appriciated

Have you tried changing the data type to Text?

 

[dsub42]

yes, but it doesnt remove what should be the null values

 

[HiTechCoach]

yes, but it doesnt remove what should be the null values

I should have been more specific. I was referring to the field's data type in the table.

Have you tried that?

 

[The_Doc_Man]

While I'm sure that there is some strange reason to do this, the idea of consciously storing a null makes me pause. Storing a null for a checkbox implies tri-state logic. I should point out that I'm a null "purist" and tend to never do this unless there is a gun to my head or genitals. Null has one and only one meaning to me, so if the import is not going to use null as equivalent to "damned if I know" then you are doing something highly questionable. Having mentioned this as an unwise thing to do, I have fulfilled my moral obligations. So here's a few thoughts on why you might be having problems...

If importing to an existing table, the field in question might have a "required=True" setting in which case nulls cannot be imported.

There is also the idea that if you are doing a MakeTable with the import (I.e. creating a table on-the-fly) you don't get to select field properties. Instead, the import wizard determines its best guess by inspection. The import wizard isn't that bright, though, and limits what you can do. In that case, if you pre-define the table to allow nulls in that place, or select tri-state logic for the Yes/No fields, then import to the existing but empty table (that you just defined), you might have better luck. The import wizard, while as dumb as a box of rocks when on his own, will honor the settings on pre-defined tables.

 

[dsub42]

The reason for nulls is as follows.... a field in the data base indicates a person having training for something... the data in this field will be either true or false if it is known, for some people it is not known and it is important to the business that they have that distinction. True / Flase / Not - known/ ...

 

[Banana]

I do not believe Jet's Yes/No will allow a null value. You would have to import them as a Byte or Integer instead.

 

[HiTechCoach]

Banana is correct that the Yes/No data type is only two state.

I normally do not use the Yes/No data type.

 

[dsub42]

o.k, how can i specify for that column not to import the data as yes/no data type..

Im importing from a paradox table, it is 70+ columns of which about 5 are true/false in paradox, access interprets them as yes/no..

to import i literally go to external tools > other > paradox > select the table, click ok and then the table is in access... at no point does it give me any import options?,....

is there another way? I am really unfamiliar with acces...

 

[SOS]

You might need to export the data from the paradox source into a comma delimited text file and then you can use an import specification to tell Access which fields to import to and which datatype to use.

 

[dsub42]

ok another problem im having is in a field that is importing time(field type) from paradox, ... in access its adding a date to the time of .. 30/12/1899... any idea?

 

[SOS]

Times are stored as date/time (actually as double) in Access. So, you can't really store just a time unless you use a text field. But you can just format it anywhere you use it to be showing the time. That's why most who use Access store date AND time together (not only for that but it makes calculations way easier).

 

[dsub42]

do you know what function i could use for a colum called "starttime" to change...

30/12/1899 07:34:00 .... to .... 07:34:00

considering the data in every field is formatted the same way, and some cells contain a null value?

 

[SOS]

Changing the data isn't really an option there. Setting the format in the table and in queries to Medium Time wouild be what you would need.

 

[dsub42]

O.K this is driving me loopy now.... when importing a column from paradox which is TRUE / FALSE....(and includes null values)

Access imports TRUE as -1 , No as 0, and the NULL values as -1 also....

What the hell is that about, I would understand if the nulls were imported in as False, but not true!!

Anyone have any ideas?

 

[Banana]

That can't be right.

When I posted earlier that Yes/No does not allow for nulls, I tested with this:

INSERT INTO MyTable (MyField) VALUES (Null);

and the result was a false value was inserted.

If you are absolutely sure that nulls is being converted to true, then I would strongly suggest you take up on SOS's suggestion - import it out of Paradox into a text file or whatever then import this into Access.

I wonder whether linking the table then importing it within the Access would work. I strongly doubt that though because it'd use the same driver as in the import and you probably would see the same thing but who knows.