one-of Zip?

Steph Williams

Is there any easier way to create a list of zips to include in query? Right now I'm doing a big list of "begins with ______" OR "begins with _____" OR....


I can't do "one of" because 12345-1111 is not included if I say "one of 12345".  Looks like the * wild card also doesn't work in a one-of list either.


Am I missing something else? these lists of zips are driving me crazy.

Brenda Heyer

Hi,

What if you tried “like” with a *to begin and
*to end?

So like *M6*

 

I tried a quick one (through Canadian Postal
Codes do not have the trailing numbers) but like may generate the
zips you are looking for?

 

Brenda Heyer
Development
Manager Database and Information

116
Spadina Avenue, Suite 300, Toronto, ON, M5V 2K6 | Tel: 416.323.9521 x259
|
Toll Free: 1.877.399.2333

Twitter: envirodefence |
Facebook: EnvironmentalDefenceCanada
Defending clean water, a safe
climate and healthy
communities. 
Check
out our new website: EnvironmentalDefence.ca

We
are Canada's most effective environmental action organization. We
challenge, and inspire change in government, business and people to
ensure a greener, healthier and prosperous life for
all.

 

Help
inspire change today! Make a charitable donation
in
support of Environmental Defence.

 

Brenda Heyer

Or, instead of putting a *in front, just try Like M6*

Steph Williams

hmm. I'd still have to go through the manual "or" statements for each one, right?

Brenda Heyer

Steph Williams:
hmm. I'd still have to go through the manual "or" statements for each one, right?

Hi Steph, I am unfamiliar with how state zip codes work.  Do all states have a zip that starts with the same two numbers?

Niko Campbell 2

Hi Steph,


Can you provide a short sample of zips in your criteria? 


Thanks,

 

 

Mary McLean

I usually have a long list but as many zips (within a radius of a zip code) are frequently in sequence I use "Between" for example:
Between 12983 and 12989-9999
or
Begins with 12901
or
Between 12913 and 12946-9999

Jen Claudy

At present, I think you have limited options.  If you have a big list, you can Export to another program (MS Access or Excel) and complete your zip criteria there, then add ConstID one of to your existing Query and paste in the IDs from the external program.  If this is a very regularly-used Query, I'd probably invest in the time to do a big series of begins with criteria and then Save As to create a backup.


Regardless, check out the RE Idea Bank and vote for Ideas (even if they're old) like this one (query using wildcards), this one (combine begins with and one of operators), and this one (truncate +4 zips to a 5 digit field).

Zane Magnuson 2

Steph Williams:

Is there any easier way to create a list of zips to include in query? Right now I'm doing a big list of "begins with ______" OR "begins with _____" OR....


I can't do "one of" because 12345-1111 is not included if I say "one of 12345".  Looks like the * wild card also doesn't work in a one-of list either.


Am I missing something else? these lists of zips are driving me crazy.

If you've got your list of ZIPs in a single column in Excel, there's hope! Follow these steps exactly and you can copy/paste a list of things into a "One Of" field:


1) Open up your query and the Excel file with the list of ZIPs


2) In the Query, create your "ZIP code ONE OF <list>" criteria and leave the window open. Switch over to your Excel file.


3) Highlight and copy the cells in Excel. Note: You'll have to copy the cells - it won't work if you click the top of the column to highlight the entire column.


4) Once you've copied the cells, switch back to the criteria in your Query without closing Excel.


5) Click in the first line of the "One of" criteria (as if you were going to type something in). Scroll down a line or two using the scroll bar. Now do CTRL+V to paste in your list.


Note - you can only do this for 500 records at a time. For lists larger than 500 records, just create additional "One of" criteria. And if it's not working, 9 times out of 10 it's because you forgot to scroll down in the One Of criteria window. That's pretty much the key to this whole thing.

Steph Williams

OH yeah. I know that nifty trick of C&P'ing 500 in a list.


But the problem is that even if I copy my list of Zips:

55123

55124

55125

55126

55127

55128


Into a query, any constituents with a zip of, for example, 55126-4321 will not be included. That "one of" list only looks for exact matches, to the related 9-digit zips are not included.  Does that make sense how I'm explaining the problem?

Niko Campbell 2

Hi Steph,


Another set of questions: 


How long is your zipcode list?

Why are you itemizing it down to zip+4s?


Best,

Steph Williams

Niko Campbell:

Hi Steph,


Another set of questions: 


How long is your zipcode list?

Why are you itemizing it down to zip+4s?


Best,

About 30 zip codes on one list, and around the same on another.


I'm not itemizing it.  I am itemizing it to the 5 digits, but if I ONLY include "one of" those 5-digit codes, any people in RE with the full 9-digits will be excluded. Because 55123-4321 does NOT equal 55123.

Brenda Heyer

Hi,

Did you try the like 12345* and bring out the
fields

 

 

When I tried this quickly, with Canadian
postal codes, it brought out all codes starting with M6 and also my
test record which I changed to an American address and added a fake
DPC.

 

Brenda Heyer
Development
Manager Database and Information

116
Spadina Avenue, Suite 300, Toronto, ON, M5V 2K6 | Tel: 416.323.9521 x259
|
Toll Free: 1.877.399.2333

Twitter: envirodefence |
Facebook: EnvironmentalDefenceCanada
Defending clean water, a safe
climate and healthy
communities. 
Check
out our new website: EnvironmentalDefence.ca

We
are Canada's most effective environmental action organization. We
challenge, and inspire change in government, business and people to
ensure a greener, healthier and prosperous life for
all.

 

Help
inspire change today! Make a charitable donation
in
support of Environmental Defence.

 

Niko Campbell 2

Hi Steph,


I believe that Brenda's like solution will meet your needs very well.  I was more curious as to why you wouldn't use city or county to make your search simpler. 

 

Carrie Clark

If you do a lot of zip code queries, I would recommend looking into the plug-in called RecordRadar. Terribly easy to use and allows you to just add a radius of any zip you want to look up. We just got it a month ago and has already been a HUGE help!

 

Steven Koplin

Steph Williams:

Is there any easier way to create a list of zips to include in query? Right now I'm doing a big list of "begins with ______" OR "begins with _____" OR....


I can't do "one of" because 12345-1111 is not included if I say "one of 12345".  Looks like the * wild card also doesn't work in a one-of list either.


Am I missing something else? these lists of zips are driving me crazy.

If the ZIP codes are sequential you can use something like the following:


Preferred ZIP greater than or equal to 100 and

Preferred ZIP less than 120


This will give you all ZIPs from 10000 thru 19999 including ZIP+4's.

 

Lara Grimm

Steph Williams:

Is there any easier way to create a list of zips to include in query? Right now I'm doing a big list of "begins with ______" OR "begins with _____" OR....


I can't do "one of" because 12345-1111 is not included if I say "one of 12345".  Looks like the * wild card also doesn't work in a one-of list either.


Am I missing something else? these lists of zips are driving me crazy.

Hi Steph,

You may have already resolved this issue--if so, please disregard. I'm not sure how you are using the list. But I encountered the same issue a couple of months ago. I was asked to create a mailing list for people living within a certain radius of Portland OR. I found this website https://www.freemaptools.com/find-zip-codes-inside-radius.htm and did a radius search. Not only does it give you all the zip codes within a certain radius of the city, it gives you the corresponding city name. So, in my query, instead of asking for "one of" for the zip code field, I asked for "one of" the cities listed and included state field of Oregon (to eliminate cities of the same name in other states). 

If anyone else out there has used this method and found something wrong with it, please let me know...it seems to have worked okay with my database, but we have a very small database.

Thanks,

Lara Grimm

Maryknoll School

Honolulu, Hawaii

Database Coordinator

Tom Klimchak

Yeah, the Zip+4s are a known challenge.  Have you considered NOT using zip codes and instead using counties?  We've started doing this for most of our geographic lists that are outside our immediate area and we've found that it's a lot easier to run and only marginally increases our lists.  Zip codes can be really, really small and specific areas and they don't always run concurrently (21050-1111 is not necessarily next to 21050-1112). 


Another possible idea:  Export out every address with AddressID and Zip Code (includes the +4).  Use Excel to split the zip code from the +4 data.  Re-import just the Zip Code to each address as an attribute.  Repeat every few months.


For zip codes we tend to use MapPoint 2013... It's cheap and simple, but it's not being updated so there will come a time when we'll have to move on to something else.

 

Steph Williams:

Is there any easier way to create a list of zips to include in query? Right now I'm doing a big list of "begins with ______" OR "begins with _____" OR....


I can't do "one of" because 12345-1111 is not included if I say "one of 12345".  Looks like the * wild card also doesn't work in a one-of list either.


Am I missing something else? these lists of zips are driving me crazy.

 

Lise Twiford

Zane Magnuson:

 

Steph Williams:

Is there any easier way to create a list of zips to include in query? Right now I'm doing a big list of "begins with ______" OR "begins with _____" OR....


I can't do "one of" because 12345-1111 is not included if I say "one of 12345".  Looks like the * wild card also doesn't work in a one-of list either.


Am I missing something else? these lists of zips are driving me crazy.

If you've got your list of ZIPs in a single column in Excel, there's hope! Follow these steps exactly and you can copy/paste a list of things into a "One Of" field:


1) Open up your query and the Excel file with the list of ZIPs


2) In the Query, create your "ZIP code ONE OF <list>" criteria and leave the window open. Switch over to your Excel file.


3) Highlight and copy the cells in Excel. Note: You'll have to copy the cells - it won't work if you click the top of the column to highlight the entire column.


4) Once you've copied the cells, switch back to the criteria in your Query without closing Excel.


5) Click in the first line of the "One of" criteria (as if you were going to type something in). Scroll down a line or two using the scroll bar. Now do CTRL+V to paste in your list.


Note - you can only do this for 500 records at a time. For lists larger than 500 records, just create additional "One of" criteria. And if it's not working, 9 times out of 10 it's because you forgot to scroll down in the One Of criteria window. That's pretty much the key to this whole thing.

 

THANK YOU!  This solved an on-going problem for me.  Truly appreciate your help.  This saved me at least an hour!

Best,

Lise Twiford

Dariel Dixon 2

Lise Twiford:

Zane Magnuson:

 

Steph Williams:

Is there any easier way to create a list of zips to include in query? Right now I'm doing a big list of "begins with ______" OR "begins with _____" OR....


I can't do "one of" because 12345-1111 is not included if I say "one of 12345".  Looks like the * wild card also doesn't work in a one-of list either.


Am I missing something else? these lists of zips are driving me crazy.

If you've got your list of ZIPs in a single column in Excel, there's hope! Follow these steps exactly and you can copy/paste a list of things into a "One Of" field:


1) Open up your query and the Excel file with the list of ZIPs


2) In the Query, create your "ZIP code ONE OF <list>" criteria and leave the window open. Switch over to your Excel file.


3) Highlight and copy the cells in Excel. Note: You'll have to copy the cells - it won't work if you click the top of the column to highlight the entire column.


4) Once you've copied the cells, switch back to the criteria in your Query without closing Excel.


5) Click in the first line of the "One of" criteria (as if you were going to type something in). Scroll down a line or two using the scroll bar. Now do CTRL+V to paste in your list.


Note - you can only do this for 500 records at a time. For lists larger than 500 records, just create additional "One of" criteria. And if it's not working, 9 times out of 10 it's because you forgot to scroll down in the One Of criteria window. That's pretty much the key to this whole thing.

 

THANK YOU!  This solved an on-going problem for me.  Truly appreciate your help.  This saved me at least an hour!

Best,

Lise Twiford

 

I think the SmartTHING plug-in called SmartPaste is excellent for this.  And it's free.  It pretty much does this exact thing, except it will automatically create the additional one of queries.  I think they can be found online.  But I used it a number of times.