Problem using the len() function

[Paul]

I have a large database where the leading zero(0) of the
zip code was stripped in translation. When I wrote an
update query with the criteria of (len(zipcode) < 5) it is
translated into (len(zipcode) <"5") and the query passes
zip code numbers whose length is 4 and 10. The function
seems to be returning a string rather than a number.

I tried (val(len(zipcode))< 5) and got a type mismatch
error.

Any help would be appreciated.

 

[Allen Browne]

1. Open your table in design view, and verify that the zipcode field is now
Text (not Number).

2. In the Field row of your query, enter:
Len([zipcode])

3. In the Criteria row beneath this calculated field, enter:
< 5

4. Check you have the desired records.

5. Change the query to an Update query. (Update on Query menu).

6. In the Update row under your zipcode field, enter:
Format([zipcode], "00000")