Re: Query for Left(String,5) - Access 2007

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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


"John Spencer" wrote:

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

JohnH wrote:
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


"Klatuu" wrote:

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


"JohnH" wrote:

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?

"Klatuu" wrote:

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


"JohnH" wrote:

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



.

Relevant Pages

  • Re: Query for Left(String,5) - Access 2007
    ... Where do I enter sql code to create a query? ... FROM ((boston INNER JOIN Name ON boston.Boston = Name.ZIP) INNER JOIN ... It will then find all matching 5 digits and all that start with the 5 digit ...
    (microsoft.public.access.queries)
  • Re: Editing Access Data
    ... > second query to an update query, and update both your phone field and your ... > last name, etc.), so why not combine that exercise with the "moving phone ... >> actual name is sometimes, also appended with a ten digit phone ...
    (microsoft.public.access.queries)
  • Re: Insert a LIST into each line of 2nd file - WILL PAY $$$!
    ... the query edit window called "criteria". ... Being that one file has one field which is a 4 digit number ... I know I have more than sufficient disc space. ... I can avoid this part by simply removing records after the fact but ...
    (comp.databases.ms-access)
  • Re: Need Help with Criteria Expression
    ... query that will do this and save this query. ... setting a validation rule that's commensurate with the way data would ...  If I actually enter a space, that is the same as entering a digit so it ... This input mask will allow for 1 digit months, 2 digit days, and 2 digit ...
    (microsoft.public.access.queries)
  • Re: Query for Left(String,5) - Access 2007
    ... Note that this query cannot use the query design view, but must be done in the SQL view. ... FROM ((boston INNER JOIN Name ON boston.Boston = Name.ZIP) INNER JOIN ... 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. ... It will then find all matching 5 digits and all that start with the 5 digit code. ...
    (microsoft.public.access.queries)