Query help for zip codes

  • Thread starter Thread starter Barry
  • Start date Start date
B

Barry

My table has several thousand 9-digit zip codes (like 90210-2345). I want to
create a query whereby all 5-digit zips are grouped together at the top (or
bottom) so that I can research them and convert them to 9-digit. I think it's
a "Mid" kind of thing, but I'm not sure. In dBase, it was an index like this:
substring(zip,8)=" ". Any help would be appreciated.
 
Hello Barry,

Put the following expression in an empty field in the query:
Len([Zipcode])
This will be a list of 5s and 9s. Sort ascending and you will have all the
5s at the top.

Steve
(e-mail address removed)
 
Barry said:
My table has several thousand 9-digit zip codes (like 90210-2345). I want
to create a query whereby all 5-digit zips are grouped together at the top
(or bottom) so that I can research them and convert them to 9-digit. I
think it's a "Mid" kind of thing, but I'm not sure. In dBase, it was an
index like this: substring(zip,8)=" ". Any help would be appreciated.
Click to expand...

SELECT *
FROM TableName
ORDER BY Len(ZipCodeField)
 
To just get the records where the zip is exactly five number characters you
can use.

Field: Zip
Criteria: Like "#####"

Or to get any/all that are not five digits, a dash, and four digits

Criteria: NOT Like "#####[-]####"

To sort the records you could add a calculated field (length of zip) and then
sort by the length.
Field: Len([Zip])


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
My table has several thousand 9-digit zip codes (like 90210-2345). I want to
create a query whereby all 5-digit zips are grouped together at the top (or
bottom) so that I can research them and convert them to 9-digit. I think it's
a "Mid" kind of thing, but I'm not sure. In dBase, it was an index like this:
substring(zip,8)=" ". Any help would be appreciated.
Click to expand...

Is the hyphen stored with the zip code?
Either way, the below SQL will return all Zips that are 6 or less
characters in length sorted by Zip.

SELECT YourTable.Namfield, YourTable.ZIP, Len([Zip]) AS Exp
FROM YourTable
WHERE (((Len([Zip]))<=6))
ORDER BY YourTable.ZIP;

If you want all zips (regardless of 5 or 9 characters) with all the 5
character zips on top, then:

SELECT YourTable.[Last Name], Len([Zip]) AS Exp,YourTable.ZIP
FROM YourTable
ORDER BY Len([Zip]), YourTable.ZIP;
 
--
Barry


Steve said:
Hello Barry,

Put the following expression in an empty field in the query:
Len([Zipcode])
This will be a list of 5s and 9s. Sort ascending and you will have all the
5s at the top.

Steve
(e-mail address removed)


Barry said:
My table has several thousand 9-digit zip codes (like 90210-2345). I want
to
create a query whereby all 5-digit zips are grouped together at the top
(or
bottom) so that I can research them and convert them to 9-digit. I think
it's
a "Mid" kind of thing, but I'm not sure. In dBase, it was an index like
this:
substring(zip,8)=" ". Any help would be appreciated.
Click to expand...


.
Click to expand...
 
--
Barry


Steve said:
Hello Barry,

Put the following expression in an empty field in the query:
Len([Zipcode])
This will be a list of 5s and 9s. Sort ascending and you will have all the
5s at the top.

Steve
(e-mail address removed)


Barry said:
My table has several thousand 9-digit zip codes (like 90210-2345). I want
to
create a query whereby all 5-digit zips are grouped together at the top
(or
bottom) so that I can research them and convert them to 9-digit. I think
it's
a "Mid" kind of thing, but I'm not sure. In dBase, it was an index like
this:
substring(zip,8)=" ". Any help would be appreciated.
Click to expand...


.
Click to expand...
 
Sorry Barry,

Put this expression in an empty field in your query:

5And9Zipcode:Len([Zipcode])

I have assumed "Zipcode" is the name of the zipcode field in your table. If
the name is something else, replace "ZipCode" on the right side of my
expression with your name.

Steve


Barry said:
Steve,
Doing what you suggested:
Barry
Len([Zipcode])
Click to expand...

returned nothing. Should there be something after the above? like =5 ?
Click to expand...
 
Back
Top