If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query help for zip codes
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 |
#2
|
|||
|
|||
Query help for zip codes
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 "Barry" wrote in message ... 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 |
#3
|
|||
|
|||
Query help for zip codes
Barry wrote:
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) |
#4
|
|||
|
|||
Query help for zip codes
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 Barry wrote: 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. |
#5
|
|||
|
|||
Query help for zip codes
On Fri, 12 Feb 2010 16:02:01 -0800, Barry wrote:
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; -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail |
#6
|
|||
|
|||
Query help for zip codes
-- Barry "Steve" wrote: 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 "Barry" wrote in message ... 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 . |
#7
|
|||
|
|||
Query help for zip codes
-- Barry "Steve" wrote: 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 "Barry" wrote in message ... 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 . |
#8
|
|||
|
|||
Query help for zip codes
Steve, Doing what you suggested: Barry Len([Zipcode]) returned nothing. Should there be something after the above? like =5 ? |
#9
|
|||
|
|||
Query help for zip codes
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" wrote in message ... Steve, Doing what you suggested: Barry Len([Zipcode]) returned nothing. Should there be something after the above? like =5 ? |
#10
|
|||
|
|||
Query help for zip codes
On Sun, 14 Feb 2010 11:08:01 -0800, Barry
wrote: 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? -- John W. Vinson [MVP] |
|
Thread Tools | |
Display Modes | |
|
|