Not Nulls in an Access Database field

[wnfisba]

I have a field in an Access Database that is actually ending up in a SQL
Server table. The column on the SQL Server Tables is defined without NULL so
I want this Access field to come over without NULL. Right now it is coming
over as NULL and I don't want that. I believe this has to do with "Allow Zero
Length", but I can't seem to figure out how to set this in Access. If the
user does not enter anything into the field in Access, I want it coming over
as blanks rather than NULL. How do I do that?

wnfisba

 

[Dirk Goldgar]

I have a field in an Access Database that is actually ending up in a
SQL Server table. The column on the SQL Server Tables is defined
without NULL so I want this Access field to come over without NULL.
Right now it is coming over as NULL and I don't want that. I believe
this has to do with "Allow Zero Length", but I can't seem to figure
out how to set this in Access. If the user does not enter anything
into the field in Access, I want it coming over as blanks rather than
NULL. How do I do that?

When you say the field is "actually ending up in a SQL Server table", do
you mean this is a linked table, to a table in the SQL Server database?
Or do you mean that it's a local Access (Jet) table, that is eventually
copied, transferred or otherwise used to update a separate, SQL Server
table? It makes a difference. If it's a Jet table that is later used
to update a SQL Server table, how is that update carried out?

 

[wnfisba]

The Access column is defaulting to NULL and when we try to update the SQL
Server table column which does not allow for NULL, the stored procedure blows
up.

Is there a way that I can default the Access table column to spaces rather
than NULL???

 

[Douglas J. Steele]

With Text fields, you have the option of declaring whether or not to accept
a zero-length string. You also have the option of assigning a default value
for a field.

You could set the field's default value to " ", or, if you allow it to
accept zero-length strings, you could set it to "".