Comparing a Postal Code against a Zone Table from UPS

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I got a table with Postal Codes and Zones from UPS Canada.

The data is like this:

'Postal Code' 'Express' 'Expedited' 'Standard'
'A0A–A9Z' '411' '311' '211'
'B0A–B2T' '411' '311' '211'

I want to determine the zone depending of the postal code.

In fact the 'A0A–A9Z' represents a range.

I want to build a select query in which we will copy the postal code and
get the zone.

I got a hard time matching a postal code or the first 3 values of a postal
code to the those ranges.


Thank You

uberblick
 
Have 5 fields in the table: PostalCodeStart, PostalCodeEnd, Express,
Expedited and Standard.

Create a query that joins the two tables, then go into the SQL of the query
and change the Join criteria to:

FROM Table1 INNER JOIN UPSTable
ON Left(Table1.PostalCode, 3) BETWEEN UPSTable.PostalCodeStart AND
UPSTable.PostCodeEnd
 
Douglas,

Thanks

My table contains the postal code range (Start and End) and Zones per service.

You're telling me to have 5 fields in the table which is ok.

Than you said to join the 2 tables, which is where I am confused.

What is the 2nd table suppose to be ?

Thanks

uberblick
 
I have enclosed my select query here:

PARAMETERS PostalCode Text ( 255 );
SELECT ZonesCanada.Expedited
FROM ZonesCanada
WHERE (("WHERE ([PostalCode],3)" BETWEEN [ZonesCanada]![PCStart] AND
[ZonesCanada]![PCEnd]));


If I try it I get a an empty field with the header column "Expedited"
 
I just assumed that you had a table of addresses, and you wanted to know the
rates for each of them.

If you simply want to be prompted for a postal code, use

PARAMETERS PostalCode Text ( 255 );
SELECT ZonesCanada.Expedited
FROM ZonesCanada
WHERE Left([PostalCode],3) BETWEEN [PCStart] AND [PCEnd]
 
Thanks Douglas.

But I get a blank field with the column header "Expedited"

Regards

uberblick

Douglas J. Steele said:
I just assumed that you had a table of addresses, and you wanted to know the
rates for each of them.

If you simply want to be prompted for a postal code, use

PARAMETERS PostalCode Text ( 255 );
SELECT ZonesCanada.Expedited
FROM ZonesCanada
WHERE Left([PostalCode],3) BETWEEN [PCStart] AND [PCEnd]



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


uberblick said:
Douglas,

Thanks

My table contains the postal code range (Start and End) and Zones per
service.

You're telling me to have 5 fields in the table which is ok.

Than you said to join the 2 tables, which is where I am confused.

What is the 2nd table suppose to be ?

Thanks

uberblick
Click to expand...
Click to expand...
 
I have enclosed my select query here:

PARAMETERS PostalCode Text ( 255 );
SELECT ZonesCanada.Expedited
FROM ZonesCanada
WHERE (("WHERE ([PostalCode],3)" BETWEEN [ZonesCanada]![PCStart] AND
[ZonesCanada]![PCEnd]));
Click to expand...

You're comparing the PCStart and PCEnd fields to the literal text string
"WHERE ([PostalCode],3)"

which makes no sense at all.

Try

PARAMETERS PostalCode Text ( 255 );
SELECT ZonesCanada.Expedited
FROM ZonesCanada
WHERE Left([PostalCode],3) BETWEEN [ZonesCanada].[PCStart] AND
[ZonesCanada].[PCEnd];

John W. Vinson [MVP]
 
You sure your table has data corresponding to the postal code you entered?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


uberblick said:
Thanks Douglas.

But I get a blank field with the column header "Expedited"

Regards

uberblick

Douglas J. Steele said:
I just assumed that you had a table of addresses, and you wanted to know
the
rates for each of them.

If you simply want to be prompted for a postal code, use

PARAMETERS PostalCode Text ( 255 );
SELECT ZonesCanada.Expedited
FROM ZonesCanada
WHERE Left([PostalCode],3) BETWEEN [PCStart] AND [PCEnd]



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


uberblick said:
Douglas,

Thanks

My table contains the postal code range (Start and End) and Zones per
service.

You're telling me to have 5 fields in the table which is ok.

Than you said to join the 2 tables, which is where I am confused.

What is the 2nd table suppose to be ?

Thanks

uberblick


:

Have 5 fields in the table: PostalCodeStart, PostalCodeEnd, Express,
Expedited and Standard.

Create a query that joins the two tables, then go into the SQL of the
query
and change the Join criteria to:

FROM Table1 INNER JOIN UPSTable
ON Left(Table1.PostalCode, 3) BETWEEN UPSTable.PostalCodeStart AND
UPSTable.PostCodeEnd

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hello,

I got a table with Postal Codes and Zones from UPS Canada.

The data is like this:

'Postal Code' 'Express' 'Expedited' 'Standard'
'A0A-A9Z' '411' '311' '211'
'B0A-B2T' '411' '311' '211'

I want to determine the zone depending of the postal code.

In fact the 'A0A-A9Z' represents a range.

I want to build a select query in which we will copy the postal code
and
get the zone.

I got a hard time matching a postal code or the first 3 values of a
postal
code to the those ranges.


Thank You

uberblick
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

help with a query 5
Lookup a Canada postal code in a range of values 2
Return data from corresponding range 1
Creating a Query based on another table` 5
Assigning postal code from definitions table based on address elem 2
Data from a Different table 2
How can I get a postal code in a cell - from the internet? 2
VB Code on Event 3

Back
Top