Query for Left(String,5) - Access 2007

[JohnH]

I found this response in a post regarding a query I'm trying to do. It looks
like it's what I need but I don't know how/were to implement it in the query.

what I found
Hi,
do you have these zip codes stored already? Then just use this in your
criteria:

Left ([YourField], 3)

If not than create a zip code table and include the zipcode field in the
query. Then set the criteria similar.
HTH
Good luck

I have a table(excel file) of 5 digit zipcodes.
I'm linking it to a name-address table that has a 9 digit zip.
I only find records with the exact 5 digit match - not the many that have +4

How/where do I add the left(yourfiled,5) above.
I've always done this by adding a like function in the zip filed for all the
zips I need but the above method would be much clearner and easier - assuming
I can learn how to do it.

Thanks
John

 

[Klatuu]

it would be easier to use:
Like Zip5 & "*"

It will then find all matching 5 digits and all that start with the 5 digit
code.

 

[JohnH]

Thanks Klatuu,
That's the part I don't know how to do.
I'm not very versed in Access-Query
The way I've set this query up is:
I'm linking the zip5 excel file to the nameaddress table via the zip to zip
- join type
1 (there are 40+zipcodes in the zip5 excel files)

The only time I've used like is in the criteral when I'n only using the
nameaddress table and then I'd type in all the likes manually - though
usually there are only 4 or 5 zips. This time the 40+ makes that impractical
for a novis like me.

How do I link the zip5 excel to the nameaddress and tell the nameaddress to
pull all records that have a zip that starts with the first 5 digits of the
zip5 field?

it would be easier to use:
Like Zip5 & "*"

It will then find all matching 5 digits and all that start with the 5 digit
code.
--
Dave Hargis, Microsoft Access MVP

I found this response in a post regarding a query I'm trying to do. It looks
like it's what I need but I don't know how/were to implement it in the query.

what I found
Hi,
do you have these zip codes stored already? Then just use this in your
criteria:

Left ([YourField], 3)

If not than create a zip code table and include the zipcode field in the
query. Then set the criteria similar.
HTH
Good luck

I have a table(excel file) of 5 digit zipcodes.
I'm linking it to a name-address table that has a 9 digit zip.
I only find records with the exact 5 digit match - not the many that have +4

How/where do I add the left(yourfiled,5) above.
I've always done this by adding a like function in the zip filed for all the
zips I need but the above method would be much clearner and easier - assuming
I can learn how to do it.

Thanks
John

 

[Klatuu]

It isn't a Link, it is criteria in a Where clause. You can set it up in the
query builder.
In the column for the zip5 in the linked excel sheet in the Criteria row,
type in:

Like [nameaddresses].[zipfieldname] & "*"
--
Dave Hargis, Microsoft Access MVP

Thanks Klatuu,
That's the part I don't know how to do.
I'm not very versed in Access-Query
The way I've set this query up is:
I'm linking the zip5 excel file to the nameaddress table via the zip to zip
- join type
1 (there are 40+zipcodes in the zip5 excel files)

The only time I've used like is in the criteral when I'n only using the
nameaddress table and then I'd type in all the likes manually - though
usually there are only 4 or 5 zips. This time the 40+ makes that impractical
for a novis like me.

How do I link the zip5 excel to the nameaddress and tell the nameaddress to
pull all records that have a zip that starts with the first 5 digits of the
zip5 field?

it would be easier to use:
Like Zip5 & "*"

It will then find all matching 5 digits and all that start with the 5 digit
code.
--
Dave Hargis, Microsoft Access MVP

I found this response in a post regarding a query I'm trying to do. It looks
like it's what I need but I don't know how/were to implement it in the query.

what I found
Hi,
do you have these zip codes stored already? Then just use this in your
criteria:

Left ([YourField], 3)

If not than create a zip code table and include the zipcode field in the
query. Then set the criteria similar.
HTH
Good luck

I have a table(excel file) of 5 digit zipcodes.
I'm linking it to a name-address table that has a 9 digit zip.
I only find records with the exact 5 digit match - not the many that have +4

How/where do I add the left(yourfiled,5) above.
I've always done this by adding a like function in the zip filed for all the
zips I need but the above method would be much clearner and easier - assuming
I can learn how to do it.

Thanks
John

 

[JohnH]

thanks again Kaltuu.

It's still not giving me any reocords with zip +4. Below is the sql view.
Perhaps you can see where (not pun intended) I'm making my mistake?

Boston excel is the 5 digit zip single field named boston
Name has the member code and status
Name_address has the full postal address with zip+4 for the preferred address
Demographics has opt-out options

SELECT boston.Boston, Name_Address.ZIP, Name.MEMBER_TYPE, Name.CATEGORY,
Name.STATUS, Name.PREFIX, Name.FIRST_NAME, Name.TITLE, Name.COMPANY,
Name_Address.ADDRESS_1, Name_Address.ADDRESS_2, Name_Address.CITY,
Name_Address.STATE_PROVINCE, Name_Address.ZIP, Name.WORK_PHONE, Name.EMAIL,
dbo_Demographics.NO_MAIL, dbo_Demographics.NO_EMAIL
FROM ((boston INNER JOIN Name ON boston.Boston = Name.ZIP) INNER JOIN
Name_Address ON Name.MAIL_ADDRESS_NUM = Name_Address.ADDRESS_NUM) LEFT JOIN
dbo_Demographics ON Name.ID = dbo_Demographics.ID
WHERE (((boston.Boston) Like [name_address].[zip] & "*") AND
((Name.MEMBER_TYPE)="MK" Or (Name.MEMBER_TYPE)="pi") AND ((Name.STATUS)="A"
Or (Name.STATUS)="I") AND ((dbo_Demographics.NO_MAIL)<>1) AND
((dbo_Demographics.NO_EMAIL)<>1));

Thanks
John

It isn't a Link, it is criteria in a Where clause. You can set it up in the
query builder.
In the column for the zip5 in the linked excel sheet in the Criteria row,
type in:

Like [nameaddresses].[zipfieldname] & "*"
--
Dave Hargis, Microsoft Access MVP

Thanks Klatuu,
That's the part I don't know how to do.
I'm not very versed in Access-Query
The way I've set this query up is:
I'm linking the zip5 excel file to the nameaddress table via the zip to zip
- join type
1 (there are 40+zipcodes in the zip5 excel files)

The only time I've used like is in the criteral when I'n only using the
nameaddress table and then I'd type in all the likes manually - though
usually there are only 4 or 5 zips. This time the 40+ makes that impractical
for a novis like me.

How do I link the zip5 excel to the nameaddress and tell the nameaddress to
pull all records that have a zip that starts with the first 5 digits of the
zip5 field?

it would be easier to use:
Like Zip5 & "*"

It will then find all matching 5 digits and all that start with the 5 digit
code.
--
Dave Hargis, Microsoft Access MVP


:

I found this response in a post regarding a query I'm trying to do. It looks
like it's what I need but I don't know how/were to implement it in the query.

what I found
Hi,
do you have these zip codes stored already? Then just use this in your
criteria:

Left ([YourField], 3)

If not than create a zip code table and include the zipcode field in the
query. Then set the criteria similar.
HTH
Good luck

I have a table(excel file) of 5 digit zipcodes.
I'm linking it to a name-address table that has a 9 digit zip.
I only find records with the exact 5 digit match - not the many that have +4

How/where do I add the left(yourfiled,5) above.
I've always done this by adding a like function in the zip filed for all the
zips I need but the above method would be much clearner and easier - assuming
I can learn how to do it.

Thanks
John

 

[Klatuu]

Is boston the name of the zip code field in the table boston?

Using Name as a name is Access is not a good idea. It is a reserved word
and can cause problems. At least enclose it in brackets [name] to help
prevent any confustion to Access.

Or (Name.STATUS)="I") AND ((dbo_Demographics.NO_MAIL)<>1) AND
((dbo_Demographics.NO_EMAIL)<>1));

This part will return only recoords with a Name.Status of I that have opted
out of contact by mail and email, but if the member only opted out of one of
the two, they will be included in the results.

Double check your criteria, because I don't see any reason you should get no
return.

Or, maybe I am misunderstanding the problem. Is it you are trying to match
records by the two Zipcodes?

--
Dave Hargis, Microsoft Access MVP

thanks again Kaltuu.

It's still not giving me any reocords with zip +4. Below is the sql view.
Perhaps you can see where (not pun intended) I'm making my mistake?

Boston excel is the 5 digit zip single field named boston
Name has the member code and status
Name_address has the full postal address with zip+4 for the preferred address
Demographics has opt-out options

SELECT boston.Boston, Name_Address.ZIP, Name.MEMBER_TYPE, Name.CATEGORY,
Name.STATUS, Name.PREFIX, Name.FIRST_NAME, Name.TITLE, Name.COMPANY,
Name_Address.ADDRESS_1, Name_Address.ADDRESS_2, Name_Address.CITY,
Name_Address.STATE_PROVINCE, Name_Address.ZIP, Name.WORK_PHONE, Name.EMAIL,
dbo_Demographics.NO_MAIL, dbo_Demographics.NO_EMAIL
FROM ((boston INNER JOIN Name ON boston.Boston = Name.ZIP) INNER JOIN
Name_Address ON Name.MAIL_ADDRESS_NUM = Name_Address.ADDRESS_NUM) LEFT JOIN
dbo_Demographics ON Name.ID = dbo_Demographics.ID
WHERE (((boston.Boston) Like [name_address].[zip] & "*") AND
((Name.MEMBER_TYPE)="MK" Or (Name.MEMBER_TYPE)="pi") AND ((Name.STATUS)="A"
Or (Name.STATUS)="I") AND ((dbo_Demographics.NO_MAIL)<>1) AND
((dbo_Demographics.NO_EMAIL)<>1));

Thanks
John

It isn't a Link, it is criteria in a Where clause. You can set it up in the
query builder.
In the column for the zip5 in the linked excel sheet in the Criteria row,
type in:

Like [nameaddresses].[zipfieldname] & "*"
--
Dave Hargis, Microsoft Access MVP

Thanks Klatuu,
That's the part I don't know how to do.
I'm not very versed in Access-Query
The way I've set this query up is:
I'm linking the zip5 excel file to the nameaddress table via the zip to zip
- join type
1 (there are 40+zipcodes in the zip5 excel files)

The only time I've used like is in the criteral when I'n only using the
nameaddress table and then I'd type in all the likes manually - though
usually there are only 4 or 5 zips. This time the 40+ makes that impractical
for a novis like me.

How do I link the zip5 excel to the nameaddress and tell the nameaddress to
pull all records that have a zip that starts with the first 5 digits of the
zip5 field?

:

it would be easier to use:
Like Zip5 & "*"

It will then find all matching 5 digits and all that start with the 5 digit
code.
--
Dave Hargis, Microsoft Access MVP


:

I found this response in a post regarding a query I'm trying to do. It looks
like it's what I need but I don't know how/were to implement it in the query.

what I found
Hi,
do you have these zip codes stored already? Then just use this in your
criteria:

Left ([YourField], 3)

If not than create a zip code table and include the zipcode field in the
query. Then set the criteria similar.
HTH
Good luck

I have a table(excel file) of 5 digit zipcodes.
I'm linking it to a name-address table that has a 9 digit zip.
I only find records with the exact 5 digit match - not the many that have +4

How/where do I add the left(yourfiled,5) above.
I've always done this by adding a like function in the zip filed for all the
zips I need but the above method would be much clearner and easier - assuming
I can learn how to do it.

Thanks
John

 

[John Spencer]

Pardon me for dropping in, but perhaps what we are looking for is something
like the following. Note that this query cannot use the query design view,
but must be done in the SQL view (the non-equi join causes this problem).

SELECT boston.Boston, Name_Address.ZIP, Name.MEMBER_TYPE, Name.CATEGORY,
Name.STATUS, Name.PREFIX, Name.FIRST_NAME, Name.TITLE, Name.COMPANY,
Name_Address.ADDRESS_1, Name_Address.ADDRESS_2, Name_Address.CITY,
Name_Address.STATE_PROVINCE, Name_Address.ZIP, Name.WORK_PHONE, Name.EMAIL,
dbo_Demographics.NO_MAIL, dbo_Demographics.NO_EMAIL

FROM (([Name]INNER JOIN Name BOSTON ON Name.ZIP LIKE (boston.Boston & "*"))

INNER JOIN
Name_Address ON Name.MAIL_ADDRESS_NUM = Name_Address.ADDRESS_NUM) LEFT JOIN
dbo_Demographics ON Name.ID = dbo_Demographics.ID

WHERE ((
((Name.MEMBER_TYPE)="MK" Or (Name.MEMBER_TYPE)="pi") AND ((Name.STATUS)="A"
Or (Name.STATUS)="I") AND ((dbo_Demographics.NO_MAIL)<>1) AND
((dbo_Demographics.NO_EMAIL)<>1));

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

thanks again Kaltuu.

It's still not giving me any reocords with zip +4. Below is the sql view.
Perhaps you can see where (not pun intended) I'm making my mistake?

Boston excel is the 5 digit zip single field named boston
Name has the member code and status
Name_address has the full postal address with zip+4 for the preferred address
Demographics has opt-out options

SELECT boston.Boston, Name_Address.ZIP, Name.MEMBER_TYPE, Name.CATEGORY,
Name.STATUS, Name.PREFIX, Name.FIRST_NAME, Name.TITLE, Name.COMPANY,
Name_Address.ADDRESS_1, Name_Address.ADDRESS_2, Name_Address.CITY,
Name_Address.STATE_PROVINCE, Name_Address.ZIP, Name.WORK_PHONE, Name.EMAIL,
dbo_Demographics.NO_MAIL, dbo_Demographics.NO_EMAIL
FROM ((boston INNER JOIN Name ON boston.Boston = Name.ZIP) INNER JOIN
Name_Address ON Name.MAIL_ADDRESS_NUM = Name_Address.ADDRESS_NUM) LEFT JOIN
dbo_Demographics ON Name.ID = dbo_Demographics.ID
WHERE (((boston.Boston) Like [name_address].[zip] & "*") AND
((Name.MEMBER_TYPE)="MK" Or (Name.MEMBER_TYPE)="pi") AND ((Name.STATUS)="A"
Or (Name.STATUS)="I") AND ((dbo_Demographics.NO_MAIL)<>1) AND
((dbo_Demographics.NO_EMAIL)<>1));

Thanks
John

It isn't a Link, it is criteria in a Where clause. You can set it up in the
query builder.
In the column for the zip5 in the linked excel sheet in the Criteria row,
type in:

Like [nameaddresses].[zipfieldname] & "*"
--
Dave Hargis, Microsoft Access MVP

Thanks Klatuu,
That's the part I don't know how to do.
I'm not very versed in Access-Query
The way I've set this query up is:
I'm linking the zip5 excel file to the nameaddress table via the zip to zip
- join type
1 (there are 40+zipcodes in the zip5 excel files)

The only time I've used like is in the criteral when I'n only using the
nameaddress table and then I'd type in all the likes manually - though
usually there are only 4 or 5 zips. This time the 40+ makes that impractical
for a novis like me.

How do I link the zip5 excel to the nameaddress and tell the nameaddress to
pull all records that have a zip that starts with the first 5 digits of the
zip5 field?

:

it would be easier to use:
Like Zip5 & "*"

It will then find all matching 5 digits and all that start with the 5 digit
code.
--
Dave Hargis, Microsoft Access MVP


:

I found this response in a post regarding a query I'm trying to do. It looks
like it's what I need but I don't know how/were to implement it in the query.

what I found
Hi,
do you have these zip codes stored already? Then just use this in your
criteria:

Left ([YourField], 3)

If not than create a zip code table and include the zipcode field in the
query. Then set the criteria similar.
HTH
Good luck

I have a table(excel file) of 5 digit zipcodes.
I'm linking it to a name-address table that has a 9 digit zip.
I only find records with the exact 5 digit match - not the many that have +4

How/where do I add the left(yourfiled,5) above.
I've always done this by adding a like function in the zip filed for all the
zips I need but the above method would be much clearner and easier - assuming
I can learn how to do it.

Thanks
John

 

[Klatuu]

I think that is more what the OP wants, John, except boston.boston is the 5
digit field and Name.zip is the 9 digit version as I read the post.
--
Dave Hargis, Microsoft Access MVP

Pardon me for dropping in, but perhaps what we are looking for is something
like the following. Note that this query cannot use the query design view,
but must be done in the SQL view (the non-equi join causes this problem).

SELECT boston.Boston, Name_Address.ZIP, Name.MEMBER_TYPE, Name.CATEGORY,
Name.STATUS, Name.PREFIX, Name.FIRST_NAME, Name.TITLE, Name.COMPANY,
Name_Address.ADDRESS_1, Name_Address.ADDRESS_2, Name_Address.CITY,
Name_Address.STATE_PROVINCE, Name_Address.ZIP, Name.WORK_PHONE, Name.EMAIL,
dbo_Demographics.NO_MAIL, dbo_Demographics.NO_EMAIL

FROM (([Name]INNER JOIN Name BOSTON ON Name.ZIP LIKE (boston.Boston & "*"))

INNER JOIN
Name_Address ON Name.MAIL_ADDRESS_NUM = Name_Address.ADDRESS_NUM) LEFT JOIN
dbo_Demographics ON Name.ID = dbo_Demographics.ID

WHERE ((
((Name.MEMBER_TYPE)="MK" Or (Name.MEMBER_TYPE)="pi") AND ((Name.STATUS)="A"
Or (Name.STATUS)="I") AND ((dbo_Demographics.NO_MAIL)<>1) AND
((dbo_Demographics.NO_EMAIL)<>1));

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

thanks again Kaltuu.

It's still not giving me any reocords with zip +4. Below is the sql view.
Perhaps you can see where (not pun intended) I'm making my mistake?

Boston excel is the 5 digit zip single field named boston
Name has the member code and status
Name_address has the full postal address with zip+4 for the preferred address
Demographics has opt-out options

SELECT boston.Boston, Name_Address.ZIP, Name.MEMBER_TYPE, Name.CATEGORY,
Name.STATUS, Name.PREFIX, Name.FIRST_NAME, Name.TITLE, Name.COMPANY,
Name_Address.ADDRESS_1, Name_Address.ADDRESS_2, Name_Address.CITY,
Name_Address.STATE_PROVINCE, Name_Address.ZIP, Name.WORK_PHONE, Name.EMAIL,
dbo_Demographics.NO_MAIL, dbo_Demographics.NO_EMAIL
FROM ((boston INNER JOIN Name ON boston.Boston = Name.ZIP) INNER JOIN
Name_Address ON Name.MAIL_ADDRESS_NUM = Name_Address.ADDRESS_NUM) LEFT JOIN
dbo_Demographics ON Name.ID = dbo_Demographics.ID
WHERE (((boston.Boston) Like [name_address].[zip] & "*") AND
((Name.MEMBER_TYPE)="MK" Or (Name.MEMBER_TYPE)="pi") AND ((Name.STATUS)="A"
Or (Name.STATUS)="I") AND ((dbo_Demographics.NO_MAIL)<>1) AND
((dbo_Demographics.NO_EMAIL)<>1));

Thanks
John

It isn't a Link, it is criteria in a Where clause. You can set it up in the
query builder.
In the column for the zip5 in the linked excel sheet in the Criteria row,
type in:

Like [nameaddresses].[zipfieldname] & "*"
--
Dave Hargis, Microsoft Access MVP


:

Thanks Klatuu,
That's the part I don't know how to do.
I'm not very versed in Access-Query
The way I've set this query up is:
I'm linking the zip5 excel file to the nameaddress table via the zip to zip
- join type
1 (there are 40+zipcodes in the zip5 excel files)

The only time I've used like is in the criteral when I'n only using the
nameaddress table and then I'd type in all the likes manually - though
usually there are only 4 or 5 zips. This time the 40+ makes that impractical
for a novis like me.

How do I link the zip5 excel to the nameaddress and tell the nameaddress to
pull all records that have a zip that starts with the first 5 digits of the
zip5 field?

:

it would be easier to use:
Like Zip5 & "*"

It will then find all matching 5 digits and all that start with the 5 digit
code.
--
Dave Hargis, Microsoft Access MVP


:

I found this response in a post regarding a query I'm trying to do. It looks
like it's what I need but I don't know how/were to implement it in the query.

what I found
Hi,
do you have these zip codes stored already? Then just use this in your
criteria:

Left ([YourField], 3)

If not than create a zip code table and include the zipcode field in the
query. Then set the criteria similar.
HTH
Good luck

I have a table(excel file) of 5 digit zipcodes.
I'm linking it to a name-address table that has a 9 digit zip.
I only find records with the exact 5 digit match - not the many that have +4

How/where do I add the left(yourfiled,5) above.
I've always done this by adding a like function in the zip filed for all the
zips I need but the above method would be much clearner and easier - assuming
I can learn how to do it.

Thanks
John

 

[JohnH]

Boston is the name of the zipcode filed in table boston. Its the only field
in the table. It was sent to me like that with a request for all records from
or DB in those zips.

I don't think I'm running into issues with the table named "name"? I use it
all the time and I'm confident I get the data I'm looking for.

Boston is the outside join. I think that might be my problem?

When I do a simple query of the Name table of - where zip like "02141*" - it
returns many records of 02141-xxxx. These records meet all the other criteria
of type, status and optout, but none of the 02141-xxx records show up in my
full query using the boston table as the primary link. I'm pretty sure my
mistake is how I'm linking boston with the 5 digit zip to the name table with
the 9 digit zip but I don't know how use boston as the primary table to find
records in name that have th zip from boston.

Thanks
John


Thanks
John

Is boston the name of the zip code field in the table boston?

Using Name as a name is Access is not a good idea. It is a reserved word
and can cause problems. At least enclose it in brackets [name] to help
prevent any confustion to Access.

Or (Name.STATUS)="I") AND ((dbo_Demographics.NO_MAIL)<>1) AND
((dbo_Demographics.NO_EMAIL)<>1));

This part will return only recoords with a Name.Status of I that have opted
out of contact by mail and email, but if the member only opted out of one of
the two, they will be included in the results.

Double check your criteria, because I don't see any reason you should get no
return.

Or, maybe I am misunderstanding the problem. Is it you are trying to match
records by the two Zipcodes?

--
Dave Hargis, Microsoft Access MVP

thanks again Kaltuu.

It's still not giving me any reocords with zip +4. Below is the sql view.
Perhaps you can see where (not pun intended) I'm making my mistake?

Boston excel is the 5 digit zip single field named boston
Name has the member code and status
Name_address has the full postal address with zip+4 for the preferred address
Demographics has opt-out options

SELECT boston.Boston, Name_Address.ZIP, Name.MEMBER_TYPE, Name.CATEGORY,
Name.STATUS, Name.PREFIX, Name.FIRST_NAME, Name.TITLE, Name.COMPANY,
Name_Address.ADDRESS_1, Name_Address.ADDRESS_2, Name_Address.CITY,
Name_Address.STATE_PROVINCE, Name_Address.ZIP, Name.WORK_PHONE, Name.EMAIL,
dbo_Demographics.NO_MAIL, dbo_Demographics.NO_EMAIL
FROM ((boston INNER JOIN Name ON boston.Boston = Name.ZIP) INNER JOIN
Name_Address ON Name.MAIL_ADDRESS_NUM = Name_Address.ADDRESS_NUM) LEFT JOIN
dbo_Demographics ON Name.ID = dbo_Demographics.ID
WHERE (((boston.Boston) Like [name_address].[zip] & "*") AND
((Name.MEMBER_TYPE)="MK" Or (Name.MEMBER_TYPE)="pi") AND ((Name.STATUS)="A"
Or (Name.STATUS)="I") AND ((dbo_Demographics.NO_MAIL)<>1) AND
((dbo_Demographics.NO_EMAIL)<>1));

Thanks
John

It isn't a Link, it is criteria in a Where clause. You can set it up in the
query builder.
In the column for the zip5 in the linked excel sheet in the Criteria row,
type in:

Like [nameaddresses].[zipfieldname] & "*"
--
Dave Hargis, Microsoft Access MVP


:

Thanks Klatuu,
That's the part I don't know how to do.
I'm not very versed in Access-Query
The way I've set this query up is:
I'm linking the zip5 excel file to the nameaddress table via the zip to zip
- join type
1 (there are 40+zipcodes in the zip5 excel files)

The only time I've used like is in the criteral when I'n only using the
nameaddress table and then I'd type in all the likes manually - though
usually there are only 4 or 5 zips. This time the 40+ makes that impractical
for a novis like me.

How do I link the zip5 excel to the nameaddress and tell the nameaddress to
pull all records that have a zip that starts with the first 5 digits of the
zip5 field?

:

it would be easier to use:
Like Zip5 & "*"

It will then find all matching 5 digits and all that start with the 5 digit
code.
--
Dave Hargis, Microsoft Access MVP


:

I found this response in a post regarding a query I'm trying to do. It looks
like it's what I need but I don't know how/were to implement it in the query.

what I found
Hi,
do you have these zip codes stored already? Then just use this in your
criteria:

Left ([YourField], 3)

If not than create a zip code table and include the zipcode field in the
query. Then set the criteria similar.
HTH
Good luck

I have a table(excel file) of 5 digit zipcodes.
I'm linking it to a name-address table that has a 9 digit zip.
I only find records with the exact 5 digit match - not the many that have +4

How/where do I add the left(yourfiled,5) above.
I've always done this by adding a like function in the zip filed for all the
zips I need but the above method would be much clearner and easier - assuming
I can learn how to do it.

Thanks
John

 

[JohnH]

Thanks John,
Where do I enter sql code to create a query? I've never used anything other
then the design view for creating queries. I've never fully understood the
point of the sql view since you can't edit it? I suspect I can edit sql
somewhere?

Thanks
The-other-John

Pardon me for dropping in, but perhaps what we are looking for is something
like the following. Note that this query cannot use the query design view,
but must be done in the SQL view (the non-equi join causes this problem).

SELECT boston.Boston, Name_Address.ZIP, Name.MEMBER_TYPE, Name.CATEGORY,
Name.STATUS, Name.PREFIX, Name.FIRST_NAME, Name.TITLE, Name.COMPANY,
Name_Address.ADDRESS_1, Name_Address.ADDRESS_2, Name_Address.CITY,
Name_Address.STATE_PROVINCE, Name_Address.ZIP, Name.WORK_PHONE, Name.EMAIL,
dbo_Demographics.NO_MAIL, dbo_Demographics.NO_EMAIL

FROM (([Name]INNER JOIN Name BOSTON ON Name.ZIP LIKE (boston.Boston & "*"))

INNER JOIN
Name_Address ON Name.MAIL_ADDRESS_NUM = Name_Address.ADDRESS_NUM) LEFT JOIN
dbo_Demographics ON Name.ID = dbo_Demographics.ID

WHERE ((
((Name.MEMBER_TYPE)="MK" Or (Name.MEMBER_TYPE)="pi") AND ((Name.STATUS)="A"
Or (Name.STATUS)="I") AND ((dbo_Demographics.NO_MAIL)<>1) AND
((dbo_Demographics.NO_EMAIL)<>1));

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

thanks again Kaltuu.

It's still not giving me any reocords with zip +4. Below is the sql view.
Perhaps you can see where (not pun intended) I'm making my mistake?

Boston excel is the 5 digit zip single field named boston
Name has the member code and status
Name_address has the full postal address with zip+4 for the preferred address
Demographics has opt-out options

SELECT boston.Boston, Name_Address.ZIP, Name.MEMBER_TYPE, Name.CATEGORY,
Name.STATUS, Name.PREFIX, Name.FIRST_NAME, Name.TITLE, Name.COMPANY,
Name_Address.ADDRESS_1, Name_Address.ADDRESS_2, Name_Address.CITY,
Name_Address.STATE_PROVINCE, Name_Address.ZIP, Name.WORK_PHONE, Name.EMAIL,
dbo_Demographics.NO_MAIL, dbo_Demographics.NO_EMAIL
FROM ((boston INNER JOIN Name ON boston.Boston = Name.ZIP) INNER JOIN
Name_Address ON Name.MAIL_ADDRESS_NUM = Name_Address.ADDRESS_NUM) LEFT JOIN
dbo_Demographics ON Name.ID = dbo_Demographics.ID
WHERE (((boston.Boston) Like [name_address].[zip] & "*") AND
((Name.MEMBER_TYPE)="MK" Or (Name.MEMBER_TYPE)="pi") AND ((Name.STATUS)="A"
Or (Name.STATUS)="I") AND ((dbo_Demographics.NO_MAIL)<>1) AND
((dbo_Demographics.NO_EMAIL)<>1));

Thanks
John

It isn't a Link, it is criteria in a Where clause. You can set it up in the
query builder.
In the column for the zip5 in the linked excel sheet in the Criteria row,
type in:

Like [nameaddresses].[zipfieldname] & "*"
--
Dave Hargis, Microsoft Access MVP


:

Thanks Klatuu,
That's the part I don't know how to do.
I'm not very versed in Access-Query
The way I've set this query up is:
I'm linking the zip5 excel file to the nameaddress table via the zip to zip
- join type
1 (there are 40+zipcodes in the zip5 excel files)

The only time I've used like is in the criteral when I'n only using the
nameaddress table and then I'd type in all the likes manually - though
usually there are only 4 or 5 zips. This time the 40+ makes that impractical
for a novis like me.

How do I link the zip5 excel to the nameaddress and tell the nameaddress to
pull all records that have a zip that starts with the first 5 digits of the
zip5 field?

:

it would be easier to use:
Like Zip5 & "*"

It will then find all matching 5 digits and all that start with the 5 digit
code.
--
Dave Hargis, Microsoft Access MVP


:

I found this response in a post regarding a query I'm trying to do. It looks
like it's what I need but I don't know how/were to implement it in the query.

what I found
Hi,
do you have these zip codes stored already? Then just use this in your
criteria:

Left ([YourField], 3)

If not than create a zip code table and include the zipcode field in the
query. Then set the criteria similar.
HTH
Good luck

I have a table(excel file) of 5 digit zipcodes.
I'm linking it to a name-address table that has a 9 digit zip.
I only find records with the exact 5 digit match - not the many that have +4

How/where do I add the left(yourfiled,5) above.
I've always done this by adding a like function in the zip filed for all the
zips I need but the above method would be much clearner and easier - assuming
I can learn how to do it.

Thanks
John

 

[John Spencer]

Well, you certainly can edit the SQL view. I do it all the time and
often I start there.

The Query design view actually creates SQL. It is just a user interface
that simplifies the process of writing the SQL.

If you open the sql view you should be able to paste the code I posted
into it. You might want to make a backup copy of your current query first.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[JohnH]

Thanks John
I didn't realize you could edit the sql in the view?
I pasted your sql into a copy my query but I'm getting syntax errors on the
where clause. I think it's a paren issue but I haven't been able to figure it
out yet.

Thanks
John

 

[JohnH]

oohn,
I spoke too soon. I found the syntax issue. One too many parens at the
beginning. Took that out and worked like a charm. GOt the results I needed.

Thanks So much!

Thanks to Klatuu as well.

You were both very helpful

 

[JohnH]

Well John it sort of worked.
The query runs and I see my data but if I try to do something like go to the
end of the list or export it to Excel Access locks up and I have to
Taskmanager end Access to get out.

Not sure what that's about?

Thanks
John

 

[John Spencer]

I would look for a null value in one (or more) of the Boston.Boston
values. If that is the case, the query is going to try to link every
record in the NAME table to that record (or records) in the Boston table.

So, are there any records in Boston table where the Boston field is
blank (null)? If so, we need to work around that.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[JohnH]

MY CPU is running @ 100% as soon as I try to do anything inside of or with
the result list. I've let it run for 10 minutes or more to see if it will
come back but so far it has not. Not sure why when I run the query it
prompts for a zip value. I can leave it blank and it runs. It prompts for a
zip value when I attempt to export to Excel as well and thats when it locks
up and goes to 100% CPU usage.

Interesting?

 

[JohnH]

No Null values. There are 64 records in Boston and all have valid zipcodes.
Boston is the only field in the table. It is a Linked Excel file if that
means anything.

Thanks
John

 

[JohnH]

I did try createding an access table from the linked exxel file and using it
instead of the linked excel file only because I can't think of anything
else, but same issue. As soon as I try to go to the bottom of the result list
and/or export the results to excel I lock up with 100%cpu usage until I end
task. Just for kicks I tried my usual metod of putting the like "*12345*" in
the name.zip criteia and that works fine except that I have to manually put
the zips in and can only put in so many before the query is too big and I
have to run it multiple times to get all the zips in...yuk.

Thanks
John

 

[JohnH]

Hi John, wanted you and Klatuu know I solved my problem. I can do the query
using design view if I simply don't do a join between the zip table and the
name table. I just put the cireteria in the where clause that you and Klatuu
said to use and tha did the trick.I'll bet you two just assumed I was doing
that already. Sorry it took so long for the light to dawn.

Thanks again for your help.

John