I've got an Access 2007 table with a field called Zip which is a text
field. Some zips were entered like "67203" and others like
"67203-2752". Is there a way to format a criteria statement in a
query to pull all zips between "67203" and "67203" without having to
resort to something like between "67203" and "67203-9999" or between
"67203" and "67204"?


Thanks

You could use
LIKE "67203*"

If you want a range you are going to have to use something like
WHERE Zip >= "67203" AND Zip < "67204"

Or you can use the Val function and then number values (at a cost in
efficiency and speed)

WHERE Val(ZIP) Between 67203 and 67203

Or you can use (at a cost in efficiency and speed)
WHERE LEFT(Zip,5) Between "67203" AND "67203"


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

emanning wrote:

On Jul 8, 2:55*pm, John Spencer <JSPEN... (AT) Hilltop (DOT) umbc> wrote:
I was just experimenting with 'WHERE LEFT(Zip,5) Between "67203" AND
"67203"' when I read your post. It appears to work just fine. Thanks
for your help.

John Spencer <JSPENCER (AT) Hilltop (DOT) umbc> wrote in
news:i15ag9$5de$1 (AT) news (DOT) eternal-september.org:

It's a string field and likely indexed, so this seems to me without
question to be the correct approach, as well as the most efficient
(LIKE conditions that start with the beginning of the field are
usually able to utilize the index).

This would also work and would probably be the most efficient user
of the index.

Do you have a typo here? Both tested values are the same?

I don't see any reason for using a function and then losing the
benefit of the indexes.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/