Find and return zip codes

[Jeff]

I have a table of a few hundred thousand records that contains address fields
(AddressLine1, AddressLine2, AddressLine3) and in any one of these fields
could be a City, State Zip combination (the city, state zip is all in the
same field, not slpit apart and the zip might be like #####-#### or just
#####).

I also have another table that is a list of zip codes (only in #####
format). I need to find all the records in the main table that have a zip
code that is in the zip code list.

How can I extract just the zip code from the address fields?

I tried using something like Instr(AddressLine2,[1-9][1-9][1-9][1-9][1-9])
to first get the position of the zip then I was going to use Mid() to pull it
out, but it didn't like the [1-9] part. Am I on the right track and maybe I
just have the syntax wrong or is there something completely different I can
try? Thanks.

 

[KARL DEWEY]

Try something like this ---
SELECT [Table Data].*, [Table Data].AddressLine2
FROM [Table Data], [Table List]
WHERE ((([Table Data].AddressLine2) Like "*" & [Table List].[ZIP] & "*"));

 

[Jeff]

Karl,

That seems to work. Thanks! This is interesting - Can you give me a brief
explanation of what this is really doing? Why does it work? Thanks.

Jeff

Try something like this ---
SELECT [Table Data].*, [Table Data].AddressLine2
FROM [Table Data], [Table List]
WHERE ((([Table Data].AddressLine2) Like "*" & [Table List].[ZIP] & "*"));

--
KARL DEWEY
Build a little - Test a little

I have a table of a few hundred thousand records that contains address fields
(AddressLine1, AddressLine2, AddressLine3) and in any one of these fields
could be a City, State Zip combination (the city, state zip is all in the
same field, not slpit apart and the zip might be like #####-#### or just
#####).

I also have another table that is a list of zip codes (only in #####
format). I need to find all the records in the main table that have a zip
code that is in the zip code list.

How can I extract just the zip code from the address fields?

I tried using something like Instr(AddressLine2,[1-9][1-9][1-9][1-9][1-9])
to first get the position of the zip then I was going to use Mid() to pull it
out, but it didn't like the [1-9] part. Am I on the right track and maybe I
just have the syntax wrong or is there something completely different I can
try? Thanks.

 

[KARL DEWEY]

It is using [Table List].[ZIP] as criteria for [Table Data].[AddressLine2]
and by using the Like function with "*" on both side it say to match any part
of the field.
--
KARL DEWEY
Build a little - Test a little

Karl,

That seems to work. Thanks! This is interesting - Can you give me a brief
explanation of what this is really doing? Why does it work? Thanks.

Jeff

Try something like this ---
SELECT [Table Data].*, [Table Data].AddressLine2
FROM [Table Data], [Table List]
WHERE ((([Table Data].AddressLine2) Like "*" & [Table List].[ZIP] & "*"));

--
KARL DEWEY
Build a little - Test a little

I have a table of a few hundred thousand records that contains address fields
(AddressLine1, AddressLine2, AddressLine3) and in any one of these fields
could be a City, State Zip combination (the city, state zip is all in the
same field, not slpit apart and the zip might be like #####-#### or just
#####).

I also have another table that is a list of zip codes (only in #####
format). I need to find all the records in the main table that have a zip
code that is in the zip code list.

How can I extract just the zip code from the address fields?

I tried using something like Instr(AddressLine2,[1-9][1-9][1-9][1-9][1-9])
to first get the position of the zip then I was going to use Mid() to pull it
out, but it didn't like the [1-9] part. Am I on the right track and maybe I
just have the syntax wrong or is there something completely different I can
try? Thanks.

 

[Jeff]

So is it essentially a cross product query, but because of the criteria it
limits the result to only those records that match the * & [Table List].[ZIP]
& *? Is that what is happening?

It is using [Table List].[ZIP] as criteria for [Table Data].[AddressLine2]
and by using the Like function with "*" on both side it say to match any part
of the field.
--
KARL DEWEY
Build a little - Test a little

Karl,

That seems to work. Thanks! This is interesting - Can you give me a brief
explanation of what this is really doing? Why does it work? Thanks.

Jeff

Try something like this ---
SELECT [Table Data].*, [Table Data].AddressLine2
FROM [Table Data], [Table List]
WHERE ((([Table Data].AddressLine2) Like "*" & [Table List].[ZIP] & "*"));

--
KARL DEWEY
Build a little - Test a little


:

I have a table of a few hundred thousand records that contains address fields
(AddressLine1, AddressLine2, AddressLine3) and in any one of these fields
could be a City, State Zip combination (the city, state zip is all in the
same field, not slpit apart and the zip might be like #####-#### or just
#####).

I also have another table that is a list of zip codes (only in #####
format). I need to find all the records in the main table that have a zip
code that is in the zip code list.

How can I extract just the zip code from the address fields?

I tried using something like Instr(AddressLine2,[1-9][1-9][1-9][1-9][1-9])
to first get the position of the zip then I was going to use Mid() to pull it
out, but it didn't like the [1-9] part. Am I on the right track and maybe I
just have the syntax wrong or is there something completely different I can
try? Thanks.