Help with a partial match query

I asked this question here many, many years ago and have since forgotten 
the answer. 

I have a table with address data in it, spread over several fields. One 
of the fields is called "Zip" and is a text field of 5 characters in 
length.

I am trying to write a query that will prompt the user to enter the first 
FOUR digits of the zip code and have it return all matches for those four 
characters.

For example, 1234 would return 12345, 12346, and 12347 and so on.

My recollection is that the query was something like: 
SELECT * FROM addresses WHERE Zip LIKE "[Enter first four digits]%" but 
that does not seem to work in Access 2007. I am probably misremembering.

Does anyone know the proper query syntax?

Thank you for your help.

-- 
If you try, you can envision peas on earth.
0
Whirled
3/20/2010 2:36:45 AM
access.queries 6343 articles. 1 followers. Follow

6 Replies
3173 Views

Similar Articles

[PageSpeed] 15

Whirled.Peas,

I think what you looking for is...

LIKE "[Enter first four digits]" & "*"

-- 
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors 
II

http://www.regina-whipp.com/index_files/TipList.htm

"Whirled.Peas" <peas@earth.org> wrote in message 
news:ho1cbt$jdg$1@news.datemas.de...
I asked this question here many, many years ago and have since forgotten
the answer.

I have a table with address data in it, spread over several fields. One
of the fields is called "Zip" and is a text field of 5 characters in
length.

I am trying to write a query that will prompt the user to enter the first
FOUR digits of the zip code and have it return all matches for those four
characters.

For example, 1234 would return 12345, 12346, and 12347 and so on.

My recollection is that the query was something like:
SELECT * FROM addresses WHERE Zip LIKE "[Enter first four digits]%" but
that does not seem to work in Access 2007. I am probably misremembering.

Does anyone know the proper query syntax?

Thank you for your help.

-- 
If you try, you can envision peas on earth. 

0
Gina
3/20/2010 3:23:09 AM
whoops!  Gina seems to have uncharacteristically gotten carried away with the 
quotes.
    LIKE [Enter first four digits] & "*"

You might need to replace the * with a % if you have set up your database to 
use ANSII-compliant SQL.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Gina Whipp wrote:
> Whirled.Peas,
> 
> I think what you looking for is...
> 
> LIKE "[Enter first four digits]" & "*"
> 
0
John
3/20/2010 1:24:34 PM
On Sat, 20 Mar 2010 09:24:34 -0400, John Spencer wrote:

> whoops!  Gina seems to have uncharacteristically gotten carried away
> with the quotes.
>     LIKE [Enter first four digits] & "*"
> 
> You might need to replace the * with a % if you have set up your
> database to use ANSII-compliant SQL.
> 
> 
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
> 
> Gina Whipp wrote:
>> Whirled.Peas,
>> 
>> I think what you looking for is...
>> 
>> LIKE "[Enter first four digits]" & "*"
>>

That did the trick! Thank you both for your help, it is very much 
appreciated. It took me a few tries to get the proper number of quotes in 
place, but John's string is correct.



-- 
If you try, you can envision peas on earth.
0
Whirled
3/20/2010 3:02:24 PM
Oh dear... THANKS John!

-- 
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors 
II

http://www.regina-whipp.com/index_files/TipList.htm

"John Spencer" <spencer@chpdm.edu> wrote in message 
news:uDYOvCDyKHA.2436@TK2MSFTNGP04.phx.gbl...
whoops!  Gina seems to have uncharacteristically gotten carried away with 
the
quotes.
    LIKE [Enter first four digits] & "*"

You might need to replace the * with a % if you have set up your database to
use ANSII-compliant SQL.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Gina Whipp wrote:
> Whirled.Peas,
>
> I think what you looking for is...
>
> LIKE "[Enter first four digits]" & "*"
> 
0
Gina
3/20/2010 5:00:30 PM
Well, thank goodness John came along!

-- 
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors 
II

http://www.regina-whipp.com/index_files/TipList.htm

"Whirled.Peas" <peas@earth.org> wrote in message 
news:ho2o20$2mf$1@news.datemas.de...
On Sat, 20 Mar 2010 09:24:34 -0400, John Spencer wrote:

> whoops!  Gina seems to have uncharacteristically gotten carried away
> with the quotes.
>     LIKE [Enter first four digits] & "*"
>
> You might need to replace the * with a % if you have set up your
> database to use ANSII-compliant SQL.
>
>
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
>
> Gina Whipp wrote:
>> Whirled.Peas,
>>
>> I think what you looking for is...
>>
>> LIKE "[Enter first four digits]" & "*"
>>

That did the trick! Thank you both for your help, it is very much
appreciated. It took me a few tries to get the proper number of quotes in
place, but John's string is correct.



-- 
If you try, you can envision peas on earth. 

0
Gina
3/20/2010 5:01:20 PM
No problem. I've had a few* of my postings refined** by others.

* - an indeterminate number less than infinity
** - corrected politely sometimes with infinite patience.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Gina Whipp wrote:
> Oh dear... THANKS John!
> 
0
John
3/20/2010 7:32:56 PM
Reply:

Similar Artilces:

Import/Export HELL
Hi, I need to import a custom attribute for all users - about 600 of them, and the attribute is specific to the user account. I can't write scripts at ALL - although I can't even find one that I could modify even if I could make out what to do. I have tried looking at csvde and all but nothing is out there about exporting all the users with what the custom attribute is and then importing it to what it needs to be? Or just doing an import based on the user name and the custom attribute I have and let it overwrite whatever it is. It is custom attribute 4 - 2003 sp1 dc's, 2003...

help with configuration
I'm new using Ms Exchange. this is the first time that I ever install Exchange. I just install Ms exchange 2003 and I need to know how I can configure. I create a couple of accounts. How I can configure my Exchange to respond as my mail server. Sincerely, Rafael On Tue, 22 Aug 2006 13:55:58 -0400, "Rafael Tejera" <rafaeltejera@hotmail.com> wrote: >I'm new using Ms Exchange. this is the first time that I ever install >Exchange. > >I just install Ms exchange 2003 and I need to know how I can configure. > >I create a couple of accounts. How I...

Query Optimization Help
Hi, GP 8.0 SQL I am trying to create a view for looking up sales data. The view is very slow so I am trying to optimize it. I ran the execution scan and it shows two table scans. I am trying to change the query to avoid these scans, however, I am stuck. THe tables I am joining are SOP30200 and SOP30300. I've isolated a part of the query that I believe is generating the most cost. Here it is: (SELECT T1HEADER.SOPTYPE, T1HEADER.SOPNUMBE, T1HEADER.DOCID, T1HEADER.DOCDATE, T1HEADER.PRCLEVEL, T1HEADER.LOCNCODE AS LOCNCODE, T1HEADER.CUSTNMBR, T1HEADER.CUSTNAME, T1HEADER.CSTPONBR, T1HEADER...

Cannot create an organisational form in Exchange 2003, SP2..Help!!
We have exchange 2003 with SP2. I cannot create an organisatonal form in exchange. When I right click EFORMS REGISTRY (First admin group\Folders\public folders\EFORMS REGISTRY) i do not have the option to select new > Organizational Form. Any ideas? I am logged in as administrator so I have the necessary permissions.. Please please help. Thanks Vijay ...

Pivot-Like Query for Christmas?
SQL Server 2000. I have the following table and need to turn it into something "flatter". Gurus, can you help me come up with any SQL 2000-compliant TSQL that can make this happen? I have this: CREATE TABLE #Pairs ( RowId Integer, ItemKey VARCHAR(50), ItemValue VARCHAR(50), ItemType VARCHAR(50), ItemDesc VARCHAR(50) ); INSERT INTO #Pairs VALUES(1,'Height','84','Int','Height of the Door'); INSERT INTO #Pairs VALUES(2,'Width','40','Int','Widtrh of the door.'); INSERT INTO ...

Help with like operator
I have a field that I am trying to filter any record that Has an upper case letter at the end of the record [A-Z]. I used like "*[A-Z]" however, I am getting upper and lower case as well. I am only interested in Upper case. any idea? thanks Al Thank you, it worked:) "KARL DEWEY" wrote: > Access treats upper and lower case the same. > > Add a new field to the grid in design view like this -- > A-Z_Check: Asc(Right([YourFieldName],1)) > Use criteria Between 65 And 90 > > -- > KARL DEWEY > Build a little - Test a little > > ...

need help with a dialog app
I have created a dialog app. I just need to run this app to update a database so I commented out the lines that create and show the dialog box. In my InitInstance I just have my lines of code to update the database and then the return FALSE. When I run this, I get an error message about the app terminating in an unusual manner. How do I stop that message box from appearing? On Wed, 2 Jun 2004 12:13:47 -0400, William Gower wrote: >In my InitInstance I just have my lines of code to update the database and >then the return FALSE. When I run this, I get an error message about the >...

PLEASE HELP: automate process to import tables from notepad to acc
Hi All, Is there a way to import tables that are in text file (notepad) into access automatically? at a specified time? How can I do this? For eg: I have a location where I receive 10-15 tables in text format (notepad) and I want to import all those tables in access (access.2007) at a specified time. Can this be automated? Thanks in advance. On Thu, 29 Apr 2010 10:15:01 -0700, sam <sam@discussions.microsoft.com> wrote: >Hi All, > >Is there a way to import tables that are in text file (notepad) into access >automatically? at a specified time? &g...

Column and line chart help
I need to create a combination column/line chart for 2 series. Each series has 3 separate entries. Example, Series 1=salesman A sold 200 cases of item x last year, then sold 250 cases of item x during our promo period, and finally sold 275 cases of item x after the promo ended. Also, Series 2=salesman A sold a TOTAL of 5000/6000/8000 cases of all items we stock last year/promo/after. I have no problem getting it to work with 1 set of data as listed above with the item x data in colums and all cases data as a line above each column. But can't get it to work with 7 salesmen. T...

Need help w very difficult formula!
Hello all! I need help with an "if then" formula. What I have is a list of about 8000 people in column a, their hours worked in column b, and a number of 8,9,10,11,12,13,20,21,22,23 in column c. I need several things to happen. If someone's scheduled hours worked are greater than or equal to 32, and they are 8,9,10 I need a result to say 150. If someone's scheduled hours are less than 32 and they are 8,9,10, I need the result to say 75. AND If their hours are greater than or equal to 32, and they are 12 or 13, the result = 250, if hours less than 32 and 12 or 13...

If statement please help
I need help, what would this statement need to look like if I want th following info: Column L must equal column N only if column K=TRUE if column K=FALS then column L must equal 0. Thanks in advance Linse -- Message posted from http://www.ExcelForum.com One way: L1: =IF(K1,N1,0) another: L1: =N1*K1 (TRUE is coerced to 1 in math operations, FALSE is coerced to 0) In article <drreamsurfer.1bnjgc@excelforum-nospam.com>, drreamsurfer <<drreamsurfer.1bnjgc@excelforum-nospam.com>> wrote: > I need help, what would this statement need to look like if I want t...

Union query not displaying select statement
I have a union query which provides column totals for a detail query. Everything works as expected but for some reason when I run the query the results will not always display the last select statement - "Percent of total" row. The odd occurence is if I run the query with a filter that limits the records to one viewable page in datasheet view the "percent of total" row is displayed. If the query output is more than one viewable page the "percent of total" row does not display. Also, if I remove "all" from the last union statement the "...

Help with formula 01-04-10
I have data and a formula like below. I'm trying to adjust the formula to count the # of rows that have a value in Column A that is = to cell A2 on my OST_Raw_Data sheet and where the value in Column C on my OST_Raw_Data sheet = "Tier 1". The formula is not on the OST_Raw_Data sheet and "A2" is on the same sheet as the formula. I believe the part of the formula that references A2 is correct, but I'm having problems with the rest. I'm using Excel 2003 and I need to keep the Subtotal to allow for consideration of filtered rows. Can someone help? ...

Excel column format problem, please help
I use a billing number in column "A" that always starts with 22-number. I get tired of repeating the 22- before every number. I wan to be able to type in the last four numbers and have the 22- pop i thier automatically, is that possible and if so, can some one tell m how to do it? Thank you in advance, Randy:confused -- Message posted from http://www.ExcelForum.com Format Cells, Number tab, Custom from the list, type 22-# in the box HTH, Greg "RandyM >" <<RandyM.178fb4@excelforum-nospam.com> wrote in message news:RandyM.178fb4@excelforum-nospam.com... &g...

Help with SELECT
I have a table with Accounts, let's call it Account. It has the following fields: AccountID AccountName ParentAccountID Data in the table: AccountID AccountName ParentAccountID 1 Checking 0 2 Savings 0 3 Expenses 0 4 Fixed Assets 0 5 Travel 3 6 Computers 4 7 Equipment 4 I need to write SEL...

SELECT help with sum based on prior rows value
This is recording when a person gets on and off the bus. I am trying to sum the stops below such that the total miles is based on an accumulated average. So for the set below, myid of 676850 would be 12.5 - 10 miles alone before picking up 677395 plus 5 / 2 (because 677395 got on at odometer 10) until 676850 got off at odometer 15. Then 677395 has 7.5 miles. 5 with 676850 and 5 alone (676850 was dropped of at odometer 15). This is a simple scenario. Any number of people could get on and off the bus between the pick up and drop off of any one person. WITH tripstops(myid, ...

Query on CRM-Exchange Email Router installation
hi folks, Reference chapter 5 of the Implementation Guide, the main CRM components must be located in the same Active Directory Domain. Have anyone tried installing the Router on an Exchange Server on a different domain from CRM Server? Can it be done? Does it work? heh. I'm familiar with the usual warning from Microsoft. Even if it works, it does not mean that it will be supported by Microsoft. Regards, Danny ...

How do i show the date a report or query was last run?
I am using Access 2007. I have several reports and queries which are run on a fairly regular basis. What I would like to be able to do is show the date the report was last run. Is there any way I can show this on a form which would show the name of the report or query and the date it was last run. Thank you for any help you can give me. Sean Bishop The easiest way I know is to have whatever triggers your report to also append a record to a table and to use DLokup on form to display Max dated record. -- Build a little, test a little. "Sean Bishop" wrote: ...

problem please help
I have been using publisher for years without any problems until now. I am making a tall poster 160 cm by 45 cm am having difficulty using the sliding bar on the right side, my page keeps jumping am also having a problem with the page freezing so I have to close down and restart computer. All my other saved progammes I still do not have this problem.. Any Ideas Is your video/graphics driver current? Read the fourth FAQ here http://ed.mvps.org/Static.aspx?=Publisher/FAQs -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com ...

Show Query Design Toolbar
I am starting my database with the database window and built-in toolbars hidden. Certain people have the right to created new queries, so I have created a button (which is visible only for these people) that will display the database window so they can access queries. The problem is the Query Design Toolbar is not displayed when they go into Design view. I have tried to show the toolbar using the following statement, without success: ---DoCmd.ShowToolbar "Query Design", acToolbarYes I have tried putting this statement before and after the statement that opens the database window, a...

Excel
Have typed a paragraph into a cell and no matter what size I set the row to allow the text to wrap (it's set to wrap), I get a row of ####. You probably have the formatting set to "Text". Try setting it to "General". You still might not be able to see all your text, because Excel limits the number of characters it will display in a cell. However, when you click on the cell, you'll see it in the formula entry area. "Format/Cells.../Number" set to "General" HTH, Eric "Cheryl" wrote: > Have typed a paragr...

Referencing a form text box in a query
Heya everyone, Quick question, I have a text box on a form that I want to reference in an update query as criteria. So far i have tried typing [formname].[textboxname] not working. Any reccomendations? Thanks! James O ...

help needed for sorting
I have an excel file with names in Column 'A' which starts with "MR " O "MRS " as prefix. and I have the formula in B as follows. IF(A1="","",IF(LEFT(A1,3)="MR ",MID(A1,4,LEN(A1)-3),IF(LEFT(A1,4)="MR ",MID(A1,5,LEN(A1)-4),A1))) It works perfect and I am running the following macro to sort column B alphabeticaly but after sorting, the empty rows are apprearing at beginning. Ca anyone help me??????? Sub Macro4() Range("A1:B" & Range("B500").End(xlUp).Row).Select Selection.Sort Key1:=Range("b1"...

i need some help!
My ISP is Tiscali and my email address is jackiewray@tiscali.co.uk On my computer i notice that i can use microsoft outlook, also with the same email address. I think i would prefer to use the microsoft one, but i dont know whether i can because of Tiscali. Could u help me please? Sorry, but i have no idea what to do, or even if i've explained my problem in a way that u can understand it! http://www.tiscali.co.uk/help/email/ has alot of information.... "jackie wray" <jackie wray@discussions.microsoft.com> wrote in message news:85BD4009-98AF-45A6-B367-A34710BC3D48@mi...

Please help a neophyte w/ connecting Outlook
I recently received a Dell Inspiron 5100 laptop. Since I only have a dial-up connection and I want to use this in a room where there is no telephone jack, I needed a long phone cord to connect the laptop to the phone jack. I attached the 50' cord to the laptop last week and all went fine connecting to the Internet. However, I cannot seem to make the connection again. I enter the password and it dials (pulse!), and the box says "connected to remote computer", then "verifying name and password", then finally "unable to establish a connection". I th...