Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Imported null values read as zero-length strings

Status
Not open for further replies.

DerekMcDonald

Technical User
Oct 5, 2000
26
US
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!!! [sig][/sig]
 
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 [sig][/sig]
 
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... [sig][/sig]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top