Help with a partial match query

[Whirled.Peas]

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.

 

[Gina Whipp]

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

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.

 

[John Spencer]

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

 

[Whirled.Peas]

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

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.

 

[Gina Whipp]

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

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]

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

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

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.

 

[John Spencer]

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