Dear all,

I would be grateful for some suggestions on the best way to filter blanks and zero length strings from a table of data. I have experimented with a couple of conditions in the Criteria field under Design View, but have ultimately been unsuccessful.

Using <>"" works for blanks, but leaves zero length strings behind.

Ideally, I would like to replace blanks and ZLS with NULL.

Is there a function that can be applied to automatically recognise numbers or text?

Thank you for your help.

accessDB1

There are a couple of ways:

Len([field] & "") <> 0

Nz([field], "") <> ""

Not Is Null And <> ""

But maybe you should set the Allow Zero Length property of the field in the table to No so all you have to deal with is Null.

Welcome to the forum.

Welcome to the forum.

Perhaps this thread (http://www.access-programmers.co.uk/forums/showthread.php?t=202091) will give you some pointers.

Thank you very much for the replies and suggestions.

I first tried to change the Allow Zero Length property to "No" before re-running a processing query, but this still led to a couple of cells without data being processed.

What I am doing is to insert characters into populated cells within selected columns in a table. I want to completely ignore cells that do not contain data.

When I set Allow Zero Length to "No" and run the query with Is Not Null in the criteria field (i.e. process only if not NULL) the result is that some cells that in fact do not contain data are still processed and have characters inserted. Also I noticed that some cells containing data were left unprocessed. I'm really puzzled about what is going on.

I next went directly to using Nz([field], "") <> "" in the Criteria fields and this resulted in none of the cells (data or not) being processed.

The cells without data seem to be a combination of i) blank (or NULL); ii) ZLS; and iii) some other non-data form.

I should add that the third option I gave you will be more effecient in a field's criteria. The others are more effective in row-wise checking.

The cells without data seem to be a combination of i) blank (or NULL); ii) ZLS; and iii) some other non-data form.In that case your data is full of typos. If you had a good user interface you would have avoided those typos. I suspect that there are spaces in those cells. Perform a Len() count on that field and see the results on the fields that are supposed to be "empty".

Hi vbaInet,

Thanks for your reply. I agree, the data seems to contain some peculiar entries and inconsistent formatting. Would you know of a generic check for validity - i.e. a check to see if the field contains valid data - that I could apply to capture errors in both formatting of populated cells and the occurrence of ZLS and other forms of blanks? I assume that ZLS does not include, for e.g., cells populated with spaces.

Many thanks,

accessDB1

Hi there,

Zls is just this "". You don't need to perform any validation for spaces. If you don't want spaces simply Trim() before you save. There's also the LTrim() function for removing spaces before text and there's RTrim() for trailing spaces.

Hi, Thanks for your reply.

Can I apply the trim() function in the Update field? Ultimately, I would like to implement all the data cleaning steps in design view as far as possible. But if this is not possible or practical, then I am open to suggestions. Trim() works well with data imports to XL, but I am rather new to Access so appreciate the guidance.

Thank you again,

accessDB1

Absolutely! Just wrap the update field (in the append query) in Trim(). It would be good to get your data cleansed before applying tougher validation on your form so everything is consistent.