×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Imported null values read as zero-length strings

Imported null values read as zero-length strings

Imported null values read as zero-length strings

(OP)
Hi.  I'm trying to append records from an imported table to an existing table in Access 97, but I'm getting the "validation rule violation" error message.  If I change the "allow zero-length strings" property to "yes" for the fields to which I'm appending, I don't get the error message and the append is successful.

As the properties on the imported table are set to the same default properties as the "appendee" table, Access must be reading imported null values as zero-length strings.  This could be a rather large pain in the tush when doing regular imports/appends...

Anyone know why Access behaves this way?  Is there a solution?

Thanks!!!

RE: Imported null values read as zero-length strings

Hi Derek,

you obviously have a validation rule that says these fields are required, therefore the source table has empty fields in some or all of its fields that you are adding.

is the required setting needed at the table level or could you put this into the form used for user entry?

if not you could add an iif statement to the query to add some flag if the source field is null like

iif (isnull(thisfield),"Help",thisfield)

and have a routeen after the import or before the import depends on what fits your requirements, to force the user to enter the correct data, if this isn't an issue change "help" for " " a single space.

HTH
Robert

RE: Imported null values read as zero-length strings

(OP)
Thanks, Robert.  I had checked all the validation rules, and no fields are required.  The null values were definitely being converted to zero length strings on import.  I worked around it by doing an update query for all the zero-length strings, converting them to nulls.  Luckily, this seems to have been a bug associated with only that one database, which was e-mailed to me as an attachment.  Hopefully it won't be a regular thing...

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close