Empty fields not being seen as Null!

[roland_access]

Strange one this. Apparantly random records of several Text or Memo fields in my database (of about 60,000 records) are being seen by Access as having data in them when there isnt any at all.

It came to light when trying to append this and other data to a table. Although the text fields in the destination table have no validation rules, several records were not being appended due to validation rules.

I ran a query on the data with Is Not Null as the criteria for one of the text fields, then sorted ascending. Several empty cells were at the top, and on closer inspection with the Zoom function nothing was contained in the fields. No spaces, no new lines etc. Nothing.

Any ideas please?

 

[Rich]

Did you try Trim([MyField])?

 

[roland_access]

I used a combination of manual and update queries to change the odd and problematic data to true Null.

Exactly what would the Trim function do (permanent?) and would it explain why it happened?

 

[roland_access]

But how did these zero length strings get there in the first place? The data in the source table was inputted from another database.

 

[FoFa]

Sometimes when importing data from another DB, if it has spaces or non-null values they will be stored like that. You can do the same thing in MSAccess also depending on your settings. I have not tried it in XP yet to see if it still works that way, but in older version you could edit a table, clear a field (high light and delete) and it would store an empty string instead of a null.