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!

Register Log in
  • Congratulations wOOdy-Soft 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

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]
 
Sort by date Sort by votes
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]
 
Upvote 0 Downvote
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]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

TrekBiker
  • Locked
  • Question
Using old IDs as Autonumbers 1
Replies
6
Views
749
Andrzejek
Andrzejek
monkeysee
  • Locked
  • Question
Patient Table to include Parent(s) as responsible party and a patient. 2
Replies
3
Views
321
MajP
MajP
cp950
  • Locked
  • Question
HOW TO : Show NULLs in Table View
Replies
2
Views
515
dhookom
dhookom
bcooler
  • Locked
  • Question
Convert imported column into row 2
Replies
16
Views
483
bcooler
bcooler
lisaharris
  • Locked
  • Question
Table List box used as query / report selection
Replies
3
Views
293
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top