Zero Length String Problem

[HenryAnthony]

Hi,

I have a table with a text field set to "Allow zero length string: Yes". I must have done this by accident because I don't know any use for this. So, I have a query with criteria for this field that is: "Is Not Null". Yet these records continue to appear in the query. I can also type double-quote markes in criteria and the same records display. I have changed the field to Allow zero length string: No" and this doesn't help. Any suggestions will be appreciated.

Best regards,

Henry

 

[PHV]

Have you tried this kind of criteria ?
WHERE ...
And Trim([Name of your field] & "")<>""
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[HenryAnthony]

PHV,

Hmmm... I searched help for "Trim" and came up with a worksheet function that removes spaces other that those between words. I thought worksheet functions were only for Excel. Not sure how this would work or how exactly to implement it but thanks anyway.

I did solve the problem though. Since I had only about 20 records with the zero length string, I just replaced the data with a text character and then deleted the character.

Best regards,

Henry