Help with a partial match query
in [Queries]
|
Prev: IIF and formatting
Next: E_lookup
From: Whirled.Peas on 19 Mar 2010 22:36 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.
From: Gina Whipp on 19 Mar 2010 23:23 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(a)earth.org> wrote in message news:ho1cbt$jdg$1(a)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.
From: John Spencer on 20 Mar 2010 09:24 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]" & "*" >
From: Whirled.Peas on 20 Mar 2010 11:02 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.
From: Gina Whipp on 20 Mar 2010 13:00
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(a)chpdm.edu> wrote in message news:uDYOvCDyKHA.2436(a)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]" & "*" > |