MS Access SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

MS Access SQL

Author

Topic

Programmer
Starting Member

5 Posts

Posted - 2004-10-12 : 11:58:00

I need help in querying a large number(hundreds) of zipcodes using the BETWEEN operator in MS Access SQL (the query's expression builder has a maximum # character limitation). I have to do it in SQL statement that says:
SELECT
FROM
WHERE
((PUBLIC_SPRADDR.SPRADDR_ZIP) Between "40058" And "40058-9999" Or (PUBLIC_SPRADDR.SPRADDR_ZIP) Between "40355" And "40355-9999" Or (PUBLIC_SPRADDR.SPRADDR_ZIP) Between "40359" And "40359-9999" Or (PUBLIC_SPRADDR.SPRADDR_ZIP) Between "40363" And "40363-9999";
It gives me an error message.
Please help!

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-10-12 : 12:44:49

Select ?

You have not told it what you want returned.

Jim
Users <> Logic

Programmer
Starting Member

5 Posts

Posted - 2004-10-12 : 13:31:01

Jim- This is what I have and I want to limit the records to certain zipcodes only...which means I need to use a BETWEEN operator.
EX: zipcode between 48103-48109 or zipcode between 41111-49999
(The SQL statement below runs fine. I have a problem when I put in the zipcodes. Its a syntax error message I get). Thanks so much for responding to my request!

SELECT SATURN_SPRIDEN.SPRIDEN_ID, BANINST1_AA_GIFT_DETAIL.NAME_PREFIX, BANINST1_AA_GIFT_DETAIL.FIRST_NAME, BANINST1_AA_GIFT_DETAIL.MIDDLE_NAME, BANINST1_AA_GIFT_DETAIL.LAST_NAME, BANINST1_AA_GIFT_DETAIL.GIFT_AMT, BANINST1_AA_GIFT_DETAIL.GIFT_DATE, BANINST1_AA_GIFT_DETAIL.DESIGNATION_NAME, BANINST1_AA_GIFT_DETAIL.GIFT_DESC, ALUMNI_ADBDESG.ADBDESG_DESG, SATURN_SPRADDR.SPRADDR_CITY, SATURN_SPRADDR.SPRADDR_ZIP
FROM SATURN_SPRADDR INNER JOIN ((BANINST1_AA_GIFT_DETAIL INNER JOIN SATURN_SPRIDEN ON BANINST1_AA_GIFT_DETAIL.ID = SATURN_SPRIDEN.SPRIDEN_ID) INNER JOIN ALUMNI_ADBDESG ON BANINST1_AA_GIFT_DETAIL.DESIGNATION_KEY = ALUMNI_ADBDESG.ADBDESG_DESG) ON SATURN_SPRADDR.SPRADDR_PIDM = SATURN_SPRIDEN.SPRIDEN_PIDM
WHERE (((BANINST1_AA_GIFT_DETAIL.GIFT_DATE) Between #8/1/2004# And #8/31/2004#))
ORDER BY BANINST1_AA_GIFT_DETAIL.LAST_NAME, ALUMNI_ADBDESG.ADBDESG_DESG;

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-12 : 14:06:15

Create a table called ZIPRange or something like that, abd store your ranges in a table with a beginningZIP and EndingZIP column.

Then just add the table to your query (no join), and add a WHERE clauase that says "WHERE ZipCode BETWEEN BeginningZIP and ENdingZIP". Make sure you have no overlaps in your ZIPRange table and you are all set.

Better yet, add an attribute to your data that indicates WHY you are filtering for that range; for example, a "Region" column or something of that nature.

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-12 : 14:07:56

just noticed your "ranges" -- don't use BETWEEN.

you can use like:

WHERE ZIPCode like "40355*" OR ZIPCode LIKE "40058*" OR .... etc

OR you can use the LEFT function with the IN () operator:

WHERE Left(ZIPCode,5) IN ("40355","40058","41002", ..etc )

- Jeff

Programmer
Starting Member

5 Posts

Posted - 2004-10-12 : 14:38:27

Thanks Jim. I think we are pretty close....

What about the sequential zipcodes such as 48103, 48104, 48105, 48106? Do I have to list each one separately or can I list them together as a range? like 48103-48106?

My work involves querying on a range of zipcodes in a given radius. Please help....

programmer

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-12 : 14:52:23

you need to read up on SQL, look at the WHERE examples in Access help or do some googling.

if you want all zip codes between 48103 and 48106, just say it:

WHERE ZIPCode between "48103" and "48106"

but if ZIPCode ends in a value such as "-1234", then you need to accomidate for that:

WHERE ZIPCode between "48103-0000" and "48106-9999"

Does that make sense? Read up on BETWEEN and how comparison operators such as < and > work with alphanumeric values.

- Jeff

Programmer
Starting Member

5 Posts

Posted - 2004-10-12 : 15:03:47

Thanks a lot for all your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources