 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / May 2008Query for Left(String,5) - Access 2007
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 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.  SignatureDave 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. [quoted text clipped - 22 lines] > Thanks > John 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 & "*" [quoted text clipped - 28 lines] > > 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] & "*" SignatureDave Hargis, Microsoft Access MVP > Thanks Klatuu, > That's the part I don't know how to do. [quoted text clipped - 45 lines] > > > Thanks > > > John 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. [quoted text clipped - 52 lines] > > > > 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? SignatureDave Hargis, Microsoft Access MVP > thanks again Kaltuu. > [quoted text clipped - 78 lines] > > > > > Thanks > > > > > John 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? > [quoted text clipped - 97 lines] > > > > > > Thanks > > > > > > 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. > [quoted text clipped - 78 lines] >>>>> Thanks >>>>> John 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. SignatureDave 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, [quoted text clipped - 104 lines] > >>>>> Thanks > >>>>> John 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, [quoted text clipped - 104 lines] > >>>>> Thanks > >>>>> John 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 '==================================================== > Thanks John, > Where do I enter sql code to create a query? I've never used anything other [quoted text clipped - 4 lines] > Thanks > The-other-John 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 > Well, you certainly can edit the SQL view. I do it all the time and > often I start there. [quoted text clipped - 20 lines] > > Thanks > > The-other-John 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 > Thanks John > I didn't realize you could edit the sql in the view? [quoted text clipped - 29 lines] > > > Thanks > > > The-other-John 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 > Well, you certainly can edit the SQL view. I do it all the time and > often I start there. [quoted text clipped - 20 lines] > > Thanks > > The-other-John 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 '==================================================== > 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 [quoted text clipped - 30 lines] >>> Thanks >>> The-other-John 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 > 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 [quoted text clipped - 44 lines] > >>> Thanks > >>> The-other-John 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 > 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 [quoted text clipped - 51 lines] > > >>> Thanks > > >>> The-other-John 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? > 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 [quoted text clipped - 30 lines] > > > Thanks > > > The-other-John 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 > Well, you certainly can edit the SQL view. I do it all the time and > often I start there. [quoted text clipped - 20 lines] > > Thanks > > The-other-John |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.