Prev: workgroup join problems
Next: inserting as variable
From: emanning on 8 Jul 2010 15:18
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
From: John Spencer on 8 Jul 2010 15:55
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:
> 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
From: emanning on 8 Jul 2010 16:01
On Jul 8, 2:55 pm, John Spencer <JSPEN...(a)Hilltop.umbc> wrote:
> 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:
> > 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- Hide quoted text -
>
> - Show quoted text -

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.
From: David W. Fenton on 9 Jul 2010 19:12
John Spencer <JSPENCER(a)Hilltop.umbc> wrote in
news:i15ag9$5de$1(a)news.eternal-september.org:

> You could use
> LIKE "67203*"

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).

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

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

> 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"

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/
 | 
Pages: 1
Prev: workgroup join problems
Next: inserting as variable