Query help for zip codes

[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.

 

[Steve]

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)

 

[Rick Brandt]

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.

SELECT *
FROM TableName
ORDER BY Len(ZipCodeField)

 

[John Spencer]

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

 

[fredg]

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.

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]

--
Barry

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)

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.

.

 

[Barry]

--
Barry

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)

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.

.

 

[Barry]

Steve,
Doing what you suggested:
Barry

Len([Zipcode])

returned nothing. Should there be something after the above? like =5 ?

 

[Steve]

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

Steve,
Doing what you suggested:
Barry

Len([Zipcode])

returned nothing. Should there be something after the above? like =5 ?

 

[John W. Vinson]

Steve,
Doing what you suggested:
Barry

Len([Zipcode])

returned nothing. Should there be something after the above? like =5 ?

Where did you put this expression, Barry?

 

[Barry]

John,
I think I have this one figured out. Thanks for your help, though.
--
Barry

Steve,
Doing what you suggested:
Barry

Len([Zipcode])

returned nothing. Should there be something after the above? like =5 ?

Where did you put this expression, Barry?