Query help for zip codes

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

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)
 
R

Rick Brandt

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.

SELECT *
FROM TableName
ORDER BY Len(ZipCodeField)
 
J

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
 
F

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;
 
B

Barry

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


.
 
B

Barry

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


.
 
S

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


Barry said:
Steve,
Doing what you suggested:
Barry
Len([Zipcode])

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top