Problem using the len() function

  • Thread starter Thread starter Paul
  • Start date Start date
P

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.
 
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")
 
Back
Top