fields in imported tables default to null

  • Thread starter Thread starter Toxalot
  • Start date Start date
T

Toxalot

I'm importing tables from dBASE IV and all the empty fields are coming
through as null.

Is there a simple way to update all fields to change all nulls to an
empty string?

Jennifer
 
There is, but I'm not sure why you would want to. A NULL value is a valid
value for a field, means that the value is unknown. An empty string implies
that the value does not exist. This methodology requires additional coding
to handle NULL values, but is very useful.

You will have to do this for each field, and if the field is numeric, you
will have to change the update query to set the value to a number (another
reason to leave the nulls in NULL <> 0).

UPdate yourTable
Set [yourField] = ""
WHERE [yourField] IS NULL

HTH
Dale
 
I'm importing tables from dBASE IV and all the empty fields are coming
through as null.

Is there a simple way to update all fields to change all nulls to an
empty string?
Click to expand...

Access, by default, trims trailing blanks and does NOT store zero-length
strings in Text fields: they need to either contain data or they're NULL.

You can set the Allow Zero Length String property of a text field in table
design view. If you do so, it would probably be wise to also make the field
required, since it's impossible to visually distinguish NULL from a ZLS (zero
length string) value.

Why do you feel that you need a ZLS? Nulls can cause problems for the unwary,
but they're *managable* problems....

John W. Vinson [MVP]
 
They aren't nulls in the original data. All nulls and zls are coming
through as nulls on the import. I do use null for data that is
unknown, but some really are known to be nothing so I use zls.

I only have certain fields where I use null and I had to argue with
other people on the project to get that. Some people feel that nulls
should never be used. One programmer even wanted to store some special
(not a null) made up value to designate unknown. :-)

The table design defaults to not allowing zls and I figure that is why
all the zls are defaulting to null on the import.

Can I alter the table in SQL as well so that I don't have to go and
manually change the table design? I'm going to have to do this import
several times over the development of the project and would like to
automate the conversion as much as possible.

Jennifer
 
They aren't nulls in the original data. All nulls and zls are coming
through as nulls on the import. I do use null for data that is
unknown, but some really are known to be nothing so I use zls.
Click to expand...

Ah. That's the only case I'd ever use zls - sounds like you're ahead of me on
the issue!
I only have certain fields where I use null and I had to argue with
other people on the project to get that. Some people feel that nulls
should never be used. One programmer even wanted to store some special
(not a null) made up value to designate unknown. :-)

The table design defaults to not allowing zls and I figure that is why
all the zls are defaulting to null on the import.

Can I alter the table in SQL as well so that I don't have to go and
manually change the table design? I'm going to have to do this import
several times over the development of the project and would like to
automate the conversion as much as possible.
Click to expand...

You can alter it in VBA - I'm not sure if Access DDL queries will let you do
so. And even for the VBA I'd have to dig through the help on field properties
to see how. Let's see...

ok, in VBA you can use the AllowZeroLength and Required properties (set to
True and False respectively in your case) of the Field object in the Tabledef.
Apparently this property isn't exposed in the Create Table or Alter Table SQL
DDL.

John W. Vinson [MVP]
 
if you used Access Data Projects then you wouldn't have to deal with
zero-length strings
 
Back
Top